Re: select in a loop

On Jul 11, 9:32 am, "alexander.stuckenh..._at_fernuni-hagen.de" <alexander.stuckenh..._at_gmail.com> wrote:
> On 11 Jul., 16:11, sybrandb <sybra..._at_gmail.com> wrote:
>
>
>
>
>
> > On Jul 11, 3:40 pm, "alexander.stuckenh..._at_fernuni-hagen.de"
>
> > <alexander.stuckenh..._at_gmail.com> wrote:
> > > Hello everyone,
>
> > > I just started with PL/Sql and directly have a problem. I want to
> > > iterate in a loop and use an entry in a where-statement of a pretty
> > > complex query.
>
> > > I did the following:
>
> > > BEGIN
> > > FOR item IN
> > > (
> > > SELECT * FROM test
> > > )
> > > LOOP
> > > select * from (pretty complex subquetries...) where something.id =
> > > item.id
> > > END LOOP;
> > > END;
>
> > > Unfortunately this does not seem to work. Does anybody of you have an
> > > idea?
>
> > > Best regards,
>
> > > Alex
>
> > Yes, several.
> > First of all I don't see a specific error message.
> > Secondly I don't see a version
> > Thirdly I don't see any subquery
> > Fourthly I don't see select into inside the loop, I only see SELECT
> > ending up nowhere
> > Fifthly, it looks like I see an attempt to develop an unscalable piece
> > of code by rewriting a proper query
> > select * from (pretty complex queries where something.id in (select
> > item.id from test) into a CURSOR FOR loop.
> > People developing code like that should either learn SQL or be shown
> > to the door of unemployment, and hopefully they will never got a job
> > in a situation, where tired and frustated DBAs are fully occupied to
> > clean out their messes.
>
> > --
> > Sybrand Bakker
> > Senior Oracle DBA- Zitierten Text ausblenden -
>
> > - Zitierten Text anzeigen -
>
> Your majesty Bakker,
>
> please forgive me my ignorance in the creation of sql masterpieces and
> the resulting affront against your brilliant mind. You may rest
> assured, that my area of expertise is not the administration of
> databases and I will not try to take away your job in the future.
>
> Unfortunately even the smartest genius is worth nothing in a company,
> when he is not willing to share his ideas with others. So, if I was
> your boss and would read your last message, you had an appoint with me
> soon.
>
> Best regards and thanks for nothing,
>
> Alex- Hide quoted text -
>
> - Show quoted text -

It's a shame you think Sybrand's response provided you nothing in the way of constructive advice; his fourth point is key to solving this problem you've created. Possibly this makes you the Court Jester?

You cannot simply code a SELECT statement in the body of a loop the way you've attempted to do without using the INTO clause. Period. Your select should be coded:

select <column list here> into <variable list here> from (pretty complex subquetries...) where something.id = item.id;

Your use of 'select *' is one of the surest ways to guarantee an application won't scale and could possibly break should a table or view definition change. Using an explicit column list provides readability and accessibility to anyone else who may be called upon to support code you've written. It also ensures you have properly coded your INTO list, matching resultset values to intended variables.

Yes, you need to declare these variables BEFORE you attempt to use them. I would seriously consider purchasing 'Oracle PL/SQL Programming, 4th edition", by Steven Feuerstein with Bill Pribyl were I you.

David Fitzjarrell Received on Wed Jul 11 2007 - 10:07:48 CDT