-2

When I call a PL/SQL of particular schema in Java file it is getting executed and retrieves the result. But when I call another PLSQL in the same Java file of different schema it shows an error saying: 

Message: `ORA-06550: line 1, column 7: PLS-00201: identifier 'TEST2' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored`

Example : There are two proc say A and B present in x schema and y schema

I am calling it under test.java file:

CallableStatement csforST= connection.prepareCall("{ call A() }"); 
csforST.execute();

It works fine.

But when I call:

CallableStatement cs= connection.prepareCall("{ call B() }"); 
cs.execute();

It displays an error saying B must be declared.

Sathish
  • 13
  • 1
  • 4
  • Usually indicates a missing database grant or synonym. – GriffeyDog Dec 04 '12 at 20:52
  • Sure Robuust There are two proc say A and B present in x schema and y schema I am calling it under test.java file CallableStatement csforST= connection.prepareCall("{ call A() }"); csforST.execute();// works fine //but when i call CallableStatement cs= connection.prepareCall("{ call B() }"); cs.execute();// it displays an error saying B must be declared be declared Griffey : database grant or synonymn means?. I had checked with the connection of the database schema , it is connected. I am using SQLdeveloper in that it shows a plug symbol which tells that it got connected to the database – Sathish Dec 05 '12 at 08:11
  • For a connection to schema x to be able to call procedure B owned by schema y, schema y needs to "GRANT EXECUTE on B to x;", and x needs to either call it as y.B() or create a synonym (CREATE SYNONYM B for y.B;) and simply call it as B(). You should be able to do this in SQLDeveloper if you can connect to each schema. – GriffeyDog Dec 05 '12 at 22:15

1 Answers1

0

Thank you very much Griffey you made my day. Yes what Griffy said is right. It is simple but spent one day to find it. Since i am new in calling stored proc.

Actually there will be a username and password given for making the connection. The username what i am using do not have permission for the another schema. Hence created a new connection with a userNaming having an access to that schema and then called the another proc. It works fine now :).

I guess it would be useful for the starters like me.

Thanks Griffey

Sathish
  • 13
  • 1
  • 4