OraFAQ Forum: SQL & PL/SQL » problem with ref cursor

Home » SQL & PL/SQL » SQL & PL/SQL » problem with ref cursor (Oracle, 10g, Windows Server 2005)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

problem with ref cursor [message #436380]

Wed, 23 December 2009 04:20

getritesh
Messages: 235
Registered: July 2006
Location: INDIA

Senior Member

Hi,

My ref cursor is giving output from anonymous block but not
through a Procedure when taken as output parameter.
My simple code between begin and End is shown as follows

Create Or Replace Package package_test Is
 Type ref_cur Is Ref Cursor;
 End package_test; 

Declare
 cur package_test.ref_cur;
 vempno number;
 var Varchar2(1000);
Begin
 var := 'SELECT EMPNO
         FROM EMP
         WHERE DEPTNO = 10
         AND JOB= ''CLERK''';
 Open cur For var;
 Loop
  Fetch cur Into vempno;
  Exit When cur%notfound;
  dbms_output.put_line('Employee number '||vempno);
 End Loop;
End;
SQL> /
Employee number 7934

But my Procedure does not give output
My Procedure is as shown below

Create  Or Replace Procedure PR_CAD(USRENTR Varchar2,cur out package_test.ref_cur) Is
 var Varchar2(1000);
Begin
 var := 'SELECT EMPNO
         FROM EMP
         WHERE EMPNO = 10
         AND JOB= ''CLERK''';
 Open cur For var;
End;
/

Procedure created.

When i execute it it says PL/SQL procedure successfully completed but it does display any output.Also i did

SQL> Set serveroutput on

Declare
 cur package_test.ref_cur;
 vempno number;
Begin
 PR_CAD('r',cur);
 Loop
  Fetch cur Into vempno;
  Exit When cur%notfound;
  dbms_output.put_line(vempno);
 End Loop;
End;
/
PL/SQL procedure successfully completed.

Please tell me why this procedure is not giving any output.

Regards,
Ritesh

[Updated on: Wed, 23 December 2009 04:25]

Report message to a moderator