Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: a simple PL/SQL question about getting a list of values to output parameters

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

From: <hasta_l3_at_hotmail.com>
Date: 28 Dec 2006 02:18:01 -0800
Message-ID: <1167301081.721656.259230@a3g2000cwd.googlegroups.com>


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

Received on Thu Dec 28 2006 - 04:18:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US