Interesting Issue

Hello,

I am trying to write a generic procedure for moving data from one table to another, that does COMMIT's every X number of rows and does some intelligent error processing.

I would like to pass the table names to the stored procedure, e.g. source_name and destination_name. Since in Oracle I cannot do something like:
 FOR rec IN (SELECT * from source) LOOP

    INSERT INTO dest VALUES ( rec.XXX, rec.YYY ) ...

my resort is to use DBMS_SQL package, where I can say something like:  c := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE( c, 'SELECT * FROM ' || source, DBMS_SQL.V7 );

Is there a way to have dynamic binding, where I can declare a variable of %ROWTYPE of the source and destination tables and map the columns? Or, even better, is there a simpler way to accomplish what I am trying to do?

The key requirement is to be able to commit INSERTs into destination after a specified # of records.
Any help / ideas will be greatly appreciated.

Sincerely,

Alex Vilner Received on Mon Jun 15 1998 - 15:43:32 CDT