1

I Recently moved on oracle database for one of my project. I have created a stored procedure for selecting multiple rows from database. Following is my procedure

create Or replace
PROCEDURE TEST(p_cursor OUT SYS_REFCURSOR) AS

BEGIN
open p_cursor FOR select * from branch_info;
END TEST;

when I execute this procedure I got following error:

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SAURAV.TEST' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I have searched for it and found similar question here but error line and reason are different.

Anyone please help me in solving this.

EDIT: Misprint TEST with TEXT

Community
  • 1
  • 1
  • What is `SAURAV.TEXT`? Tablename.columnname? What if when you run `select * from branch_info;` Any errors? – Jacob Mar 21 '13 at 09:58
  • @Polppan Its SAURAV.TEST, it is dbname.procedurename. If I run this command on sql editor it works fine and give me result set. I have corrected the misprint. –  Mar 21 '13 at 10:04
  • If `SAURAV` is a user or schema or table or view make sure you have permission to access it. Without much knowledge of what is `branch_info` it would be difficult. – Jacob Mar 21 '13 at 10:06
  • well SAURAV is database schema name and I have full permission to it. However branch_info is table name in schema SAURAV. –  Mar 21 '13 at 10:07
  • If you are getting results from sql editor, then from where you are getting the errors? – Jacob Mar 21 '13 at 10:08
  • I am getting error when I try to run stored procedure name TEST which contain this statement. –  Mar 21 '13 at 10:09
  • Can you describe `branch_info`? – Jacob Mar 21 '13 at 10:10
  • @Polppan branch_info is table in schema named saurav, I am not getting why are you confused with 'brranch_info' –  Mar 21 '13 at 10:12
  • Try some name other than `TEST` and see. – Jacob Mar 21 '13 at 10:20
  • @Polppan I have tried your suggestion with procedure named 'NTTEST' but getting same error with the 'NTTEST' in place of 'TEST' –  Mar 21 '13 at 10:25
  • Adittya See my answer – Jacob Mar 21 '13 at 10:30

1 Answers1

1

The problem is with keyword test

CREATE OR REPLACE PROCEDURE test (p_cursor OUT sys_refcursor)
AS
BEGIN
    OPEN p_cursor FOR
        SELECT  *
          FROM  branch_info;
END test;

and execute by

variable rc refcursor;

exec test( :rc );

print rc;

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

Change to some other name

CREATE OR REPLACE PROCEDURE test2 (p_cursor OUT sys_refcursor)
AS
BEGIN
    OPEN p_cursor FOR
        SELECT  *
          FROM  branch_info;
END test2 ;

execute as

variable rc refcursor;

exec test2 ( :rc );

print rc;

PL/SQL procedure successfully completed.

From sql plus

SQL> variable usercur refcursor;
SQL> DECLARE
  2  BEGIN
  3  test2(:usercur);
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> print usercur;
Jacob
  • 14,463
  • 65
  • 207
  • 320
  • do we have any other command for printing result in query editor of sql plus –  Mar 21 '13 at 10:51
  • [This](http://stackoverflow.com/questions/4376044/display-resultset-from-oracle-10g-stored-procedure) may help – Jacob Mar 21 '13 at 10:57
  • Well have you tried it on command line, I am still getting same issue there. –  Mar 21 '13 at 10:57