Recently, I had to move some data from an upper environment into a lower environment for unit testing purposes. Of course, one can always use datapump or other proven approaches. In my case, I only have access via Oracle SQL Developer (not even SQL*Plus or sqlcl). The option to use datapump would require tickets raised to infrastructure Team and days of waiting. And I need the data now!
And that’s where Oracle SQL Developer comes into play with its ‘special’ query hints. Specifically I have used the INSERT hint:
select /*insert*/ * from ...to quickly create an INSERT script locally that I can run agaisnt the target database.
So here is the simple procedure used:
- Run the SELECT statement identifying the rows that should be moved and check the result is in line with the expectation. Use the Run Statement green triangle button to achieve this:
- Run the same statement ‘as script’ by using the Run Script second button with the relevant SELECT statement selected (with other words, the ‘hint’ functions only with ‘Run as Script’ and not when ‘Run as statement’). This will generate the INSERT script in the Script Output lower half of the workspace.
Note: depending on the amount of the data to be returned the Script Output might need to get adjusted but this will be the subject of a different post. - Next, the INSERT‘s script can be saved locally by using the lovely floppy disk icon.
- The saved script can be used next to be executed in tha target database followed by a COMMIT as needed. Note: the insert statements are created with the original schema_name.table_name, if the target database have different schema and / or table names a global search and replace might be needed in your favorite text editor.
Nice and simple!
Similar procedure can be used to create test data in a lower environment. In this case more sophisticated text processing tools like sed, awk or perl can de used to search and replace confidential data with masked strings in the INSERT script.
Well done Oracle SQL Developer Team!