If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Return a recordset in Oracle Stored procedure.

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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;

****************************************
Reply With Quote
  #2 (permalink)  
Old
Moderator.
 
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
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #3 (permalink)  
Old
Moderator.
 
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
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On