| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: ResultSet from Stored Procedure
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...Received on Tue Jun 25 2002 - 15:15:42 CDT
> 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
>
 |
 |