2

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;
0

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.

0

I would not bother implementing it, because others already did it and spent a lot of time with it. SQL Developer can do this for you.

Example: Exporting Metadata and Data for a Table

In SQL Developer, click Tools, then Database Export.

Set the required options:

enter image description here

Select object types, after that, select the tables you want to be exported:

enter image description here

Result will be something like this when choosing to export data only:

--------------------------------------------------------
--  File created - Wednesday-May-10-2017   
--------------------------------------------------------
REM INSERTING into BP.T1
SET DEFINE OFF;
Insert into BP.T1 (C1) values (1);
REM INSERTING into BP.T2
SET DEFINE OFF;
Insert into BP.T2 (C1) values (2);
  • I know that it's already implemented, but i want to do this without using it. – bogdan May 10 '17 at 6:54

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.