Thanks! That's exactly what I need!
Jin
Quoting Pete Finnigan <[EMAIL PROTECTED]>:
> 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).
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
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).
