Re: a simple PL/SQL question about getting a list of values to output parameters

sybrandb wrote:
> Paul wrote:
> > Hi,
> >
> > I am newbie to PL/SQL and have a problem of passing OUT parameters.
> >
> >
> > If I have a procedure like this:
> >
> > ========================
> > CREATE OR REPLACE PROCEDURE MYPROC (myinput IN varchar2, output1 out
> > varchar2, output2 out varchar2)
> > AS
> > BEGIN
> > select field1, field2 into output1, output2 from mytable where
> > field3 like myinput;
> > END MYPROC
> >
> > ========================
> >
> > The sql statement returns more than one row from "mytable". I found
> > that everytime when I try to invoke the procedure the "output1" and
> > "output2" never give me anything, just NULL value.
> >
> > What is the correct way to get that two fields?
> >
> >
> > Thanks a lot.
> >
> > Paul
>
>
> The correct way is to start to acknowledge
>
> Oracle != Sqlserver
> Oracle != Sqlserver
> Oracle != Sqlserver
>
> Say after me
> Oracle != Sqlserver
>
> That said, you should *truly* study the application developers manual.
> The procedure you posted above is a Sqlserver procedure ported to
> Oracle.
> Obviously that won't work.
> First of all
> select into can only retrieve one row.
> Secondly : a proper procedure needs exception handling.
> You need to deal with
> exception
> when no_data_found then
> ....
> when too_many_rows then
> ....
> when others then raise
>
> If your select truly returns more than 1 record it should have raised
> the too_many_rows exception (ora-1002) *in your face*, and the
> procedure should have crashed.
> However, as you incorrectly specify
> like myinput
> instead of
> like myinput||'%'
> your procedure probably doesn't return anything.
>
> If your select truly retrieves multiple records, the variables should
> either be defined as a COLLECTION, or you should output a REF CURSOR.
>
> However, in that case you are just porting your bad sqlserver habits to
> Oracle, and also this forum is not the place for a complete PL/SQL
> course.
>
> As Oracle isn't sqlserver, but much more, and you can't learn Oracle
> without reading manuals, please get the PL/SQL reference manual, and
> the Application Developers Manual at http://tahiti.oracle.com.
> It's all there!!!
>
> --
> Sybrand Bakker
> Senior Oracle DBA

I love manuals too, but here are some links to RefCursors examples that others found useful.

http://www.oracle-base.com/articles/8i/UsingRefCursorsToReturnRecordsets.php

http://www.orafaq.com/node/980

Sybrand, what's wrong with SQLServer? Received on Thu Dec 28 2006 - 11:22:31 CST