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

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