CODEWITHSUNDEEP
linuxoracle-databaseplsqlsqlplusksh
Franco
Franco

Reputation: 23

How to detect a DDL statement in a script and exit

When running a script via SQLPLUS from within a KSH against an Oracle 11.2.0.4 or above database on Linux, is it possible to detect a DDL statement, exit and rollback all previous non DDL statements? So it would be something like this but enhanced to stop on the first DDL.

    WHENEVER SQLERROR EXIT ROLLBACK
    @script.sql

Is there some way to set exception handling in PLSQL to immediately exit before the first DDL statement can be executed?

Upvotes: 0

Views: 1094

Answers (1)

wolφi
wolφi

Reputation: 8361

I cannot think of an easy way to do this in SQL*Plus, and this is probably the biggest reason why we always have two accounts at work: One schema user who owns the tables and does all the DDL, and on application user, who has full read and write privs on the tables. The script is run by the application user and cannot execute any DDL at all.

If you insists, though, you could create a DDL trigger 

CREATE OR REPLACE TRIGGER my_ddl_watchdog 
  BEFORE DDL ON SCHEMA
DECLARE
  l_program v$session.program%type;
BEGIN
  SELECT program
    INTO l_program
    FROM v$session 
   WHERE audsid = sys_context('userenv', 'sessionid'); 
  IF lower(l_program) LIKE 'sqlplus%' THEN
    RAISE_APPLICATION_ERROR(-20000, 'sqlplus may not do DDL');
  END IF;
END my_ddl_watchdog; 
/

which fires as soon as an application called sqlplus tries to do DDL:

sqlplus user/pwf@localhost:1521/orcl

WHENEVER SQLERROR EXIT ROLLBACK;       

INSERT INTO bla VALUES (1);
1 row created.

DROP TABLE bla;
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: sqlplus may not do DDL
Disconnected from Oracle Database...

Upvotes: 5

Related Questions