How to pass Map to Oracle PL/SQL function?

Question

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

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.