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

From: EscVector <Junk_at_webthere.com>
Date: 28 Dec 2006 11:10:47 -0800
Message-ID: <1167333047.819133.5180@i12g2000cwa.googlegroups.com>

sybrandb wrote:
> 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

Hey, great response. It sounds like you've had a really bad SQLServer experience... I didn't expect an answer. I forgot to add the smiley after my question. :)

but...

"Do readers block writers in sqlserver".

I can get this to happen in Oracle as well. Very easy to do do in fact. All you need is parallel query/partitioned table and toad or any other sql query tool. Child process remain active after simple select and block additional queries who's sessions need that particular cpu. I can reproduce this in 9i every time. Haven't bother in 10g yet. System shows no activity when this happens.  Simple select from dual or logout/kill of the session holding the child process frees up the processor and then all is well. This is particular problem when ETL developers try to "monitor" their jobs in production. If the right combination is there, they "select" then go to lunch, all stops until they move on from their select. As I've said, seen this in multiple warehouses at multiple clients. Always a mystery until I show them what's happening. Remember, I'm talking about a reader "SELECT" blocking everything in Oracle on any OS in version 9i.

Have you seen this? If I have time, I'll post the an example in another thread.

"Windows has never been scalable"
With enough money, anything is scalable....,but it might not be optimal... :) (another smiley)

Any opinions on MySQL? I have to deal with that vendor too. Received on Thu Dec 28 2006 - 13:10:47 CST