Re: Dynamic FOR IN mySQL_String LOOP doSomething END LOOP, 8i

From: Billy <vslabs_at_onwe.co.za>
Date: 22 Aug 2005 22:57:21 -0700
Message-ID: <1124776641.946951.280670@o13g2000cwo.googlegroups.com>

COAST wrote:

> mySQL_String := 'select count(*) from table A'
> FOR tempData IN (mySQL_String)
> LOOP
> doSomethingWithTempData;
> END LOOP;
>
>
> Is this possible? Because it's annoying to implement different section
> for each case of the needed SQL. Because may be I need this:
>
> mySQL_String := 'select count(*) from table A where myField1 = 'Hello''
> or
> mySQL_String := 'select count(*) from table A where myField2 = 'WhatYouNeed''
> or ....

Don't use FOR loop for cursor fetches. It is bad programming practise and prohibits the use of coding bulk processing. A FOR loop for cursor processing in PL/SQL is akin to a GOTO statement. Both can be done a *lot* better.

One way to address the problem you describe is to use dynamic SQL with bind variables. E.g.
==
SQL> declare

  2     s       varchar2(20);
  3     mySQL   varchar2(4000);
  4     i       integer;
  5  begin
  6     s := 'Hello';
  7     mySQL := 'select count(*) into :I from user_objects where
object_name = :S';
  8     execute immediate mySQL into i using s;
  9     W( 'count='||i||' row(s)' );

 10* end;
SQL> /
count=0 row(s)

PL/SQL procedure successfully completed. ==

If you are dealing with data sets and not a single return, ref cursors as Sybrand mentioned, is another option. Ref cursors and dynamic SQL also allows bulk processing. You can even do bulk binding and executing of dynamic DMLs via FORALL statements.

PL/SQL is extremely capable. It is as much a programming language as Java or C/C++. In fact it is more capable than either within the Oracle context. Yet developers approach it as if it is a simplistic scripting language. PL/SQL is *not* Transact-SQL. It is not some kind of macro scripting language for Oracle.

So if you need to be annoyed, be annoyed with yourself. PL/SQL is very capable of solving very complex data processing problems in Oracle. You need to make sure that you're equally capable.

--
Billy

Received on Tue Aug 23 2005 - 00:57:21 CDT