RE: passing ref cursor on a procedure
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_idNUMBER(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).
RE: passing ref cursor on a procedure
Title: passing ref cursor on a procedure 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- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Hatzistavrou John Sent: Wednesday, June 04, 2003 8:40 AM To: Multiple recipients of list ORACLE-L Subject: passing ref cursor on a procedure 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
RE: passing ref cursor on a procedure
Title: passing ref cursor on a procedure Oops Its supposed to be: Variable lCursor REFCURSOR; Not: declare lCursor REFCURSOR; Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Neyman Sent: Wednesday, June 04, 2003 9:40 AM To: Multiple recipients of list ORACLE-L Subject: RE: passing ref cursor on a procedure 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- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Hatzistavrou John Sent: Wednesday, June 04, 2003 8:40 AM To: Multiple recipients of list ORACLE-L Subject: passing ref cursor on a procedure 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
RE: passing ref cursor on a procedure
Title: passing ref cursor on a procedure example -Original Message-From: Hatzistavrou John [mailto:[EMAIL PROTECTED]Sent: Wednesday, June 04, 2003 2:40 PMTo: Multiple recipients of list ORACLE-LSubject: passing ref cursor on a procedure 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 * This electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible. This footnote also confirms that this message has been swept for computer viruses. **
RE: passing ref cursor on a procedure
Title: passing ref cursor on a procedure Example Package ll is Type lrec is record( ? A ?integer, ? B? varchar2(72)); Type lrectype is REF CURSOR return lrec; Procedure getl( par1 in varchar2, ?? Xcursor IN OUT lrectype); End; Now , HOW do I execute procedure getl from sqlplus? Kind Regards, Hatzistavrou Yannis ? -Original Message- From: Regis Biassala [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 04, 2003 6:05 PM To: Multiple recipients of list ORACLE-L Subject: RE: passing ref cursor on a procedure example -Original Message- From: Hatzistavrou John [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 04, 2003 2:40 PM To: Multiple recipients of list ORACLE-L Subject: passing ref cursor on a procedure 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 * This electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible. This footnote also confirms that this message has been swept for computer viruses. **
