0 Replies - 4807 Views - Last Post: 13 March 2012 - 02:07 PM

#1 DpinkyandDbrain  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 09-March 10

PL/SQL execute immediate problem

Posted 13 March 2012 - 02:07 PM

Hello all who read/respond,

I am having a ORACLE PL/SQL issue where I want to dynamically build an insert statement. I am obviously using execute immediate, but this procedure needs to be called many many times, so caching is a major issue. I have tried using a piping function to make a dynamic table to:

 
insert 
  into table_x 
select *
  from table(piping_function(CURSOR(SELECT * FROM source_table)))
  


This would be proceed by an object being made of the column names and a table made of that object type. After that I found that using bind variables would allow me to get around the cashing issue. But, the draw back is I can't dynamically choose the number of columns I want to insert into like:

insert
  into table_x(1 - 100 columns)
values (1 - 100 bind variables corresponding to the number of columns)
 using 1 - 100 variables corresponding to the number of columns & bind variables
 


If anyone could help me find a way to get around the issue of having to hard code every variable passed into the using clause that would be very helpful. As I could not find out myself.

Is This A Good Question/Topic? 0
  • +

Page 1 of 1