| ref cursor question [message #37021] |
Thu, 10 January 2002 08:16  |
jj
Messages: 11
Registered: July 2001
|
Junior Member |
|
|
Hello,
I've created a function (state_name) where there is an IN variable - state_list (ie. 'WI','IL' )- that has a series of state abbreviations. I am trying to return a list of state names that match the state abbriviations. The code below compiles but doesn't return anything. Any help on the problem would be greatly appreciated.
FUNCTION state_name (
state_list IN VARCHAR2)
RETURN VARCHAR2
IS
type r is ref cursor;
r1 r;
state_out VARCHAR2 ;
ret S_table%rowtype;
BEGIN
open r1 for 'SELECT abbr,name FROM S_table WHERE abbr IN ('|| state_list ||') ORDER BY abbr';
Loop
FETCH r1 INTO ret;
exit when r1%notfound;
state_out := state_out || ' ' || ret.name;
END LOOP;
RETURN state_list;
END state_name;
|
|
|
|
| Re: ref cursor question [message #37022 is a reply to message #37021] |
Thu, 10 January 2002 09:09  |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
1)return state_out variable
2) call this function like below
var1:= state_name('''WI'',''IL''');
Note : all single quotes..
FUNCTION state_name (
state_list IN VARCHAR2)
RETURN VARCHAR2
IS
type r is ref cursor;
r1 r;
state_out VARCHAR2(200) ;
ret S_table%rowtype;
BEGIN
open r1 for 'SELECT abbr,name FROM S_table WHERE abbr IN ('|| state_list ||') ORDER BY abbr';
Loop
FETCH r1 INTO ret;
exit when r1%notfound;
state_out := state_out || ' ' || ret.name;
END LOOP;
RETURN state_out;
END state_name;
|
|
|
|
| Re: ref cursor question [message #38191 is a reply to message #37021] |
Fri, 29 March 2002 09:50 |
Krishna P Puvvula
Messages: 1
Registered: March 2002
|
Junior Member |
|
|
Hello,
For simple select statement I used ref. cursor
How to see what data is there in the ref. cursor at the end of the procedure. Here is PKG code, Procedure code, Test the data at SQL prompt code,
PKG code:
CREATE OR REPLACE PACKAGE KPPKG AS
TYPE RefCurTyp IS REF CURSOR ;
PROCEDURE KPPROC (p_sno number, rec OUT KPPKG.RefCurTyp);
END KPPKG;
PROCEDURE code:
create or replace procedure kpproc (p_sno number, rec OUT KPPKG.RefCurTyp) as
begin
open rec for
select * from kp where sno = p_sno;
end;
code for Test the data in ref cursor at SQL prompt:
declare
rec1 kppkg.RefCurTyp;
begin
kppkg.kpproc(1, rec1);
for i in rec1
loop
dbms_output.put_line(i.sno || i.sname);
end loop;
end;
Can you help me please what is the wrong in my code for "test the data in ref cursor"
Thanks.
Prasad.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
