Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL help needed

Re: PL/SQL help needed

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 31 May 1999 20:35:24 GMT
Message-ID: <3759efc3.12790531@newshost.us.oracle.com>


A copy of this was sent to "Thomas Klinger" <t.klinger_at_mobilkom.at> (if that email address didn't require changing) On Mon, 31 May 1999 11:56:45 +0200, you wrote:

>Hi there!
>
>I've written this script:
>
>begin
> select bts.name_bsc BSC,
> bts.name_bts BTS,
> bts.addr_bts ADDRESS,
> bts.ci CI,
> bts.trx1 TRX1,
> bts.frt1 FRT1,
> trx2.trx2 TRX2,
> trx2.frt2 FRT2,
> trx3.trx3 TRX3,
> trx3.frt3 FRT3,
> trx4.trx4 TRX4,
> trx4.frt4 FRT4,
> trx5.trx5 TRX5,
> trx5.frt5 FRT5,
> trx6.trx6 TRX6,
> trx6.frt6 FRT6
> from BTS_TRX_TEMP bts,
> TRX_FRT_2_TEMP trx2,
> TRX_FRT_3_TEMP trx3,
> TRX_FRT_4_TEMP trx4,
> TRX_FRT_5_TEMP trx5,
> TRX_FRT_6_TEMP trx6
> where bts.bts_id in (select bts2.bts_id from BTS_TRX_TEMP bts2)
> and bts.bts_id = trx2.bts_id
> and bts.bts_id = trx3.bts_id
> and bts.bts_id = trx4.bts_id
> and bts.bts_id = trx5.bts_id
> and bts.bts_id = trx6.bts_id
> order by bts.name_bts;
>
> if bts.bts_id not in (select trx21.bts_id from TRX_FRT_2_TEMP trx21) then
> trx2.trx2 := '-';
> trx2.frt2 := '-';
> end if;
>end;
>
>Q1: is this acceptable (executeable)?

nope

>Q2: I can't start it. I type in "@myscript.sql" but comes up with the next
>line numbers even if I type in a semicolon -> next line. Why?
>

need a SLASH "/" at the end to run plsql blocks in sqlplus.. for example:

SQL> begin

   2 dbms_output.put_line( 'Hello World' );    3 end;
   4 /
Hello World

PL/SQL procedure successfully completed.

I'm not sure exactly what you are trying to do in the above. You have a "SELECT" but you don't select it into anything, nor are you using a cursor (so it has no place to go). Also, you are refering to bts.bts_id in an IF statement -- but bts is a table alias, not a plsql variable. You also assign to trx2.trx2 and frt2 -- but again trx2 is a table, not a variable.

It *looks* like you might want to update every row in trx2 that satisfies the query AND bts.bts_id is not in that other table? If so, and you wanted to do it in plsql, it could look like this:

begin

  for c1 in ( select  bts.name_bsc BSC,
                      bts.name_bts BTS,
                      bts.addr_bts ADDRESS,
                      bts.ci CI, ... (your other columns here)
                      trx6.frt6 FRT6, 
                      TRX2.ROWID RID  -- <<< new column here
                 from BTS_TRX_TEMP bts,
                      TRX_FRT_2_TEMP trx2,
                      TRX_FRT_3_TEMP trx3,
                      TRX_FRT_4_TEMP trx4, 
                      TRX_FRT_5_TEMP trx5,
                      TRX_FRT_6_TEMP trx6
                where bts.bts_id in (select bts2.bts_id from BTS_TRX_TEMP bts2)
                  and  bts.bts_id = trx2.bts_id 
                  and  bts.bts_id = trx3.bts_id
                  and  bts.bts_id = trx4.bts_id
                  and  bts.bts_id = trx5.bts_id
                  and  bts.bts_id = trx6.bts_id
                order by bts.name_bts )
  loop
     select count(*) 
       into l_cnt
       from dual 
      where NOT EXISTS ( select NULL from trx_frt_2_temp trx21
                           where trx21.bts_id = C1.btx_id );

     if ( l_cnt = 1 ) 
     then 
         update trx_frt_2_temp set trx2 = '-', frt2 = '-' 
           where rowid = C1.RID;
     end if;

  end loop;

end;

or something similar.

The above

You might consider breezing through the plsql manual -- especially the control structures sections, to get a feeling for plsql and such.  

>This script should select from 5 different tables where not all values
>bts.bts_id are in each from the other tables. So there I tried to do a
>PL/SQL script do prevent that only those rows are selected where bts.bts_id
>is in all tables.
>Or is there any other possiblilty?
>
>Thanx in forward.

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon May 31 1999 - 15:35:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US