Home » SQL & PL/SQL » SQL & PL/SQL » Help needed using REF CURSOR
Help needed using REF CURSOR [message #219559] Thu, 15 February 2007 00:13 Go to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Hello All,

I am new to using REF CURSOR.
can anyone help me in letting me know how to use it?

This is the scenario...

Package specification:
######################################
create or replace package test_pack is

type test_cur is REF CURSOR;

procedure test_proc(rep_type in number, obj_cur out test_cur);

end test_pack;
######################################

Package body:
######################################
create or replace package body test_pack is

procedure test_proc(rep_type in number, obj_cur out test_cur) is
begin

if rep_type = 1 then
open obj_cur for
select empno eno,
empname ename
from emp;

elsif rep_type = 2 then
open obj_cur for
select empno eno,
empname ename,
deptno dno
from emp;
end if;

end test_proc;
end test_pack ;
######################################

Calling the procedure test_proc as follows:
######################################
declare
obj_cur test_pack.test_cur;
begin
test_pack.test_proc(1,obj_cur);
-- <How to display the values that are returned by ref cursor?>
test_pack.test_proc(2,obj_cur);
-- <How to display the values that are returned by ref cursor?>
end;
######################################

Can anyone let me know how to display the values that are returned by ref cursor?

How can we use the alias names eno, ename, etc in the 3rd program given above?

Thanks in advance,
prashas_d.




Re: Help needed using REF CURSOR [message #219565 is a reply to message #219559] Thu, 15 February 2007 00:29 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Take a look at the examples in the manual.

Ross Leishman
Re: Help needed using REF CURSOR [message #219834 is a reply to message #219565] Fri, 16 February 2007 04:47 Go to previous messageGo to next message
prashas_d
Messages: 66
Registered: February 2007
Member
I have followed the manual and modified the calling program(3rd program that I listed)

Now, it is giving me an error as follows:

ORA-06550: line 14, column 24:
PLS-00321: expression 'REC_CUR' is inappropriate as the left hand side of an assignment statement
ORA-06550: line 14, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 18, column 26:
PLS-00330: invalid use of type name or subtype name
ORA-06550: line 18, column 5:

I have changed the calling program as follows:
#############################
declare
obj_cur test_pack.test_cur;
TYPE rec_cur IS RECORD(
empno NUMBER(10),
ename VARCHAR2(20),
dept VARCHAR2(20));
begin
test_pack.test_proc(1,obj_cur);
LOOP
FETCH obj_cur INTO REC_CUR;
EXIT WHEN obj_cur%notfound;
DBMS_OUTPUT.PUT_LINE(REC_CUR.empno);
END LOOP;
end;
##################################
Re: Help needed using REF CURSOR [message #219873 is a reply to message #219834] Fri, 16 February 2007 08:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rec_cur is a type, not a variable.
You have to define a variable with datatype rec_cur.
Re: Help needed using REF CURSOR [message #220107 is a reply to message #219873] Sun, 18 February 2007 21:18 Go to previous message
prashas_d
Messages: 66
Registered: February 2007
Member
Frank/Leishman,

Thanks a lot, it's working now Smile

prashas_d
Previous Topic: DBMS_DLL.ANALYZE_OBJECT
Next Topic: Multi byte in Oracle
Goto Forum:
  


Current Time: Sat Dec 03 22:00:27 CST 2016

Total time taken to generate the page: 0.10274 seconds