I used the following program to learn bind variables and ref cursor
long time ago. This might help. Put this code into a file called
getstaff.sql and then run it. Bottom lines show how to declare a cursor
and pass it to a procedure using SQL.
HTH
Mohammed Shakir
------------------
rem filename getstaff.sql
rem uses employee table(emp ??) from scott/tiger schema on oracle
rem you can use bind variables, then cursor can not have the return
type??.
rem You can return the ref cursor, see the multir~2.sql
rem This program works
rem USAGE: sqlplus scott/tiger @getstaff.sql
rem Originally this was multir~1.sql
drop package emp_data;
CREATE PACKAGE emp_data AS
TYPE EmpRecTyp IS RECORD (
emp_id NUMBER(4),
emp_name CHAR(10),
job_title CHAR(9),
dept_name CHAR(14),
dept_loc CHAR(13));
TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
PROCEDURE get_staff (
dept_no IN NUMBER,
emp_cv IN OUT EmpCurTyp);
END;
/
CREATE PACKAGE BODY emp_data AS
PROCEDURE get_staff (
dept_no IN NUMBER,
emp_cv IN OUT EmpCurTyp) IS
BEGIN
OPEN emp_cv FOR
SELECT empno, ename, job, dname, loc FROM emp, dept
WHERE emp.deptno = dept_no AND emp.deptno = dept.deptno
ORDER BY empno;
END;
END;
/
COLUMN EMPNO HEADING Number
COLUMN ENAME HEADING Name
COLUMN JOB HEADING JobTitle
COLUMN DNAME HEADING Department
COLUMN LOC HEADING Location
SET AUTOPRINT ON
VARIABLE cv REFCURSOR
EXECUTE emp_data.get_staff(20, :cv);
--- Igor Neyman <[EMAIL PROTECTED]> wrote:
> Oops.
>
>
>
> It's supposed to be:
>
>
>
> Variable lCursor REFCURSOR;
>
>
>
> Not: "declare lCursor REFCURSOR;"
>
>
>
> Igor Neyman, OCP DBA
>
> [EMAIL PROTECTED]
>
>
>
>
>
> -----Original Message-----
> Neyman
> Sent: Wednesday, June 04, 2003 9:40 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> If in SQL*Plus:
>
>
>
> Just declare variable of REFCURSOR type and pass it to stored
> procedure:
>
>
>
> DECLARE lCursor REFCURSOR;
>
> Begin
>
> <procedure_name>(par1, par2, ., :lCursor);
>
> end;
>
> /
>
>
>
> Igor Neyman, OCP DBA
>
> [EMAIL PROTECTED]
>
>
>
>
>
> -----Original Message-----
> Hatzistavrou John
> Sent: Wednesday, June 04, 2003 8:40 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Dear All,
>
> I have a vendor procedure which I wish to run . This procedure takes
> as
> input variables a varchar and a ref_cursor.
>
> I know the query that is related to the ref_cursor and I wish to
> implicitly pass it to the procedure . How can this be done?
>
> Kind Regards,
>
>
>
> Hatzistavrou Yannis
>
>
=====
Mohammed Shakir
CompuSoft, Inc.
11 Heather Way
East Brunswick, NJ 08816-2825
(732) 672-0464 (Cell)
(732) 257-6001 (Home)
__________________________________
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mohammed Shakir
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
