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).
