Re: need help with a query, please

From: <rtproffitt_at_my-deja.com>
Date: 2000/07/18
Message-ID: <8l1vkr$teh$1@nnrp1.deja.com>#1/1

Joe,
You can write a PL/SQL block to create
dynamic SQL...Here is how you would do it with SQL/Plus...You can adapt the technique for using the DBMS_SQL package if you need to.

[cut]
> I have some 250 tables....
> select orderid, tracenumber, username
> from sp236_transaction
> where orderid = '963262260741'
>
> the sp236 is the only thing that changes
set serverout on size 20000
set head off
set pages 0
Declare
  vOrderID Varchar2(50) := '&Desired_Order_ID'; Begin
  For i in 1 .. 256 loop
    Dbms_Output.Put_Line(

      'select orderid, tracenumber, username '||chr(10)||
      'from sp'||to_char(i)||'_transaction where orderid = '||
      ''''||vOrderID||''';');

  End Loop;
End;
.
Spool MyCmdFile.sql
/
Spool off
@MyCmdFile.sql

HTH
Robert Proffitt
Beckman Coulter

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Jul 18 2000 - 00:00:00 CDT