Home » SQL & PL/SQL » SQL & PL/SQL » how to stop a running script but not exit from sqlpus
how to stop a running script but not exit from sqlpus [message #203012] Mon, 13 November 2006 09:28 Go to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi everybody,
In a script that will run by a user ,i want hem to be a DBA ;if not then the script must stop and not continue to the rest of the code to let the user changing his connecting string to the database:
set serveroutput on size 20000
DECLARE
usrnm varchar2(50);
BEGIN
select username into usrnm from user_users ;
IF usrnm<>'SYS' or usrnm <>'SYSTEM' THEN
RAISE_APPLICATION_ERROR(-20110,'Error:You must run this script'||''' @4_CreateUser.sql'''||' as a DBA, like SYSTEM or SYS. ');
END IF;
END;
/
--So i do not want hem from here to continue with the rest of the code
accept USER_1 prompt -
'Enter name of database user : ';

accept USER_1_PASSWORD prompt -
'Enter password of database user : ' HIDE;

thanks in advance,
bahaa
Re: how to stop a running script but not exit from sqlpus [message #203026 is a reply to message #203012] Mon, 13 November 2006 10:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
split the code in several scripts.
create a temporary script by spooling
select '@dba_script.sql' from dual where user='SYS'
union
select '@retry_logon.sql' from dual where user != 'SYS'

If the user is not connected at all, well, too bad. Nothing is going to be selected, so maybe do a WHENEVER SQLERROR THEN EXIT first.

Note code is not tested.
Re: how to stop a running script but not exit from sqlpus [message #203046 is a reply to message #203026] Mon, 13 November 2006 12:41 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
more info...
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1934751139108
Re: how to stop a running script but not exit from sqlpus [message #203085 is a reply to message #203046] Mon, 13 November 2006 17:13 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Thanks for your answer,
but i stil have some difficulties with the solution of the link you suggest,i prefer to let you check with me the script i have to see how i can force the user to disconnect when he is not SYS or SYSTEM:

Drop Table SCRIPTLOG;

CREATE TABLE SCRIPTLOG
(
NAME VARCHAR2(50) NOT NULL ,
VERSION VARCHAR2(7) NOT NULL ,
RUNDATE DATE NOT NULL ,
USERNAME VARCHAR2(30) NOT NULL
);


define SCRIPT_NAME = 4_CreateUser
define SCRIPT_VERSION = 1.1
define DB_VERSION = 5.2

BEGIN
INSERT INTO SCRIPTLOG ( NAME, VERSION, rundate, username )
VALUES ( '&SCRIPT_NAME (&SCRIPT_VERSION)',
'&DB_VERSION',
(SELECT SYSDATE FROM USER_USERS WHERE username = sys_context ('userenv','current_schema')), (SELECT USER FROM USER_USERS WHERE username = sys_context ('userenv','current_schema')));
END;
/
commit;

--whenever sqlerror exit sql.sqlcode
set serveroutput on size 20000
DECLARE
usrnm varchar2(50);
BEGIN
SELECT DISTINCT username into usrnm FROM SCRIPTLOG WHERE NAME LIKE '4%' ;

IF (usrnm !='SYS' or usrnm !='SYSTEM') THEN

dbms_output.put_line('Error:You must run this script'||''' @4_CreateUser.sql'''||' as a DBA, like SYSTEM or SYS.');
dbms_output.put_line('press ctrl-c to disconnect from SQLPLUS ,and re-connect again as SYS or SYSTEM');

END IF;

END;
/

accept USER_1 prompt -
'Enter name of database user : ';

accept USER_1_PASSWORD prompt -
'Enter password of database user : ' HIDE;

accept USER_1_TS_DEFAULT prompt -
'Enter default tablespace name for the database user : ';

accept USER_1_TS_TEMP prompt -
'Enter temporary tablespace name of the database user : ';

accept OWNER1_ROLE prompt -
'Enter name of database owner (for the name of the role) : ';

create user &USER_1 identified by &USER_1_PASSWORD
default tablespace &USER_1_TS_DEFAULT temporary tablespace &USER_1_TS_TEMP;

prompt Grant connect, create session
grant connect, create session to &USER_1;

prompt Grant &OWNER_1._USER_ROLE
grant &OWNER_1._USER_ROLE to &USER_1;

i want hem to disconnect from sqlplus as soon as this error will appear on the screen 'Error:You must run this script'||''' @4_CreateUser.sql'''||' as a DBA, like SYSTEM or SYS.' by pressing on 'ctrl-c' and not to continue with the rest of the script.
Note:
----
#The suggestion of splitting the script is not acceptable because of other complications considring the total of the script files.


Thanks in Advance,
bahaa



Re: how to stop a running script but not exit from sqlpus [message #203087 is a reply to message #203085] Mon, 13 November 2006 18:20 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
The trick is quite simple - if a certain condition is met, run one script else run another. e.g. (from an 11yr old doc)

Q:     How to start a SQL*Plus script when at least one row is found
       from a table ? If no rows are found, then nothing should be
       started.

A:     Use a script like the following:
       spool tmp.sql
       Select decode(greatest(count(*),0),'0','start dummy.sql','start script.sql')
       from tablex where colx = &parameter;
       spool off
       @tmp.sql


Personnaly I just force connection to the right schema.
...
set termout off
col DB new_val DB
SELECT name DB from v$database;
set termout on
prompt connecting to SYSTEM - enter PASSWORD:
prompt ======================================
connect SYSTEM@&DB
...


you can easily combine both ideas so that you only prompt to connect if schema is wrong.
Re: how to stop a running script but not exit from sqlpus [message #203248 is a reply to message #203087] Tue, 14 November 2006 08:16 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
i found a solution for the problem :
set serveroutput on size 100000
set verify off
var abort NUMBER;

BEGIN
:abort := 0;
IF (USER != 'SYS') THEN
IF (USER != 'SYSTEM') THEN
:abort := :abort +1;
dbms_output.put_line('Error:You must run this script'||''' @4_CreateUser.sql'''||' as a DBA, like SYSTEM or SYS.');
dbms_output.put_line('Please connect as DBA and re-execute.');
dbms_output.put_line('SQLPLUS will be canceld after 15 sec.');

END IF;
END IF;

END;
/

WHENEVER SQLERROR EXIT
BEGIN
IF :abort > 0
THEN
dbms_lock.sleep(15);
raise_application_error(-20000, 'Script abort detected');
END IF;
END;
/
WHENEVER SQLERROR CONTINUE

but i need first to (grant execute on dbms_lock to the 'new user') after connecting to the database as sys or system..
the other problem:
if i do not have the ability to login into the database as SYS or SYSTEM how can (grant execute on dbms_lock to the 'new user') from within the 'new user' session??

thanks in Advance,
bahaa
Re: how to stop a running script but not exit from sqlpus [message #203303 is a reply to message #203248] Tue, 14 November 2006 12:47 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
the correct way is to use dbms_lock - a brute force way that burns CPU is:

SQL> set timing on
SQL> declare
  2   v_begin date := sysdate;
  3  begin
  4      loop
  5         exit when( sysdate > v_begin+5/24/60/60 ) ;
  6      end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.01
SQL> 
Re: how to stop a running script but not exit from sqlpus [message #204003 is a reply to message #203303] Fri, 17 November 2006 05:15 Go to previous message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi again,
i still can not get it right..see the code:

SET SERVEROUTPUT ON SIZE 100000
VARIABLE rc NUMBER
WHENEVER SQLERROR EXIT

DECLARE
l_count NUMBER;
ItExists NUMBER;
v_begin date := sysdate;
BEGIN
DBMS_OUTPUT.ENABLE(25000);

SELECT COUNT(*) INTO l_count FROM USER_OBJECTS WHERE OBJECT_NAME = 'CONTENTSITEMPROPERTIES' AND OBJECT_TYPE ='TABLE';
SELECT COUNT(*) INTO ItExists FROM USER_OBJECTS WHERE OBJECT_NAME = 'VWITEMPROPERTYLIST' AND OBJECT_TYPE ='VIEW';
IF (l_count=0 OR ItExists=0 )THEN
:rc := 1;
END IF;
IF (:rc <> 0) THEN
BEGIN

DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('--* In case the database has no ItemProperties objects,'||
'the upgrade process will fail ' );
DBMS_OUTPUT.PUT_LINE('--* due to invalid upgrading to a nonexistent objects.' );
DBMS_OUTPUT.PUT_LINE('--* execute these scripts '||''' @@ItemProperties\1_Objects.sql'''||',' );
DBMS_OUTPUT.PUT_LINE('--* '||'''@@ItemProperties\2_Actions.sql'''||' instade of the current script.' );
DBMS_OUTPUT.PUT_LINE('--* press ctrl-c to abandon the execution of the current script and start' );
DBMS_OUTPUT.PUT_LINE('--* the original ItemProperties scripts.' );
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------------------');

LOOP
EXIT WHEN( sysdate > v_begin+40/24/60/60 ) ;
END LOOP;

END;

END IF;
RAISE_APPLICATION_ERROR(-20000,'Error');

END;
/

how can i make the script shows the error message befor the closing of the SQLPLUS..??

Thanks in Advance,
bahaa
Previous Topic: Complex query
Next Topic: track stored procedures and functions that are "used" in a schema
Goto Forum:
  


Current Time: Fri Dec 09 21:38:29 CST 2016

Total time taken to generate the page: 0.05012 seconds