Connection connection = DriverManager.getConnection(DB_URL,userName,passWord);
ArrayDescriptor des = ArrayDescriptor.createDescriptor("ARRAY_TABLE", connection);
ARRAY array_to_pass = new ARRAY(des,connection,ssoIDs);
CallableStatement cs = connection.prepareCall("{ call TEST1 (?,?,?,?,?) }");
cs.setArray(1, array_to_pass);
// cs.registerOutParameter(2,sdate);
cs.setString(2, sdate);
cs.setString(3, edate);
//st.registerOutParameter(3,OracleTypes.ARRAY,"SchemaName.ARRAY_INT");
cs.registerOutParameter(4, OracleTypes.ARRAY, "ARRAY_TABLE");
cs.registerOutParameter(5, OracleTypes.ARRAY, "ARRAY_TABLE");
cs.execute(); // works fine .
Where as
String[] aIdtobePassed = actorid.toArray(new String[actorid.size()]);
String[] pIdtobePassed=processid.toArray(new String[processid.size()]);
ArrayDescriptor desforActorId = ArrayDescriptor.createDescriptor("ARRAY_TABLE", connection);
ARRAY actorArray_to_pass = new ARRAY(desforActorId,connection,aIdtobePassed);
ArrayDescriptor desforProcessId = ArrayDescriptor.createDescriptor("ARRAY_TABLE", connection);
ARRAY processArray_to_pass = new ARRAY(desforProcessId,connection,pIdtobePassed);
CallableStatement csforST= connection.prepareCall("{ call TEST2 (?,?,?,?,?,?,?,?) }");
csforST.setArray(1, actorArray_to_pass);
// csforST.setString(1, "as");
csforST.setArray(2, processArray_to_pass);
csforST.registerOutParameter(3, OracleTypes.ARRAY, "ARRAY_TABLE");
csforST.registerOutParameter(4, OracleTypes.ARRAY, "ARRAY_TABLE");
csforST.registerOutParameter(5, OracleTypes.ARRAY, "ARRAY_TABLE");
csforST.registerOutParameter(6, OracleTypes.ARRAY, "ARRAY_TABLE");
csforST.registerOutParameter(7, OracleTypes.ARRAY, "ARRAY_TABLE");
csforST.registerOutParameter(8, OracleTypes.ARRAY, "ARRAY_TABLE");
csforST.execute(); // do not works
Test1 belongs to one schema and Test2 belongs to another schema.
Error message is
State : 65000 Message: ORA-06550: line 1, column 7: PLS-00201: identifier 'TEST2' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored
I am using SQL developer where the schema is connected to the database because i can see plug symbol on the schema which resembles it is connected. But i am not sure why it do not worked any one any idea please help ?
Finally found why i am getting the error like this . The username and password what i am trying to make the connection with do not have privilige to access the second schema .
Since i am calling the proc of different schemas by just their name alone "{call a(?,?)}". It refers to the schema of first one alone and hence throws the error that the other proc is not present .
On making a new connection with userName and password in order to call the second schema . It works fine.