Re: How to stop script

From: Pete Finnigan <oracle_list_at_peterfinnigan.demon.co.uk>
Date: Tue, 17 Jun 2003 08:14:39 -0700
Message-ID: <F001.005B2F4A.20030617075938@fatcity.com>

Hi

You can stop the script by using the line

whenever sqlerror exit rollback

and then generating an error by using an exception in a PL/SQL block as follows:

oracle:jupiter> cat imp.sql
whenever sqlerror exit rollback
declare

        cursor c_db is
        select name from v$database;
        lv_db c_db%rowtype;
        no_database exception;
        wrong_database exception;
        pragma exception_init(no_database,-5101);
        pragma exception_init(wrong_database,-5102);
begin
        open c_db;
        fetch c_db into lv_db;
        if c_db%notfound then
                close c_db;
                raise no_database;
        else
                if lv_db.name<>'TEST' then
                        close c_db;
                        raise wrong_database;
                end if;
        end if;
        close c_db;
        dbms_output.put_line('Running on database : '||lv_db.name);
exception
        when no_database then
                dbms_output.put_line('ERROR: could not find database');
                raise_application_error(-20100,'ERROR - no database');
        when wrong_database then
                dbms_output.put_line('ERROR: wrong database');
                raise_application_error(-20101,'ERROR - wrong
database');
        when others then
                dbms_output.put_line('ERROR: unknown error');
                raise_application_error(-20101,'ERROR - unknown error');

end;
/

select user from sys.dual;

• end of script imp.sql

then run it as follows

SQL> @imp
declare
*
ERROR at line 1:
ORA-20101: ERROR - wrong database
ORA-06512: at line 29

Disconnected from Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
With the Partitioning option
JServer Release 9.0.1.0.0 - Production
oracle:jupiter>

i added the select user from sys.dual to show the script stops processing.

hope this helps

kind regards

Pete

>
>However, I want to check database name at the beginning of import.sql. I knew
>"select name from v$database" can obtain database name. But how can I stop the
>script if I found it is not TEST database? In import.sql, it is like:
>
>drop user A cascade;
>create user A ....;
>host imp A/[EMAIL PROTECTED] file=a.dmp full=yes;
>// some PL/SQL ...
>

-- 
Pete Finnigan
email:[EMAIL PROTECTED]
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Pete Finnigan
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Jun 17 2003 - 10:14:39 CDT