Re: drop table script

From: <markp7832_at_my-deja.com>
Date: Fri, 19 Nov 1999 20:05:46 GMT
Message-ID: <814aim$6hh$1@nnrp1.deja.com>

In article <81491m$57j$1_at_nnrp1.deja.com>,   sonali2000_at_my-deja.com wrote:
> I am trying to write a drop table, view .. script, which will find all
> my user objects and drops them. But apparently PL/SQL does not support
> drops or creates, which is really weird. Following is the script that
I
> have written. Can someone suggest some modifications or a way to
achieve
> my goal? I have even tried putting the drop statement within single
> quotes and also within double quotes.
> Thanks in advance.
>
> P.S. - PLEASE MAKE SURE YOU HAVE A DUMMY USER/ DATABASE BEFORE USING
> THIS SCRIPT. IF IT DOES WORK, IT WILL DROP ALL THE OBJECTS IN YOUR
USER
> ACCOUNT.
>
> --Sonali.
>
> SCRIPT:
>
> DECLARE
> CURSOR cur_obj IS
> SELECT object_name, object_type FROM user_objects;
> BEGIN
> FOR cur_rec IN cur_obj LOOP
> drop cur_rec.object_type cur_rec.object_name;
> END LOOP;
> COMMIT;
> END;
> /
>

This is not pure SQL; it is pl/sql and pl/sql does not directly support DDL (data definition language) statements. With ver 8.1 you have the execute immediate statement can allows the execution of dynamic SQL including DDL within a pl/sql code. Prior to ver 8.1 you can use the dbms_sql package to execute DDL.

Or you could code something like the following in SQL*Plus: This was an attempt to speed the process of doing a 'drop user x cascade;' (which drops a user and all their objects) command when I had a list of x-employees to clean up.

set echo off
REM SQL*PLUS script to drop all objects owned by a specific user. REM
REM 19990623 m d powell Added cascade constraints clause. REM

REM                          Reset report data display

CLEAR BREAKS
CLEAR COLUMNS
CLEAR COMPUTE
REM
REM Flatfile - to create a flatfile of results do: REM
SET ECHO OFF
SET FEEDBACK OFF
SET LINESIZE 133
SET NEWPAGE 0
SET PAGESIZE 0
SET HEADING OFF
SET SPACE 0
set verify off
accept owner_name prompt 'Enter user whose objects are to be dropped => '
SPOOL dropusrtbl.sql
select 'spool dropusrtbl.lst'
from sys.dual
/
select 'drop '||object_type||' '||owner||'.'||object_name||

       decode(object_type,'TABLE',' cascade constraints;',';') from sys.dba_objects
where owner = upper('&owner_name')
and object_type != 'INDEX'
and object_type != 'TRIGGER'

order by decode(object_type,'FUNCTION'    ,'1',
                            'PACKAGE'     ,'4',
                            'PACKAGE BODY','3',
                            'PROCEDURE'   ,'2',
                            'SYNONYM'     ,'5',
                            'TABLE'       ,'7',
                            'VIEW'        ,'6',
                                          '0')

/
select 'spool off'
from sys.dual
/
spool off
select 'Run SQL*Plus job dropusrtbl to complete task'   from sys.dual
/
SET NEWPAGE 1
SET PAGESIZE 14
SET LINESIZE 80
SET ECHO ON
SET FEEDBACK ON
SET HEADING ON
undefine owner_name

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 19 1999 - 14:05:46 CST