Re: Calling stored procs and functions in PL/SQL using Cursor Variables
Date: 1998/04/15
Message-ID: <353601d5.21004462_at_192.86.155.100>#1/1
A copy of this was sent to NOdominic.klein_at_ventura-uk.comSPAM (Dominic Klein) (if that email address didn't require changing) On Wed, 15 Apr 1998 14:50:35 GMT, you wrote:
The problem is that you are trying to "print" a cursor as though it was a string. That doesn't make sense in pl/sql. A cursor is something you open, fetch from and close.
Here is an example using cursor vars. The first run of cv_demo.open_cursor shows how you can use SQL*Plus to 'print' a cursor. The second example shows how to process the data in pl/sql
create or replace package cv_demo
as
type my_rec is record
(
ename emp.ename%type,
mgr emp.mgr%type
);
type emp_cur is ref cursor return my_rec;
procedure open_cursor( p_ename_like in varchar2,
p_cur_rec in out emp_cur );
end;
/
create or replace package body cv_demo
as
procedure open_cursor( p_ename_like in varchar2,
p_cur_rec in out emp_cur )
is
begin
open p_cur_rec for select ename, mgr
from emp
where ename like upper(p_ename_like);
end;
end;
/
REM SQLPLUS example
variable RefCur refcursor
begin
cv_demo.open_cursor( '%a%', :refCur );
end;
/
print refCur
REM How to do it in pl/sql...
declare
refCur cv_demo.emp_cur;
ename varchar2(50);
mgr number;
begin
cv_demo.open_cursor( '%a%', refCur ); loop
fetch refCur into ename, mgr;
exit when ( refCur%notfound );
dbms_output.put_line( ename || ',' || mgr );
end loop;
close refCur;
end;
/
>I've got a package on the server with stored procs and functions on
>and I want to be able to retrieve values from these using cursor
>variables. Now I've declared my package and procs and functions OK
>and they compile but I can't retrieve them using DBMS_OUTPUT.PUTLINE.
>I have declared the cursor as :
>
>type BLOB_REC is record
> (
> thing_id blobtest.thing_id%TYPE
> )
> ;
>type BLOBTEST_TYPE is ref cursor return BLOB_REC;
>
>in the simple procedure i just do this ...
>
>procedure p_surname
> (
> p_surname out varchar2,
> p_cur_rec in out BLOBTEST_TYPE
> )
>is
>l_surname varchar2(5);
>begin
> p_surname := 'KLEIN';
> open p_cur_rec for
> select
> THING_ID
> from
> BLOBTEST
> ;
>end; --p_surname
>
>which just returns 2 values as there are 2 recs in the table.
>
>Anyway my problem is getting the values to the screen from the calling
>program (which will be on the client) and is :
>
>set serveroutput on;
>declare
>l_surname varchar2(5);
>l_thing_id AM001.BLOBTEST_TYPE;
>Begin
> AM001.p_surname(l_surname, l_thing_id);
> dbms_output.put_line (AM001.f_test||' '||l_surname||' '||
> l_thing_id.thing_id);
>End ;
>/
>
>I'm trying to reference l_thing_id as though it is an element in a
>record.
>If i take the DBMS line out it doesn't complain but i don't know what
>it's done so that's not much use.
>Anyway can anyone shed some light on this please ? All the books I've
>seen just give examples for selecting * which is not much use !
>
>Cheers.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Apr 15 1998 - 00:00:00 CEST