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

Paul wrote:
> Thanks a lot all.
>
> What I have been asked to do is just grabbing some simple information
> from DB. I am an application developer and get used to embedded sql,
> but my new company policy only allows stored proc (admin reason) and
> that's something I don't know. That's why I am trying to ask and see
> how I can do this simple thing in stored proc quickly.
>
> Paul
>
>
>
> On Dec 29, 8:02 am, "hpuxrac" <johnbhur..._at_sbcglobal.net> 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.
>>> PaulSelect into is a construct best avoided.  Look at using oracle tables
>> and bulk select capabilities.- Hide quoted text -- Show quoted text -

You can ... but please don't top post here at c.d.o. Scroll to the bottom to reply.

For some very straight-forward easy to understand demos try these links: http://www.psoug.org/reference/anonymous_blocks.html http://www.psoug.org/reference/procedures.html

Ideally in Oracle you should be using packages, not stored procedures. http://www.psoug.org/reference/packages.html

For general demos:
http://www.psoug.org/library.html

HTH

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org