I have some experience with SQL Server but now I'm starting to work with PL SQL and I'm facing some problems because the difference between the two technologies.
I have some scripts to drop my database and when I need to delete my user I do something like this:
DEFINE OWNER = MYUSER ALTER SESSION SET DDL_LOCK_TIMEOUT = 3600; BEGIN FOR r IN (select sid,serial# from v$session where username=&OWNER) LOOP EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid || ',' || r.serial# || ''''; END LOOP; DROP USER &OWNER CASCADE; END; /
However I'm getting this error:
Details: ORA-06550: line 6, column 3: PLS-00103: Encountered the symbol \"DROP\" when expecting one of the following:
( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge json_exists json_value json_query json_object json_array (DBD ERROR:
error possibly near
<*>indicator at char 189 in 'BEGIN FOR r IN (select sid,serial# from v$session where username=MYUSER) LOOP EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid || ',' || r.serial# || ''''; END LOOP;
<*>DROP USER MYUSER CASCADE; END; ')
I already tried to add a slash in line 8:
END LOOP; / DROP USER &OWNER CASCADE; END; /
but the error still appearing, and I already tried to move
DROP USER &OWNER CASCADE; to the end of the script but I'm still getting errors.
I can't understand what is wrong and I already read many links this about this, including:
What is the problem?
Begin End don't allow empty lines?
DROP USER &OWNER CASCADE; must be inside another