Thanks for the question, Valery.

Asked: March 20, 2007 - 12:10 pm UTC

Last updated: March 29, 2016 - 5:19 am UTC

Version: 10.2

Viewed 10K+ times! This question is

You Asked

Can I export table rows as insert statements through DBMS_METADATA package?

CREATE OR REPLACE FUNCTION GET_DML( p_schema IN VARCHAR2, p_table IN VARCHAR2) RETURN CLOB AUTHID CURRENT_USER AS
  handle NUMBER;
  tr_handle NUMBER;
  ret CLOB;
BEGIN
  handle := DBMS_METADATA.OPEN('TABLE_DATA');
  DBMS_METADATA.SET_FILTER(handle, 'SCHEMA', p_schema);
  DBMS_METADATA.SET_FILTER(handle, 'NAME', p_table);
  tr_handle := dbms_metadata.ADD_TRANSFORM(handle, 'DDL');
  ret := dbms_metadata.FETCH_CLOB(handle);
  dbms_metadata.CLOSE(handle);
  RETURN ret;
END GET_DML;

and Tom said...

Thankfully - no, you cannot.

I say thankfully, because people would and then we'd be in a world of hurt as people trashed shared pools all over the place running unique insert statements.

http://asktom.oracle.com/tkyte/flat/index.html

you can use various techniques to unload data into a flat file for subsequent reloading.

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Valery Dubrava, March 20, 2007 - 2:10 pm UTC

Thank you for detail answer and link.

Link doesn't work

Adelaida, October 21, 2010 - 9:54 am UTC

I want to know how can I export table rows as insert staments sql. Table contains data, and i need to export these data as insert staments for executing those statements in other instance.
This useful for migrations.

Thank you.

October 25, 2010 - 3:24 pm UTC

Well, that link never did that - I wrote about how that would not be a good thing.

http://asktom.oracle.com/Misc/httpasktomoraclecomtkyteflat.html

is the link, but it unloads a flat file, not inserts.

datapump is useful in migrations.

expoting sql statements

SHAKIL AHMED, September 25, 2013 - 4:09 pm UTC

you can use a third party software called toad. There you are. log using schema, user name, password. select database, go to export, select tables, give file name and destination path and you are done

dead link

A reader, March 28, 2016 - 10:48 am UTC

/tkyte/flat/index.html -- 404

March 29, 2016 - 5:19 am UTC

Go to Resources => Presentations and scroll through that list.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here

Breadcrumb

Question and Answer