oracle collection as function return type

We have a database function to cache sequence values. It accepts a sequence object name and a fetch size, increments the sequence and return the values. The return type is a oracle collection.

Here is the definition of the db types used by the function:

create or replace type icn_num_type as object(v_inc_num number);
create or replace type icn_num_type_table as table of icn_num_type; --this is returned

The values returned by the function are cached on the application side. We are using ibatis for DAO. All this worked well when the function, types and the sequence objects were all in the same schema.

Now we have the function, types and sequ开发者_开发问答ences defined in one parent schema. The user schema has synonyms to the all the above mentioned objects. I am facing the following error now:

--- The error occurred while executing query procedure.  
--- Check the {? = call seq_inc(?, ?)}.  
--- Check the output parameters (register output parameters failed).  
--- Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_PICKLER", line 18
ORA-06512: at "SYS.DBMS_PICKLER", line 58
ORA-06512: at line 1

However when we access the function from SQLDeveloper(user schema) it works fine.

Could some one help me on this issue?

Seems there were issues (restrictions / bugs) with synonyms and Object types back in 9iR2 for java. Google for ORA-21700 and DBMS_PICKLER

I suspect you've got some issue with the JDBC driver being used for iBatis that is resolved for the JDBC version used for SQL Developer.

Grab something like SQuirrel SQL Client and try the JDBC driver you are using for iBatis with that to see if you can reproduce.