CODEWITHSUNDEEP
oracle-databaseinsertpackagedatabase
resilva87
resilva87

Reputation: 3465

Oracle DBMS package command to export table content as INSERT statement

Is there any subprogram similar to DBMS_METADATA.GET_DDL that can actually export the table data as INSERT statements?

For example, using DBMS_METADATA.GET_DDL('TABLE', 'MYTABLE', 'MYOWNER') will export the CREATE TABLE script for MYOWNER.MYTABLE. Any such things to generate all data from MYOWNER.MYTABLE as INSERT statements?

I know that for instance TOAD Oracle or SQL Developer can export as INSERT statements pretty fast but I need a more programmatically way for doing it. Also I cannot create any procedures or functions in the database I'm working.

Thanks.

Upvotes: 3

Views: 6361

Answers (3)

HSI
HSI

Reputation: 3

Oracle SQL Developer does that with it's Export feature. DDL as well as data itself. Can be a bit unconvenient for huge tables and likely to cause issues with cases mentioned above, but works well 99% of the time.

Upvotes: 0

Jon Heller
Jon Heller

Reputation: 36922

As far as I know, there is no Oracle supplied package to do this. And I would be skeptical of any 3rd party tool that claims to accomplish this goal, because it's basically impossible.

I once wrote a package like this, and quickly regretted it. It's easy to get something that works 99% of the time, but that last 1% will kill you.

If you really need something like this, and need it to be very accurate, you must tightly control what data is allowed and what tools can be used to run the script. Below is a small fraction of the issues you will face:

  • Escaping
  • Single inserts are very slow (especially if it goes over a network)
  • Combining inserts is faster, but can run into some nasty parsing bugs when you start inserting hundreds of rows
  • There are many potential data types, including custom ones. You may only have NUMBER, VARCHAR2, and DATE now, but what happens if someone adds RAW, BLOB, BFILE, nested tables, etc.?
  • Storing LOBs requires breaking the data into chunks because of VARCHAR2 size limitations (4000 or 32767, depending on how you do it).
  • Character set issues - This will drive you ¿¿¿¿¿¿¿ insane.
  • Enviroment limitations - For example, SQL*Plus does not allow more than 2500 characters per line, and will drop whitespace at the end of your line.
  • Referential Integrity - You'll need to disable these constraints or insert data in the right order.
  • "Fake" columns - virtual columns, XML lobs, etc. - don't import these.
  • Missing partitions - If you're not using INTERVAL partitioning you may need to manually create them.
  • Novlidated data - Just about any constraint can be violated, so you may need to disable everything.

If you want your data to be accurate you just have to use the Oracle utilities, like data pump and export.

Upvotes: 5

Daniel Haviv
Daniel Haviv

Reputation: 1036

Why don't you use regular export ?
If you must you can generate the export script:

Let's assume a Table myTable(Name VARCHAR(30), AGE Number, Address VARCHAR(60)).

select 'INSERT INTO myTable values(''' || Name || ','|| AGE ||',''' || Address ||''');' from myTable

Upvotes: 0

Related Questions