insert data from same table 260 times...

[Lysen]

Hello,

I need to insert data from a table into the same table with a different company_code (CMPCODE). This has to be done for about 250 tables, so I thought I might be able to do this in Dynamic SQL. But I've no idea how to do it!
I've seen some examples on the internet, but I'm a complete newbie when it comes to oracle... :-(

So basically, what I want is a script which executes:

insert into @table_name
select new_cmpcode
,column2
,column3
,...
from @table_name

and this script has to be generated by a dynamic sql script...??

1> All tables start with OAS_name AND should contain the column CMPCODE.

2> the column to be changed is CMPCODE (the first column of the table; contains 'OAD' now, and should be inserted with 'T-OAD')

Can anyone help me with this one??

Thanks in advance for your time...

 

[aryeh1010]

Why not just update the field that has to be changed, instead of touching entire tables?

Is the field name identical throughout the database?


you could then generate a script for the update:

select 'update ' || table_name || ' set cmpcode = 'T-OAD' || ';'
from dba_tables
where table_name in
(table list)

This will work if the values are absolute.

 

[Lysen]

Hi,

update is not an option, because cmpcode 'OAD' is the production company, and 'T-OAD' is the test company.

Finance got a new tool but they want to test it first on the test company...

 

[SantaMufasa]

Lysen,

Following are three sections: 1) Original data, 2) Code, 3) Results:

[b]Section 1: Original data.[/b]

SQL> select * from oas_master;

CMPCODE                                               COLUMN2    COLUMN3
-------------------------------------------------- ---------- ----------
OAD                                                         1          1
OAD                                                         1          1
OAD                                                         2          2
OAD                                                         2          2

4 rows selected.

SQL> select * from oas_toad;

no rows selected

SQL> select * from oas_yada;

no rows selected

SQL> select * from companies;

NEW_CMPCODE                                        TABLE_NAME
-------------------------------------------------- --------------------------
T-OAD                                              OAS_TOAD
YADA                                               OAS_YADA

2 rows selected.

[b]Section 2: Code.[/b]

SQL> declare
  2   sql_stm  varchar2(2000);
  3  begin
  4   for r in (select * from companies) loop
  5    sql_stm := 'insert into '||r.table_name||
  6     ' select '''||r.new_cmpcode||''', column2, column3 '||
  7     ' from oas_master';
  8    execute immediate sql_stm;
  9   end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

[b]Section 3: Results.[/b]

SQL> select * from oas_toad;

CMPCODE                                               COLUMN2    COLUMN3
-------------------------------------------------- ---------- ----------
T-OAD                                                       1          1
T-OAD                                                       1          1
T-OAD                                                       2          2
T-OAD                                                       2          2

4 rows selected.

SQL> select * from oas_yada;

CMPCODE                                               COLUMN2    COLUMN3
-------------------------------------------------- ---------- ----------
YADA                                                        1          1
YADA                                                        1          1
YADA                                                        2          2
YADA                                                        2          2

4 rows selected.

Let me know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:59 (19Feb04) UTC (aka "GMT" and "Zulu&quot, 09:59 (19Feb04) Mountain Time)

 

[Lysen]

Hi Mufasa,

Sorry, but that's not quite what I want, but thanks for your extensive reply!

I have a table oas_yada

SQL> select * from oas_yada;

CMPCODE COLUMN2 COLUMN3
-------------------------------------------------- ---------- ----------
OAD 1 1
OAD 1 1
OAD 2 2
OAD 2 2

and a table named oas_docline:

CMPCODE COLUMN2 COLUMN3
-------------------------------------------------- ---------- ----------
OAD 1 1
OAD 1 1
OAD 2 2
OAD 2 2

And 100's more like these, just with different columns etc...

What I want is a script which takes all the tables that start with OAS_, and contain a column CMPCODE to be updated with the data it already contains BUT with CMPCODE changed to T-OAD... For table OAS_YADA the script should generate this code:

insert into OAS_YADA
select 't-oad' as cmpcode
,column1
,column2
from OAS_YADA;

Since the columns are different for every table the script should check these in the dbc.tabs table or something I guess... So after the update the table OAS_YADA should look like this:

CMPCODE COLUMN2 COLUMN3
-------------------------------------------------- ---------- ----------
OAD 1 1
OAD 1 1
OAD 2 2
OAD 2 2
T-OAD 1 1
T-OAD 1 1
T-OAD 2 2
T-OAD 2 2

Can this be done? Thanks again for your time! I really appreciate it!

 

[SantaMufasa]

So, Lysen,

Given the sample code I provided earlier, despite its not being precisely what you wanted, how would you extrapolate the solution to fit what you need? You assert a solution to the best of your knowledge and ability, referring, as needed to my "EXECUTE IMMEDIATE" example, then if you run into any trouble, we can do a sanity check with you and help you tweak code as needed. ¿Está bien?

Mufasa
(aka Dave of Sandy, Utah, USA @ 08:40 (20Feb04) UTC (aka "GMT" and "Zulu&quot, 01:40 (20Feb04) Mountain Time)

 

[Dagon]

Try the following. Suppose you have data:

SQL> select * from oas_toad;

CMPCODE COLUMN1 COLUMN2
---------- ---------- ----------
OAD 9 25

SQL> select * from oas_yada;

CMPCODE COLUMN1 COLUMN2 COLUMN3
---------- ---------- ---------- ---------
OAD 15 XXXXX 20-FEB-04
OAD 36 XXXXX 20-FEB-04

Run:

set serveroutput on size 90000
declare
cursor c_tabs is select table_name from sys.dba_tab_columns
where column_name = 'CMPCODE';
cursor c_cols (p_tab_name in varchar2) is select column_name from sys.dba_tab_columns
where table_name = p_tab_name;
INSERT_STMT VARCHAR(2000);
begin
for rec_tab in c_tabs loop
insert_stmt := 'insert into '||rec_tab.table_name||'(';
for rec_col in c_cols(rec_tab.table_name) loop
if c_cols%rowcount > 1 then
insert_stmt := insert_stmt||',';
end if;
insert_stmt := insert_stmt ||rec_col.column_name;
end loop;
insert_stmt := insert_stmt||')';
insert_stmt := insert_stmt||' select ';
for rec_col in c_cols(rec_tab.table_name) loop
if c_cols%rowcount > 1 then
insert_stmt := insert_stmt||',';
end if;
if rec_col.column_name = 'CMPCODE' then
insert_stmt := insert_stmt ||'''T_''||'||rec_col.column_name;
else
insert_stmt := insert_stmt ||rec_col.column_name;
end if;
end loop;
insert_stmt := insert_stmt||' from '||rec_tab.table_name;
execute immediate insert_stmt;
end loop;
end;

gives:

SQL> select * from oas_yada;

CMPCODE COLUMN1 COLUMN2 COLUMN3
---------- ---------- ---------- ---------
OAD 15 XXXXX 20-FEB-04
OAD 36 XXXXX 20-FEB-04
T_OAD 15 XXXXX 20-FEB-04
T_OAD 36 XXXXX 20-FEB-04

SQL> select * from oas_toad;

CMPCODE COLUMN1 COLUMN2
---------- ---------- ----------
OAD 9 25
T_OAD 9 25

 

[Lysen]

Thanks Dagon,

But before I run this script I want to review all the queries it's going to do, now I'm trying to get the variable which contains the 'insert into.... ' to spool to a file...

but all that is spooled is: 'pl/sql procedure completed succesfully'

I've tried to use this (after looking through some forums)
spool c:\query_review.txt

your query here

exec dbms_output.put_line(insert_stmt);
end;

but that doens't work either. And I've no idea how to get this to work...

 

[Dagon]

Did you do the set serveroutput on size 90000 ? You will not see anything unless you do that.

 

[Lysen]

Yes I did...

I logged on to SQL*plus.
Then I type '@c:\kopie.sql' where I put your code, which I altered a bit...

the code in kopie.sql is:

SPOOL c:\kopie2.txt
set serveroutput on size 90000
declare
cursor c_tabs is select table_name from sys.user_tab_columns
where column_name = 'CMPCODE';
cursor c_cols (p_tab_name in varchar2) is select column_name from sys.user_tab_columns
where table_name = p_tab_name;
INSERT_STMT VARCHAR2(2000);
begin
for rec_tab in c_tabs loop
insert_stmt := 'insert into '||rec_tab.table_name||'(';
for rec_col in c_cols(rec_tab.table_name) loop
if c_cols%rowcount > 1 then
insert_stmt := insert_stmt||',';
end if;
insert_stmt := insert_stmt ||rec_col.column_name;
end loop;
insert_stmt := insert_stmt||')';
insert_stmt := insert_stmt||' select ';
for rec_col in c_cols(rec_tab.table_name) loop
if c_cols%rowcount > 1 then
insert_stmt := insert_stmt||',';
end if;
if rec_col.column_name = 'CMPCODE' then
insert_stmt := insert_stmt ||'''T-''||'||rec_col.column_name;
else
insert_stmt := insert_stmt ||rec_col.column_name;
end if;
end loop;
insert_stmt := insert_stmt||' from '||rec_tab.table_name;

exec dbms_output.put_line(insert_stmt);
end loop;
end;
/

SPOOL OFF

should the set serveroutput line be before the spool command or something?

 

[sem]

Why do you think it should spool anything else? It just executes dynamic code immediately, thus the only output you may get - either error if any or the message you see once your feedback is on.

Regards, Dima

http://seminihin.narod.ru

 

[Lysen]

I don't know! I don't want it to execute immediately, I want to review the generated code before it executes anything!

 

[sem]

I can't imagine it may be executed successfully, because of wrong syntax: exec keyword can not be used within pl/sql block, because it's an sql*plus command.

Regards, Dima

http://seminihin.narod.ru

 

[Lysen]

wow, you're really helping me out here!!!!

I know it's wrong, I want to know how to get it to work...

It's something i was trying (found it in a forum), and I tried it without the exec command, but none of these show the generated sql...

 

[Dagon]

All you need is:

...
insert_stmt := insert_stmt||' from '||rec_tab.table_name;

dbms_output.put_line(insert_stmt);
...

As sem says, "exec" doesn't exist as a PL/SQL command.

 

[Lysen]

it doesn't work...

after starting sql*plus i enter:
@c:\kopie.sql

which contains this:

SPOOL c:\kopie2.txt
set serveroutput on size 90000
declare
cursor c_tabs is select table_name from sys.user_tab_columns
where column_name = 'CMPCODE';
cursor c_cols (p_tab_name in varchar2) is select column_name from sys.user_tab_columns
where table_name = p_tab_name;
INSERT_STMT VARCHAR2(2000);
begin
for rec_tab in c_tabs loop
insert_stmt := 'insert into '||rec_tab.table_name||'(';
for rec_col in c_cols(rec_tab.table_name) loop
if c_cols%rowcount > 1 then
insert_stmt := insert_stmt||',';
end if;
insert_stmt := insert_stmt ||rec_col.column_name;
end loop;
insert_stmt := insert_stmt||')';
insert_stmt := insert_stmt||' select ';
for rec_col in c_cols(rec_tab.table_name) loop
if c_cols%rowcount > 1 then
insert_stmt := insert_stmt||',';
end if;
if rec_col.column_name = 'CMPCODE' then
insert_stmt := insert_stmt ||'''T-''||'||rec_col.column_name;
else
insert_stmt := insert_stmt ||rec_col.column_name;
end if;
end loop;
insert_stmt := insert_stmt||' from '||rec_tab.table_name;
dbms_output.put_line(insert_stmt);
end loop;
end;
/

SPOOL OFF


this is what's spooled to kopie2.txt

declare
*
FOUT in regel 1:
.ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 84
ORA-06512: at "SYS.DBMS_OUTPUT", line 58
ORA-06512: at line 29


Invoer is afgekapt na 9 tekens.

??? ???

 

[Dagon]

Unfortunately, dbms_output is limited to 255 bytes for a single output statement and it isn't difficult to exceed this when you're building up a long insert statement.

The only option you have is to try to split the string into smaller pieces or to insert into into a table. I usually create a debugging table with a text field of length 2000 and then insert the statement into there.

 

[carp]

Or use utl_file to dump the script out to a file. Then you can review it, modify it if necessary, and then run it.

Elbert, CO
0824 MST