set linesize 32767
set pagesize 0
set feedback OFF
set trims on
set heading off
set echo off
set term off
spool c:\rn.sql
SELECT 'SPOOL c:\rn_RESULT.TXT' FROM DUAL;
select 'drop table "'||TABLE_NAME||'; ' from user_tables where table_name='SYS_DEPT';
SELECT 'SPOOL OFF' FROM DUAL;
spool off
@c:\rn.SQL
set pagesize 0
set feedback OFF
set trims on
set heading off
set echo off
set term off
spool c:\rn.sql
SELECT 'SPOOL c:\rn_RESULT.TXT' FROM DUAL;
select 'drop table "'||TABLE_NAME||'; ' from user_tables where table_name='SYS_DEPT';
SELECT 'SPOOL OFF' FROM DUAL;
spool off
@c:\rn.SQL
declare
cursor c_chk is
SELECT name into
FROM sysobjects
WHERE name = 'SYS_DEPT'
AND type = 'V';
cur_chk c_chk%rowtype;
bgein
open c_chk;
fetch c_chk into cur_chk;
if c_chk%found then
drop VIEW SYS_DEPT;
end;
close c_chk;
end
/
num number;
begin
select count(1) into num from user_views where VIEW_NAME='SYS_DEPT';
if num>0 then
execute immediate 'drop view SYS_DEPT'; --grant drop any view to user_name
end if;
end;
/