We need to move multiple tables using Load from cursor. But the number columns in the source and destination tables are not the same.

Also the tables structures can change so we need the same to be dynamic.

the statement would be something like below.

 declare cur1 cursor for select col1,col2, col3  from test1;
 load from cur1 of cursor insert into test2 (col1,col2, col3);

I need to call this from a procedure using execute immediate so that we can append the columns in the query. The list of columns is in a variable V_COL_LIST so the query i need to execute for procedure is like below.

 execute  immediate ('declare cur1 cursor for select '||V_COL_LIST ||'  from test1');
 execute  immediate ('load from cur1 of cursor insert into test2 ('||V_COL_LIST ||')');

But Execute Immediate does not work for the above queries. Is there any work around to this ?


Figured it out used ADMIN_CMD

set v_sql ='LOAD FROM (SELECT '|| v_colquery || ' FROM ' || srctablename  || ') OF CURSOR INSERT INTO ' || destablename || '_LOG  ( ' || v_colquery || ')';
call admin_cmd(v_sql);   
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.