Re: ResultSet from Stored Procedure

Luciano Belotto wrote:

> wow that's alot more complicated than I thought it would be. I would've
> thought this would be more common (and thus easier). I didn't want to have
> to create object types nor use pipelining. ( I do very much thank you for
> your help)
>
> I'm going to try your suggestion, I have to modify it so instead being a
> function it is an actual procedure with an in/out parameter. (Don't know if
> that's possible but i'll try). We use Sybase Powerbuilder, and for
> datawindows to come from stored procs it requires a cursor as the parameter
> (it won't work with functions).
>
> My goal in this has been to simply return a result set to the Powerbuilder
> datawindow, that contains all the data as I already want it, all work done
> in the stored procedure.
>
> --
> Luciano Belotto
> replace spam with traxsoftware for e-mail
>
> "Thomas Kyte" <tkyte_at_oracle.com> wrote in message
> news:afac1h02fpa_at_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
> >

Then look at the example I posted.

Dan Morgan Received on Tue Jun 25 2002 - 22:33:35 CDT