aborting a SQL script based upon DB name

  • From: Greg Norris <spikey.mcmarbles@xxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 19 May 2005 08:49:33 -0500

I was recently asked how to setup a SQL script, to ensure that it
aborts if accidentally run on the wrong database.  The scriptlet below
is what I came up with... it seems to work just fine, but got me
curious about alternate approaches.  Anyone care to share a
substitute, or comment on the futility of life in general? ;-)

----- <snip> -----
-- ensure that the following block aborts the script on error
whenever sqlerror exit 100

   dbname     global_name.global_name%TYPE;
   select global_name into dbname
      from global_name;

   if dbname !=3D 'LVLSDP' then
      raise_application_error(-20001,'I pity ''da fool who connects to
''da wrong database!!!');
   end if;

-- back to the default behaviour
whenever sqlerror continue
----- <snip> -----

"I'm too sexy for my code." - Awk Sed Fred.

Other related posts: