1

My goal here is to export all databases with their data into a .sql file using UTL-FILE which I can run in the future with the help of a plsql script.

So I selected metadata for each tables and saved it to a .sql file but I am having trouble on exporting data as INSERT INTO <table_name> VALUES (...);

This is what I came with so far. A cursor for each table:

FOR v_i IN (SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE='TABLE') LOOP

With that cursor I can create a varchar which I can put in .sql file:

INSERT INTO v_i.OBJECT_NAME VALUES( <values> );

My biggest problem is selecting for each row of table the data in a format that would replace values in my INSERT statement.

Here is where I blocked.How can I do it ?

user2907139
  • 97
  • 1
  • 9
  • Is there a a reason you want to write this yourself, rather than using a tool like SQL Developer's export functionality? If you really want to do this yourself you can look at the data dictionary to get the data types, or I suppose you could use `dbms_sql`; and format the output appropriately. – Alex Poole Apr 27 '16 at 17:38
  • it seemed that this would be a better choice for me. plus I couldn't figure out export tool and went on this path – user2907139 Apr 27 '16 at 17:42
  • do you mean something like `DBMS_SQL.PARSE(v_i, 'INSERT INTO table VALUES(:x)',DBMS_SQL.NATIVE);` – user2907139 Apr 27 '16 at 17:45
  • No, I mean using the query to get all the existing data from the table. If you get that as a ref cursor you can do [something like this](http://stackoverflow.com/a/11233929/266304) though that isn't a great example; I can't immediately find anything that uses the column data types. Querying `user_tab_columns` might be simpler. Again not a good example but [this](http://stackoverflow.com/a/28581465/266304) shows the general idea. – Alex Poole Apr 27 '16 at 17:54
  • I'd really suggest you look at existing tools again though; the SQL Developer data export wizard is pretty straightforward, from the Tools->Database Export menu option. [This might be useful too](http://www.thatjeffsmith.com/archive/2012/05/formatting-query-results-to-csv-in-oracle-sql-developer/). – Alex Poole Apr 27 '16 at 18:00
  • ty for your answers. I'll see what I can manage to do. – user2907139 Apr 27 '16 at 18:06

0 Answers0