populating data from temp tables

[dbajay]

I have couple of tables in my schema as transaction tables like
T150,T151,T152,T156 without data. The indexe is associated with each concern tables.

I have couple of tables like T150_TMP,T151_TMP,T152_TMP,T156_TMP in the same schema with data.

I need to transfer or populate data on the basis of above scenario.

i.e. T150_TMP TO T150, T151_TMP TO T151 etc. in the same order

For single table, it can be achieved using single SQL statement like below

insert into T150 SELECT * FROM T150_TMP

But, how can I achieve it for numbers of tables at a time using pl/sql script?

Can you please provide me the PL/SQL script to achieve it ?

Your help would highly be appreciated.

Thanks in advance.

[girirajdba]

You could create a procedure which would take a value (argument ) thats the table
where u want to insert data from the corresponding temporary table

[tamilselvan]

declare
L_stmt  varchar2(900);
L_start int := 150;
begin
  FOR J in 150..152 LOOP
      L_stmt := 'insert into T'||J||' select * from T'||J||'_TMP' ;
      dbms_output.put_line(L_Stmt) ;
      execute immediate L_stmt;
  END LOOP;
end;
/

Tamil

[LKBrwn_DBA]

-- OR -- Try dynamic sql:

set pages 0 trims on ver off feed off echo off term off
spo /tmp/_cpy_data.sql
Select 'Insert Into T'||N||' Select * From T'||N||'_TMP;'
  From (Select Level+149 N From Dual Connect By Level < 11);
spo off
set pages 99 ver on feed on term on echo on
@/tmp/_cpy_data

[tamilselvan]

Is there any bug invloved in connect by level in 9i?

See below the number of rows return in 9i and 10g.

In 9i

SQL*Plus: Release 9.2.0.5.0 - Production on Wed Jan 3 14:15:01 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: /

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> Select Level+149 N From Dual Connect By Level <= 3;

N
----------
150

In 10g

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Jan 3 15:15:28 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Enter user-name: /

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> Select Level+149 N From Dual Connect By Level <= 3;

N
----------
150
151
152


Tamil

[dbajay]

Currently, I have tested this script and it is working fine. But, If any of the table like t151 is missing then it would returns an error message. How to handle this exception in this script.


declare
L_stmt varchar2(900);
L_start int := 150;
begin
FOR J in 150..152 LOOP
L_stmt := 'insert into T'||J||' select * from T'||J||'_TMP' ;
dbms_output.put_line(L_Stmt) ;
execute immediate L_stmt;
END LOOP;
end;

Thanks a lot for your help.

[gamyers]

"Is there any bug invloved in connect by level in 9i?"
Yes and no. The bug is in the 9i CLIENT not the SERVER. As I recall, its actually in the SQL*Net rather than SQL*Plus so TOAD etc also have 'odd' responses, sometimes depending on arraysize.
Stick with the 10g SQL*Plus and you should be fine, even against a 9i database.

[tamilselvan]

I did not use 9i / 10g client.

The tests were conducted in 9i DB server and 10g DB server.

Tamil