schema owner: USER1
logged in as user1: CREATE OR REPLACE TYPE BLABLABLA AS TABLE OF VARCHAR2(8)
in my app:
If I run my app (logged in as USER1 or USER2) --> OCI-22303: type ""."USER1.BLABLABLA" not found
If I change the type name in setVector to "BLABLABLA" then everything works fine when logged in as USER1 (the owner) but OCI-22303: type ""."BLABLABLA" not found error occurs when logged in as USER2. In production, I must run my app with a "no owner" user.
I noticed the "" in the error message (maybe this is the missing schema name... I can't find how to specify it)
Somebody can help me?
Thanks in advance!
Edit: I forgot to say that I already granted execute on BLABLABLA to USER2
Edited by: PabloCaballero on 06-oct-2012 19:36
There are several overloaded setVector functions with the schemaName parameter. The first google result for "occi classes" is http://docs.oracle.com/cd/B10501_01/appdev.920/a96583/cci08r20.htm#1107159 wich is very old.
As a workaround, try using a synonym so as to avoid having to prefix the type name with USER1
e.g. as user2: create or replace synonym blab for user1.blablabla;
I'm not sure why its not working - the "" in the error message does seem strange.
One other thing you could do is post some SQL (e.g. simplified test case) for the creation of the type, granting permission and show that it can be used OK from user2 in SQL - this would help isolate it as an OCCI problem rather than just a general SQL/Types problem