Re: for loop mit select - Firebird -> Oracle

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 17 May 2005 17:52:44 -0700
Message-ID: <1116377328.523711@yasure>

Werner Hofmann wrote:

> DA Morgan wrote:
> 
> 

>>Werner Hofmann wrote:
>>
>>>Hallo,
>>>
>>>in a firebird-procedure I habe the statement:
>>>
>>>for select TITLE in DOCUMENTS d where d.STATUS = 'online' do
>>>begin
>>> path = 'd:\'||vUserDir||'\'||TITLE;
>>>end
>>>
>>>
>>>this I need as a PSQL statement in an oracle-procedure.
>>>How does the code In oracle looks like?
>>>
>>>Regards, Werner
>>
>>I have no idea as your code doesn't seem to do anything.
>>
>>You have what looks like a cursor FOR loop which would look
>>like this:
>>
>>FOR rec IN (select TITLE in DOCUMENTS d where d.STATUS = 'online')
>>LOOP
>>
>>END LOOP;
>>
>>But how you are going to assign multiple values, as you loop
>>through records to a single variable, PATH, and accomplish
>>anything is a mystery.
>>
>>If on the other hand this isn't a loop then just:
>>
>>SELECT title
>>INTO x
>>FROM DOCUMENTS
>>WHERE STATUS = 'online'
>>
>>path := 'd:\'||vUserDir||'\'|| x;
>>
>>Seems like you should be taking a PL/SQL class before trying
>>to write it. Why are you doing it the other way around? A
>>burning desire to write bad code and fail?

> 
> 
> Hallo Morgan,
> 
> don't worry about the sense of my example. The real code is doing some
> important things, but I wanted to know, how to make such a firebird-loop
> with oracle. And how I can access the values of one row in the loop body.
> 
> Can you show me please how to access the values in rec?
> Is rec.TITLE correct?
> 
> Excuse me for my stupid questions, but I am new in oracle, and I am stil
> waiting for my PL/SQL-Book. I think oracle is not intuitive, in no way, its
> an academic solution for db management. Very complex in the ideas and
> solutions behind them.

Understanding English is not your first language ... Firebird is a product. What you want is a FOR loop.

rec.TITLE is correct.

Go to http://www.psoug.org
click on Morgan's Library
click on Cursors & Cursor Loops for multiple examples

But if in Oracle 9i or above you should not be writing cursor FOR loops except in very specific conditions. The general solution is "bulk binding" ... also with examples in the library.

HTH

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Tue May 17 2005 - 19:52:44 CDT