RE: How to stop script
Hi Chen, My solution will be about the same with previous DBA guru I assume u r using Window (if is unix you just need to change a little bit here and there) I write this script for you. +save as imp.sql++ SET HEAD OFF SET FEEBACK OFF SET PAUSE OFF SET VERIFY OFF SET LINESIZE 250 SPOOL 'C:\temp\~dynamic_imp.bat' SELECT 'imp username/password@' || name || ' full=y file=bla bla other parameter=bla bla bla log=mypath\log' from v$database where name = 'TEST'; SPOOL OFF -- host chmod u+x C:\temp\~dynamic_imp.bat (if you using unix this will prevent you from bug cause by your umask) -- host C:\temp\~dynamic_imp.bat-- uncomment this if you ready to do the import -- host del C:\temp\~dynamic_imp.bat-- uncomment this if your C:\temp\~dynamic_imp.bat is look ok ++= +save as imp.bat*** sqlplus -s someone_can_do_select_v$database/[EMAIL PROTECTED] +++ You can add alot of fancy stuffs but the concept is about like that Sinardy -Original Message- Sent: 17 June 2003 12:39 To: Multiple recipients of list ORACLE-L I have a script and can be run on commandline like: sqlplus usrname/password@TEST @import.sql 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 ... Thanks! -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sinardy Xing 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).
Re: How to stop script
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).
Re: How to stop script
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).
Re: How to stop script
You could use a little indirect approach in this case. The file import.sql will look like this set feed off set lines 120 set trimpsool off set pages 0 spool imp.sh select 'imp un/[EMAIL PROTECTED] file=a.dmp full=yes' from v$database where name = 'TEST' / spoo off exit You have another script called call_import.sh with the follwoing lines sqlplus -s us/[EMAIL PROTECTED] @import.sql sh imp.sh If the database is not test, then the imp.sh will be empty and the import will not go through. Otherwise it will have the entire import line. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, June 17, 2003 12:39 AM I have a script and can be run on commandline like: sqlplus usrname/password@TEST @import.sql 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 ... Thanks! -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda 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).
RE: How to stop script
Ave ! Here is one possible solution. Create two tiny scripts. First script let say ex.sql which includes only word exit and linefeed and second one cont.sql which has only one space character, nothing else. Then in Your sql script: -- column NAME noprint new_value SCRIPT_NAME select decode(name,'TEST','cont.sql','ex.sql') NAME from v$database; @SCRIPT_NAME -- So if Your database is TEST, then You run cont.sql (nothing happens), but if it's something else, You run ex.sql which stops Your sqlplus session. HTH Jorma -Original Message- Sent: 17 June, 2003 07:39 To: Multiple recipients of list ORACLE-L I have a script and can be run on commandline like: sqlplus usrname/password@TEST @import.sql 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 ... Thanks! -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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).
