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 : ';

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:

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');

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.

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

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

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??

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