Oracle Nested Cursors – Solving Problems with Nested Cursors

dynamic-sqloracleoracle-11gplsql

I'm trying to restore all the inserts that I've done in my tables.
I have a cursor that loops through table names, a second one that loops through all the rows from that table and a third one that loops through all the columns.
The problem is that I can't declare a dynamic row because it gives an exception at runtime. Is there any solution to my problem? Or can you suggest another approach?

Here is the code:

declare
  cursor c_table is select table_name from user_tables;
  sql_query varchar2(100);
  rc sys_refcursor;
begin
  for v in c_table loop
    dbms_output.put_line('INSERT INTO ' || v.table_name ||' VALUES (');

      sql_query := 'select * from '||v.table_name;
      open rc for sql_query;    
      loop
        ---- here i want to fethc all the rows 
        declare
          sql_statement varchar2(200);
          v_column varchar2(50);
          re sys_refcursor;
        begin
          sql_statement := 'select column_name from user_tab_columns where table_name = '|| q'[']' || v.table_name || q'[']';
          open re for sql_statement;
          loop
            fetch rc into v_column;
            exit when rc%notfound;
          end loop;
          close re;
        end;
      end loop;
  end loop;
end;

Best Answer

Try taking things one level deeper than you have.

You're creating dynamic SQL to get the information you need; instead, create a script that you'll run to perform the actual change.

When building your script, you'll grab all the table and column name data you need, and construct the necessary SQL commands to pull the data out of the current database and write it to the new one. Yes, you'll have a separate piece of the script for each of the tables, but that's fine - you're not creating it through copy and paste, but through a process, so you know it should work. And, once it is working, you can generate a new script whenever you need, so there's no maintenance overhead.

Once your script is created (which should be fast), you can run it whenever you like. You can even carve out chunks for individual testing, if needed.

The simplest way to do this is to manually generate a script for one table. Then, look at the table- and column- specific data, and build SQL to create that script, populating table and column names as you are for your dynamic SQL. Once it's prepped for one table, it should work for all.

Related Question