CODEWITHSUNDEEP
databaseoracle-databaseoracle11g
user1465587
user1465587

Reputation:

PLS-00201 Error when running stored procedure in Oracle

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

Upvotes: 1

Views: 27608

Answers (1)

Jacob
Jacob

Reputation: 14741

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;

Upvotes: 1

Related Questions