How to pass Map to Oracle PL/SQL function?

Question

Hi,

I would like to create an equivalent if this Java method as PL/SQL function in Oracle 10g:

String myMethod(int par1, Map<String, Object> par2);

Is it possible to pass a Map (or some simillar structure) to Oracle PL/SQL function? I have to be able to call this function from Java somehow.

Answer 1

down this path lies horror and despair. i have seen it.

Answer 2

Hi Ula,

There is an interesting discussion on AskTom about passing java objects to Oracle. In particular IMO, this excellent advice from Tom Kyte:

Me, I would juse

create global temporary table gtt ( fname varchar2(20), lname varchar2(20) ) on commit delete rows;

and have the java app BATCH insert into this and then call the procedure, the procedure just uses that tables data as its inputs.

that, in my experience, is the least amount of code between "me" and "being finished"

i-e: just use a set of relational temporary tables, write into it with java and let pl/sql read from the tables. It will probably be nearly as efficient (as passing objects) and it will be orders of magnitude easier to implement and debug.

Answer 3

Read this article on JDBC interface for Oracle collections first

Basically for complex objects you should use JPublisher to automatically generate the Java classes and the boilerplate code. Otherwise, a simple oracle.sql.ARRAY should do.

Note that you also need to create matching PL/SQL objects too, read this article. You don't need to implement any MEMBER methods for just passing around data between Java and Oracle.

---

Edit 1: Unfortunately, PL/SQL does not have a Map concept like Java. However, you can model a Map as a special (Key,Value) object or as an Index-by table for simple class (sort of like a hash map).