Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Return a recordset in Oracle Stored procedure.

    Hi guys!

    New to Oracle and have a simple stored procedure. I have to return a recordset and this SP is not doing it. Please help me!! Here is a sample of the stored procedure :

    *****************************************
    CREATE OR REPLACE PROCEDURE INDIVIDUALMI.sp_gMasterPassport (GUID IN VARCHAR2) AS
    BEGIN
    SELECT INDIVIDUALMI.MasterPassport.gMasterPassportID,
    INDIVIDUALMI.MasterPassport.vchFirstName,
    INDIVIDUALMI.MasterPassport.vchLastName,
    INDIVIDUALMI.MasterPassport.vchIDNumber,
    INDIVIDUALMI.MasterPassport.bVerified,
    INDIVIDUALMI.MasterPassport.bExpired,
    INDIVIDUALMI.MasterPassport.vchReason,
    INDIVIDUALMI.MasterPassport.dtUpdatedDate
    FROM INDIVIDUALMI.MasterPassport;
    WHERE INDIVIDUALMI.MasterPassport.gMasterPassportID = GUID;
    END;

    ****************************************

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Return a recordset in Oracle Stored procedure.

    Looks like you are used to SQL Server, which works like that. In Oracle you have to use a "cursor variable", which is a bit more complicated. See here for details:

    http://download-west.oracle.com/docs...6_ora.htm#1510

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Return a recordset in Oracle Stored procedure.

    Oh yes, and there is another way using user-defined types that allows you to select from the return value of a function:

    Code:
    SQL> create type dept_type as object
      2  ( deptno number(2)
      3  , dname varchar2(14)
      4  , loc varchar2(13)
      5  );
      6  /
    
    Type created.
    
    SQL> create type dept_tab_type as table of dept_type;
      2  /
    
    Type created.
    
    SQL> create or replace function get_depts return dept_tab_type as
      2    dept_tab dept_tab_type := dept_tab_type();
      3    i integer := 0;
      4  begin
      5    for r in (select * from dept) loop
      6      dept_tab.extend;
      7      i := i+1;
      8      dept_tab(i) := r;
      9    end loop;
     10    return dept_tab;
     11* end;
    SQL> /
    
    Function created.
    
    SQL> select * from table(cast(get_dept() as dept_tab_type));
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    Even better, in 9i you can use pipelined functions:

    http://download-west.oracle.com/docs...subs.htm#19677

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •