Home » SQL & PL/SQL » SQL & PL/SQL » problem with ref cursor (Oracle, 10g, Windows Server 2005)
problem with ref cursor [message #436380] Wed, 23 December 2009 04:20 Go to next message
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

Re: problem with ref cursor [message #436382 is a reply to message #436380] Wed, 23 December 2009 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
        WHERE DEPTNO = 10

         WHERE EMPNO = 10

Do you see the difference?

Regards
Michel
Re: problem with ref cursor [message #436385 is a reply to message #436382] Wed, 23 December 2009 04:34 Go to previous message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Thanks Michel, it worked
Previous Topic: Export
Next Topic: need help for insert
Goto Forum:
  


Current Time: Fri Dec 02 18:18:35 CST 2016

Total time taken to generate the page: 0.14366 seconds