Java and JavaScript in the Database

Hi,

I have an issue when implementing the ODCI pipeline function interface in Java using 11.1.0.7. I have copy/pasted the [url http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28425/pipelined_example.htm#CHDHDHEE]example code from the documentation, only adding an ODCITablePrepare function in order to take advantage of the knowledge of the columns that are addressed by the query. Everything works fine the first time I launch my function as one can see from the trace

SQL>exec insert_ab10_cat;
Entering ODCITablePrepare
setContext key=0
create hashtable
setContext new key=1
Created context with key = 0
Marking fields accessed by query: 3 - 13 - 14 - 15 ...
Leaving ODCITablePrepare with context = DWH.PARSEFILECAT

Entering ODCITableStart with context = DWH.PARSEFILECAT
Retrieving context with key = 0
getContext key=0 count=1
Calling list_files() with sysName = AB10, fileType = CAT
Leaving ODCITableStart

Entering ODCITableFetch with nrows = 1
Retrieving context with key = 0
getContext key=0 count=1
Opening file /mnt/cluster/xxxxxxxxx
Leaving ODCITableFetch with nrows = 276

Entering ODCITableFetch with nrows = 32766
Retrieving context with key = 0
getContext key=0 count=1
Leaving ODCITableFetch with nrows = 0

Entering ODCITableClose
Retrieving context with key = 0
getContext key=0 count=1
Leaving ODCITableClose

PL/SQL procedure successfully completed.

Also things are ok if I launch the function again in the same session since I did not clear the previous context. This time Oracle does not call ODCITablePrepare because the SQL query is kept in shared area:

SQL>exec insert_ab10_cat;
Entering ODCITableStart with context = DWH.PARSEFILECAT
Retrieving context with key = 0
getContext key=0 count=1
Calling list_files() with sysName = AB10, fileType = CAT
Leaving ODCITableStart

Entering ODCITableFetch with nrows = 1
Retrieving context with key = 0
getContext key=0 count=1
Opening file /mnt/cluster/......
Leaving ODCITableFetch with nrows = 250

Entering ODCITableFetch with nrows = 32766
Retrieving context with key = 0
getContext key=0 count=1
Leaving ODCITableFetch with nrows = 0

Entering ODCITableClose
Retrieving context with key = 0
getContext key=0 count=1
Leaving ODCITableClose

PL/SQL procedure successfully completed.

However things go awry if I log out and in again, or if I try from a new session:

SQL>exec insert_ab10_cat;
Entering ODCITableStart with context = DWH.PARSEFILECAT
Retrieving context with key = 0
getContext key=0 count=0
Exception in thread "Root Thread" java.lang.NullPointerException
        at oracle.CartridgeServices.ContextManager.getContext(ContextManager.java:48)
        at myClass.ODCIstart(ParseFile.java:144)
        at myClass.ODCITableStart(ParseFileCAT.java:97)
BEGIN insert_ab10_cat; END;

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.lang.NullPointerException

Note that the crash happens inside of Oracle's ContextManager class... Hmm... Of course everything go back to normal if I flush the shared pool. The behavior (except of course for the crash part) is a bit comprehensible since different sessions are not supposed to share memory, so the context that was initially created by ODCITablePrepare is not available to other sessions, although the index to the object stored by the CartigeServices ContextManager is an attribute of the type object (in this case DWH.PARSEFILECAT) and is shared by all sessions.

So what's the solution to this situation? I have the feeling that the right way to do it would be to define and store all objects that are to be passed between sessions as attributes of the type object, instead of a single integer pointer to an ContextManager entry; however the Java object representing the context is quite complex and I have no idea how to represent it in PL/SQL, coding it by hand would be a very difficult and error-prone task, let alone a maintenance nightmare. Does anyone know of a better way to do it?

Thanks for your help,
Chris