Home » SQL & PL/SQL » SQL & PL/SQL » forming insert value dynamically based on columns in destination (10g)
forming insert value dynamically based on columns in destination [message #429814] Thu, 05 November 2009 20:05 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi ,

Im trying to form an insert statmeent dynamically together with cursor integration.

I have a table in instance B, im trying to insert the values of this table to instance A, but the column name will follow the instance B.

Below is my test plan

CREATE TABLE T1
(
  COL1  NUMBER,
  COL2  NUMBER
);

------------
CREATE TABLE T2
(
  COL1  NUMBER,
  COL2  NUMBER
);
---------------

insert into t2 values (1,1); 

commit;

--------------


CREATE OR REPLACE procedure P1
is

v_column varchar2(32767);
v_column_cur varchar2(32767);


cursor cur_fact is
select * from T2@prd;
Type  TAB_OIC is table of cur_fact%rowtype index by binary_integer;
t_OIC TAB_OIC;


BEGIN


for x in ( select column_name
from user_tab_columns@prd
where table_name = 'T2'
order by column_id )
loop
v_column := x.column_name || ', ' || v_column;
v_column_cur := 't_oic(i).' || x.column_name || ', ' || 
end loop;
v_column := substr(v_column,1,length(v_column)-2);
v_column_cur := substr(v_column_cur,1,length(v_column_cur)-2);


open cur_fact;
loop
t_oic.DELETE(1,t_oic.COUNT);
FETCH cur_fact bulk collect into t_oic LIMIT 2000;
EXIT WHEN t_oic.COUNT = 0;
for i in 1..t_oic.count loop

v_column := 'insert  into T1 (' || v_column || ')' || ' values (' || v_column_cur || ');';
dbms_output.put_line(v_column);
execute immediate (v_column);

end loop;
commit;
end loop;
commit;
close cur_fact;


End;
/



The code above returns the following error


ORA-00911: invalid character
ORA-06512: at "TEST.P1", line 45
ORA-06512: at line 1


This is the execute immediate command line, when i try to print the v_column variable out, its giving the right syntax.


insert into T1 (COL2, COL1) values (t_oic(i).COL2, t_oic(i).COL1);

Not sure if im missinng something..

[Updated on: Thu, 05 November 2009 20:09]

Report message to a moderator

Re: forming insert value dynamically based on columns in destination [message #429815 is a reply to message #429814] Thu, 05 November 2009 20:26 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
>insert into T1 (COL2, COL1) values (t_oic(i).COL2, t_oic(i).COL1);

Not valid SQL syntax above

If you insist on (ab)using dynamic SQL,
then build statement which could be run in sqlplus.
Re: forming insert value dynamically based on columns in destination [message #429816 is a reply to message #429814] Thu, 05 November 2009 20:33 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Don't think you can reference PL/SQL collections and row-types from within Dynamic SQL.

Theoretically, I suspect you could do it with DBMS_SQL dynamic SQL by binding the collection variables individually at each iteration.

You would do better to execute:
INSERT INTO T1 SELECT * FROM T2@PRD;

or even
INSERT INTO T1 (col list...) SELECT col list... FROM T2@PRD;


Ross Leishman
Re: forming insert value dynamically based on columns in destination [message #430013 is a reply to message #429816] Fri, 06 November 2009 16:36 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi.

Earlier i tried with the sql you highlighted

INSERT INTO T1 SELECT * FROM T2@PRD;
or even
INSERT INTO T1 (col list...) SELECT col list... FROM T2@PRD;


Due to the amount of data and size of rollback segment, it failed.

I will take a look at the DBMS_SQL package
Re: forming insert value dynamically based on columns in destination [message #430014 is a reply to message #430013] Fri, 06 November 2009 17:17 Go to previous message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
>Due to the amount of data and size of rollback segment, it failed.

>I will take a look at the DBMS_SQL package
You'd be better off increasing the size of the ROLLBACK space.
Don't be surprised if you get ORA-01555 error when using DBMS_SQL & COMMIT inside LOOP.
Previous Topic: Guidance for better table design
Next Topic: updating values of a column
Goto Forum:
  


Current Time: Sun Sep 25 04:24:54 CDT 2016

Total time taken to generate the page: 0.09488 seconds