Re: PL/SQL versus Pro*C

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 03 Sep 1998 21:16:10 GMT
Message-ID: <3614066a.116444077@192.86.155.100>

A copy of this was sent to "gp" <gp_at_gp> (if that email address didn't require changing) On Thu, 3 Sep 1998 18:16:05 +0100, you wrote:

>Hello,
>
> Is there a way to do the following using PL/SQL
>(avoiding temporary tables)?
>Using Pro*C is almost trivial.
>
>Thanks in advance,
>greg
>
>
>exec sql
> open cursor c1 for
> select A
> from table1;
>
>for(;;)
>{
> exec sql fetch cursor c1 into :varA;
> if(ORA_NOT_FOUND)
> break;
> else
> {
> printf("%.*s\n", varA.len, varA.arr);
> exec sql
> select B
> into :varB
> from table2
> where C= :varA;
> printf("%.*s\n", varB.len, varB.arr);
> }
>}
>
>

Using PL/SQL to do this *is* trivial:

declare

   varB b.table2%type;
begin
  for c1 in ( select A from table1 ) loop

     dbms_output.put_line( c1.a );
     select b into varB from table2 where C = c1.a;
     dbms_output.put_line( varB );

  end loop;
end;
/

The above block (which could be a procedure) would run in sqlplus. You must remember to issue:

SQL> set serveroutput on size 1000000

before doing that -- dbms_output only pumps out data if you enable it.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Sep 03 1998 - 16:16:10 CDT