PL/SQL: Identifier must be declared-CodePudding

I'm a beginner to PL/SQL so this issue is stumping me. I have some PL/SQL code:

create or replace package p2 as
  procedure show_students(ref_cursor out sys_refcursor);
  ... 
end;
/
show errors

create or replace package body p2 as
procedure show_students(ref_cursor out sys_refcursor) is
begin
    open ref_cursor for SELECT * FROM students;
end;
...
end;
/
show errors

and I also have this Java code:

public static void showTable( int option, Connection conn ) {
    try {
        CallableStatement cs = conn.prepareCall( "begin p2.show_students(?); end;" ) ;
        cs.registerOutParameter( 1, OracleTypes.CURSOR ) ;
        cs.execute() ;
        ResultSet rs = (ResultSet)cs.getObject( 1 ) ;
        while ( rs.next()) {
          System.out.println( rs.getString(1)   "\t"  
                              rs.getString(2)   "\t"   rs.getString(3)   
                              rs.getString(4)   
                              "\t"   rs.getDouble(5)   "\t"  
                              rs.getString(6)) ;
        }
        cs.close() ;
      }
      catch( SQLException ex ) {
        System.out.println( "SQL Exception "   ex.getMessage()) ;
      }

When I run the Java program, I get the following error:

SQL Exception ORA-06550: line 1, column 7:
PLS-00201: identifier 'P2.SHOW_STUDENTS' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

... I have no idea what is causing the issue. Any help is very appreciated!

CodePudding user response：

You must call the procedure like this

CallableStatement cs = conn.prepareCall("{call show_students (?)}");

There can be no other procedure with the name then p2. no need to mention

it should work

CodePudding user response：

I don't have all the answers but that error usually occurs due to a permissions issue. i.e. Your code doesn't have permission to run the SQL procedure.

You should try doing some GRANT statements