Re: PL/SQL question

From: GovindanK <gkatteri_at_fastmail.fm>
Date: Wed, 28 Sep 2005 17:59:57 UT
Message-Id: <1127930397.17075.243965585@webmail.messagingengine.com>

Manoj

My approach (as i have done in the past on more than one occassions) would be to use a plsql block to generate an SQL script on the fly and run the same from SQL*Plus prompt. I would not be using Dynamic SQL for these one off (i suppose) updates.

HTH GovindanK

> set timing on
> set serverout on size 1000000
> declare
> l_commit_interval number := 5000;
> l_where_clause varchar2(2000);
> l_cnt number := 0;
> l_owner varchar2(25) := 'OWNER1';
> l_index_cnt number := 4;
> type txn_rec_tab_cnt is record
> (
> tab_name varchar2(70),
> tab_aff_rows number(6):= 0
> );
> type t_tab_cnt is table of txn_rec_tab_cnt index by
binary_integer;
> txn_tab_cnt t_tab_cnt;
> cursor c1 is select ref_num from trans where country_code =
'KK';
> cursor c2 is select a.owner||'.'||a.table_name table_name
,b.column_name
> from all_tab_columns a
> ,all_tab_columns b
> where a.column_name = 'CTRY'
> and a.table_name = b.table_name
> and b.column_name in

('REF_NUM','BILL_REF_NUM','FLDR_T2_ID','TXN_REF_NUM')
> and a.owner = l_owner
> and b.owner = l_owner;

>

> begin
> for curs1 in c1
> loop
> l_cnt := l_cnt + 1;
> l_index_cnt := 4;
> for curs2 in c2
> loop
> l_where_clause := ' where '||curs2.column_name || ' =
:col1';
> execute immediate 'update '||curs2.table_name||' set
ctry_cd = ''KK'''|| l_where_clause
> using curs1.ref_num;
> txn_tab_cnt(l_index_cnt).tab_name :=
curs2.table_name;
> txn_tab_cnt(l_index_cnt).tab_aff_rows:=
txn_tab_cnt(l_index_cnt).tab_aff_rows+sql%rowcount;
> l_index_cnt := l_index_cnt + 1;
> end loop;
> if mod(l_cnt,l_commit_interval) = 0 then
> commit;
> end if;
> end loop;
> commit;
> for j in 1..txn_tab_cnt.count
> loop
> dbms_output.put_line('No of rows updated in
'||txn_tab_cnt(j).tab_name||' = '
> ||txn_tab_cnt(j).tab_aff_rows);
> end loop;
> end;
> /

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 28 2005 - 13:02:15 CDT