An administrative script needs a reliable failure path. SQL*Plus normally continues after an SQL error. A later successful exit can therefore leave the calling shell with status zero even though an earlier statement failed. Install error handling before the work, verify the connected context, and inspect the expected result as well as the process status.
Oracle DBA Lesson 93 Part 1 — Make an administrative script fail safely
What the two error rules do
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
WHENEVER OSERROR EXIT FAILURE ROLLBACKSQLERROR handles errors in SQL statements and unhandled PL/SQL errors. OSERROR handles operating-system errors recognized by SQL*Plus, such as a failure writing a file. Both rules end SQL*Plus with a failure status and roll back pending transaction work. Put them before the statements they must protect.
FAILURE supplies the platform's failure return code; test for zero versus nonzero rather than assuming one exact number. Retain the error text in the log for diagnosis. On Unix, process statuses are limited to 0–255, so returning a full Oracle SQLCODE does not reliably preserve its original number.
These rules have limits. SQL*Plus client-command errors are outside WHENEVER SQLERROR. For example, COLUMN table_name HEADIING Name has a misspelled formatting option. Its client error can be followed by a successful exit. Validate client commands and review the log; an exit code alone cannot establish that every command and result was correct.
See WHENEVER SQLERROR and WHENEVER OSERROR.
Check the intended context
Save the following as check_target.sql. The error rules come first, then a guard on the connected context, then the inspection queries.
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
WHENEVER OSERROR EXIT FAILURE ROLLBACK
SET ECHO ON
SET FEEDBACK ON
SET PAGESIZE 100
SET LINESIZE 200
SPOOL check_target.log REPLACE
BEGIN
IF NVL(SYS_CONTEXT('USERENV','DB_UNIQUE_NAME'),'?')
<> 'APPDB'
OR NVL(SYS_CONTEXT('USERENV','CON_NAME'),'?')
<> 'APP_PDB'
OR NVL(SYS_CONTEXT('USERENV','SESSION_USER'),'?')
<> 'APP_USER' THEN
RAISE_APPLICATION_ERROR(-20001,
'Unexpected database context');
END IF;
END;
/
SELECT SYSTIMESTAMP AS checked_at,
SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') AS db_name,
SYS_CONTEXT('USERENV','CON_NAME') AS container_name,
SYS_CONTEXT('USERENV','SESSION_USER') AS login_user
FROM dual;
SELECT table_name FROM user_tables
WHERE table_name IN ('DEPARTMENTS','EMPLOYEES',
'ORDERS','RECOVERY_MARKER')
ORDER BY table_name;
SPOOL OFF
EXIT SUCCESS ROLLBACKThe guard compares the database's unique name, connected container, and session login against the intended target. In this script those values are APPDB, APP_PDB, and APP_USER. Use independently verified target details, and do not copy them from whichever session happens to be connected. NVL substitutes ? for a missing value, so a missing context fails the comparison. A mismatch raises application error -20001; the SQL error rule exits, and the queries below it are skipped. A match permits the inspection queries to run. A session identity check does not grant privileges or establish that a later administrative change is appropriate.
See SYS_CONTEXT, NVL, and raising exceptions explicitly.
SYSTIMESTAMP gives the database system timestamp, including its time zone. USER_TABLES describes relational tables owned by the current user, so this query needs no DBA catalog privilege. The result must contain each expected name: DEPARTMENTS, EMPLOYEES, ORDERS, and RECOVERY_MARKER. A missing table produces fewer rows, which is still a successful SQL query. This script does not implement an automatic assertion that all four rows exist. Add an explicit result assertion before using such a query as an unattended acceptance gate.
See USER_TABLES.
SPOOL ... REPLACE replaces the log in the current directory. Use a different directory or filename to preserve an existing log. Confirm that the log was created and contains the identity and complete result. Spool and other client failures also require log/file checks; do not assume every SQL*Plus SP2- error triggers one of these rules.
See SPOOL.
Capture the caller's result immediately
Call SQL*Plus and save the status before the next command runs:
sqlplus -L app_user@DB_SERVICE @check_target.sql
rc=$?
printf 'SQL*Plus exit status: %s\n' "$rc"
if [ "$rc" -ne 0 ]; then
printf '%s\n' 'Inspection failed; review the terminal and log.'
else
printf '%s\n' 'Process succeeded; verify identity and expected rows in the log.'
fi-L avoids repeated login attempts after an initial login failure. This form prompts for the password. It is interactive, and it is not a complete unattended authentication design. Do not put credentials in script files, command arguments, or logs. Unattended use requires a separately configured authentication mechanism.
Bash $? contains the most recent foreground command's exit status. rc=$? saves the SQL*Plus result before another command replaces it. Branch on the saved value. The log explains the cause and supports result verification. Keep failed-run terminal output too: a login or early client failure can occur before spooling starts.
See Starting SQL*Plus and Bash special parameters.
Transaction boundaries
The required quick check is: Does WHENEVER SQLERROR ... ROLLBACK make Oracle DDL transactional? No.
Oracle commits before a syntactically valid DDL statement, even if that statement later fails, and commits again after DDL completes successfully. An error exit's rollback affects only changes still pending in the current transaction. It cannot reverse previously committed work. Put target checks before any work, keep transaction boundaries explicit, and prepare a suitable recovery or reversal plan before extending the script with changes. The inspection script contains no DDL or data changes. Its explicit EXIT SUCCESS ROLLBACK ends pending transaction work without an implicit commit; plain EXIT defaults to commit and success.
No comments:
Post a Comment