Build Update Statements with Oracle SQL Developer and Python

Posted by in Software, Tutorial

By default, SQL Developer can only export INSERT statements. This is quite unfortunate when one needs to build UPDATE statements because their syntax is different. I’ve tried to use regexp to convert the statements but I didn’t find it not straight forward at all. So, I cam up with the following strategy:

  1. Export the data as JSON
  2. Use a python script (below) to write the statements

The python script

We first need to specify things like the DB Schema, table and the primary key:

Subsequently, I’ve defined a wrapper function to be able to wrap quotes around string values:

Once this preparation is done, I load the JSON data:

One thing I have noticed about the JSON exported from SQL Developer is that null fields are ignored. Therefore, we first need to build the fill list of fields by scanning the JSON entries:

Now we can build the list of UPDATE statements:

This is not rocket science by any means, but it saved me a couple of times already when I needed to populate a PROD DB with values tested in acceptance.

##The script

The full script is:

A little experiment: If you find this post and ad below useful, please check the ad out :-)