is it ok to do a Drop inside a PL SQL.
whats wrong with this SQL
I am trying to drop all users. The problem is in the drop user username cascase statement
connect sys/abcsds@1.1.1.1 as sysdba
exec dbms_output.enable(1000000)开发者_C百科;
set serveroutput on
DECLARE
BEGIN
for rec in (select username from dba_users where username LIKE 'S%' AND username NOT LIKE 'SY%') loop
dbms_output.put_line(to_char(rec.username));
dbms_sql.execute('drop user rec.username cascade');
end loop;
END;
.
run;
exit;
You can use DDL in a loop but you'll have to build the string since you can't bind object names.
For example:
DECLARE
l_ddl VARCHAR2(1000);
BEGIN
FOR rec IN (SELECT username
FROM dba_users
WHERE username LIKE 'S%'
AND username NOT LIKE 'SY%') LOOP
l_ddl := 'drop user '|| rec.username ||' cascade';
dbms_output.put_line(l_ddl);
EXECUTE IMMEDIATE (l_ddl);
END LOOP;
END;
Be careful though, DDL is not transactional in Oracle (it performs a commit before and after) and thus you won't be able to rollback any changes.
精彩评论