Re: ResultSet from Stored Procedure

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 25 Jun 2002 11:16:17 -0700
Message-ID: <afac1h02fpa@drn.newsguy.com>

In article <af7rgv$h0o_at_dispatch.concentric.net>, "Luciano says...
>
>I've read the article at http://osi.oracle.com/~tkyte/ResultSets/index.html
>but it only shows examples of returning a ResultSet which is a cursor (which
>itself was from a select)
>
>What I'd like to do is have a result set, then do some manipulations on that
>data inside the stored procedure itself, then return the modified resultSet.
>Can anyone give a simple example of this?
>
>--
>Luciano Belotto
>replace spam with traxsoftware for e-mail
>
>
>
>
>
>

Depends on the release. In 9i, it would look like this:

ops$tkyte_at_ORA9I.WORLD> create or replace type myScalarType as object   2 ( x number,
  3 y date,
  4 z varchar2(25)
  5 )
  6 /

Type created.

ops$tkyte_at_ORA9I.WORLD>
ops$tkyte_at_ORA9I.WORLD> create or replace type myTableType as table of myScalarType
  2 /

Type created.

ops$tkyte_at_ORA9I.WORLD>
ops$tkyte_at_ORA9I.WORLD> create or replace function f return myTableType   2 PIPELINED
  3 as
  4 begin

  5          for x in ( select * from scott.emp )
  6          loop
  7                  x.ename := initCap( x.ename );
  8                  x.hiredate := x.hiredate+20;
  9  
 10                  pipe row (myScalarType(x.empno, x.hiredate, x.ename ) );
 11          end loop;
 12          return;

 13 end;
 14 /

Function created.

ops$tkyte_at_ORA9I.WORLD>
ops$tkyte_at_ORA9I.WORLD> select * from table( f() );

         X Y Z

---------- --------- -------------------------
      7369 06-JAN-81 Smith
      7499 12-MAR-81 Allen
      7521 14-MAR-81 Ward
      7566 22-APR-81 Jones
      7654 18-OCT-81 Martin
      7698 21-MAY-81 Blake
      7782 29-JUN-81 Clark
      7788 29-DEC-82 Scott
      7839 07-DEC-81 King
      7844 28-SEP-81 Turner
      7876 01-FEB-83 Adams
      7900 23-DEC-81 James
      7902 23-DEC-81 Ford
      7934 12-FEB-82 Miller

14 rows selected.

In 8i, if your have a SMALL number of rows to return, you can do this:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace function f return myTableType
  2 as
  3 l_data myTableType := myTableType();   4 begin

  5          for x in ( select * from scott.emp )
  6          loop
  7                  x.ename := initCap( x.ename );
  8                  x.hiredate := x.hiredate+20;
  9                  l_data.extend;
10                  l_data(l_data.count) := myScalarType(x.empno, x.hiredate,
x.ename );
 11          end loop;
 12          return l_data;

 13 end;
 14 /

Function created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> variable x number
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from table( cast( f() as myTableType

));

         X Y Z

---------- --------- -------------------------
      7369 06-JAN-81 Smith
      7499 12-MAR-81 Allen
      7521 14-MAR-81 Ward
      7566 22-APR-81 Jones
      7654 18-OCT-81 Martin
      7698 21-MAY-81 Blake
      7782 29-JUN-81 Clark
      7788 29-DEC-82 Scott
      7839 07-DEC-81 King
      7844 28-SEP-81 Turner
      7876 01-FEB-83 Adams
      7900 23-DEC-81 James
      7902 23-DEC-81 Ford
      7934 12-FEB-82 Miller

14 rows selected.

but note that here you FILL the array up and then return it -- if you have thousands of rows, this will not be realistic.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Tue Jun 25 2002 - 13:16:17 CDT