Dump Oracle table(s) data to INSERT statements

Question

I have a requirement right now where my client's business people have populated a website with a bunch of data. They want the site to go live to production with the UAT data so that on launch day the site is not barren.

Now, the webservers and data centers are managed by a certain Big Blue friend of ours and they refuse to give me a user account on the UAT Database server, not even with access restricted only to the tables my app owns. That situation can be left to another discussion.

So, originally I was simply going to connected up to UAT using SQL Developer, and run it's nifty little INSERT statement export tool which will dump the data from a table into a series of INSERT statements. Since I can't have access to UAT, I can't do that.

Is there a method by which I can literally hand my blue friends some PL/SQL code which will dump all the table data from specified tables to INSERT statements? Preferably to a file (instead of the console)? This way they can take those INSERT statements and execute them against UAT.

Answer 1

have you considered exporting the data from your UAT db and then importing it to your local?

Answer 2

Let the Big Blue friend sort this out. If they don't give you access to the databases then they should populate the production database. Give them a list of tables an let them export them from UAT and import it into production. Export / import or datapump is the standard for these kind of operations, you should not be forced to invent your own because of their lack of cooperation.

Answer 3

I just answered a similar question yesterday. It may not be exactly what you want (and it is still incomplete), but it probably has the information to get you started to complete the scripts yourself. Check it out.