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
