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

Paul a écrit :

> 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".

The sql statement must return exactly one row.

You should get an ORA-01422 if the query returns more than one row, or an ORA-01403 if it returns none.

> 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?

The procedure is correctly written as far as Oracle is concerned.

If the select indeed returns many - or no - rows and you see NULLs, then perhaps some calling code is trapping the Oracle exception - not a very good proposition, for precisely the reasons that you are witnessing...

After finding out what's going on in the current state of affairs, you may of course have to change the procedure so that it works for a multiple values answer.

Have a nice day, Paul

• Raoul