RE: passing ref cursor on a procedure

From: Mohammed Shakir <mshakir08816_at_yahoo.com>
Date: Mon, 09 Jun 2003 16:51:49 -0700
Message-ID: <F001.005AE030.20030609162421@fatcity.com>

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).

Received on Mon Jun 09 2003 - 18:51:49 CDT