SQL*Plus Template Script

In order to run dml or ddl statements into the database, I pack them into a SQL*Plus template script that keeps track of its execution and let everything very well documented.

SET Echo Off
CONNECT / AS SysDBA
COLUMN "NAME" NEW_VALUE DataBase
COLUMN "Now" NEW_VALUE Now
SELECT NAME FROM V$DataBase;
SELECT To_Char(SysDate,'YYYY-MM-DD_HH24-MI-SS')
AS "Now" FROM Dual;
ALTER SESSION SET
Current_Schema = ' put the schema name here'
NLS_Date_Format = 'YYYY-Mon-DD HH24:MI:SS';
SET Define On
SPOOL " put the script name here ".1.&DataBase..&Now..log
PROMPT EXIT
SET LineSize 254 TrimSpool On
ALTER SYSTEM CheckPoint LOCAL;
COLUMN CheckPoint_Change#-
FORMAT 999999999999999999
SELECT
Name,
To_Char(SysDate,'YYYY-MM-DD_HH24-MI-SS'),
CheckPoint_Change#
FROM V$DataBase;
WHENEVER SQLerror EXIT FAILURE ROLLBACK
SET Echo On ServerOutput On Format Wrapped

/*******************************************/

--- put the statement(s) here

/*******************************************/

Commit WORK;
DisCONNECT
SPOOL OFF
EXIT