Oracle Data Migration Via SQL Script

Data changes are often promoted to production environments via scripts that are stored in a change control system.

If using a SQL script to apply data changes, these are useful to include:

-- The output file name. DO NOT include a path nor a drive letter.

spool outputFileName

prompt Include info here about defect tracking number, developer, etc.

-- On error rollback and exit. This is generally only needed for scripts that will be scheduled. For an ad-hoc migration, this is not needed.
whenever sqlerror exit failure rollback
whenever oserror exit failure rollback

-- Set the date format to avoid relying on defaults that are platform-specific.
alter session set nls_date_format='MM/DD/YYYY';

-- set echo on will print the SQL to the screen. This is optional and may not be useful if there is too much output.
set echo on

-- set define off will disable interpreting of the ampersand character.
set define off

-- Select the current time to show when script was implemented.
select systimestamp from dual;

-- set timing on will print elapsed time of each command.
set timing on

-- place script contents here.
UPDATE tablename set columna=value1;

-- spool off will stop logging to the output file.
spool off

For change control purposes, the migrations to production may run via sqlplus. Be aware that sqlplus has a limit to the number of lines per SQL, while tools such as Benthic appear to not have a reasonable limit to the number of lines per SQL. What may be encountered is a developer testing a script via Benthic with 5000 or more lines per statement (typically a large IN list) that will not run via sqlplus. The error message will be:

ERROR at line 565:
ORA-00933: SQL command not properly ended