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

From: sybrandb <sybrandb_at_gmail.com>
Date: 28 Dec 2006 10:21:41 -0800
Message-ID: <1167330101.685437.214810@n51g2000cwc.googlegroups.com>

EscVector wrote:
> 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?

Essentially sqlserver is a *toy*.
Please answer the following questions
- on how many scalable OS-es sqlserver runs, excluding Windows (as Windows has never been scalable)
- Do readers block writers in sqlserver
- Does t-sql uses the concept of a 'procedure' in its proper sense, as defined in Information Science?
I could go on for centuries.
Basically
- Microsoft didn't develop sqlserver, but *bought* it (as with all of their products)

- technologically speaking sqlserver is at least 10 years behind Oracle
- sqlserver is an example of vendor lock-in.
- vendor lock-in is also enforced by Microsoft not supporting (in their

ODBC and related products) any Oracle feature beyond 7.3.

-- 
Sybrand Bakker
Senior Oracle DBA

Received on Thu Dec 28 2006 - 12:21:41 CST