Re: Cursor to drop all tables

Hi,

I don't know how DBMS_SQL works but don't you need to do a DBMS_SQL.EXECUTE after a DBMS_SQL.PARSE to execute a statement ?

In article <386a209b.0_at_news.hcs.net>,
  "Jim Day" <jday_at_fslso.com> wrote:
> The following is code that our developers created and we use to drop
all
> objects for a user. All of our production database objects are owned
by a
> single user/schema. I think you have to be careful if you just drop
> tables....
>
> Anyway, this is what I use to drop all the objects BEFORE I run the
import
> (IMP) utility to populate my test database with the production data.
>
> Just change the XXXXXXX to your username....and please test before
using on
> production data. It works for me....but.....
>
> Jim
>
> --
> -- WARNING - THIS SCRIPT WILL REMOVE ALL OBJECTS FOR
> -- THE SCHEMA/USER 'XXXXXXX'. MAKE SURE YOU ARE IN
> -- THE CORRECT INSTANCE OF THE DATABASE BEFORE YOU
> -- RUN THIS SCRIPT!
> --
>
> set define ~
> set define $
>
> DECLARE
> CURSOR c_Constraints IS
> SELECT CONSTRAINT_NAME, TABLE_NAME
> FROM ALL_CONSTRAINTS
> WHERE OWNER = 'XXXXXXX'
> AND CONSTRAINT_TYPE = 'R';
>
> v_Constraint c_Constraints%ROWTYPE;
>
> CURSOR c_Tables IS
> SELECT TABLE_NAME
> FROM ALL_TABLES
> WHERE OWNER = 'XXXXXXX';
>
> v_Table c_Tables%ROWTYPE;
>
> CURSOR c_Views IS
> SELECT View_NAME
> FROM ALL_VIEWS
> WHERE OWNER = 'XXXXXXX';
>
> v_View c_Views%ROWTYPE;
>
> CURSOR c_Sequences IS
> SELECT SEQUENCE_NAME
> FROM ALL_SEQUENCES
> WHERE SEQUENCE_OWNER = 'XXXXXXX';
>
> v_Sequence c_Sequences%ROWTYPE;
>
> CURSOR c_Synonyms IS
> SELECT SYNONYM_NAME
> FROM ALL_SYNONYMS
> WHERE OWNER = 'XXXXXXX';
>
> v_Synonym c_Synonyms%ROWTYPE;
>
> CURSOR c_Procs IS
> SELECT DISTINCT NAME, TYPE
> FROM ALL_SOURCE
> WHERE OWNER = 'XXXXXXX';
>
> v_Proc c_Procs%ROWTYPE;
>
> v_Stmt VARCHAR2(200);
> v_Cursor NUMBER;
> BEGIN
> v_Cursor := DBMS_SQL.OPEN_CURSOR;
>
> OPEN c_Constraints;
> LOOP
> FETCH c_Constraints INTO v_Constraint;
> EXIT WHEN c_Constraints%NOTFOUND;
> v_Stmt := 'ALTER TABLE ' || v_Constraint.TABLE_NAME || ' DROP
CONSTRAINT '
> || v_Constraint.CONSTRAINT_NAME;
> DBMS_SQL.PARSE(v_Cursor, v_Stmt, DBMS_SQL.V7);
> END LOOP;
> CLOSE c_Constraints;
>
> OPEN c_Tables;
> LOOP
> FETCH c_Tables INTO v_Table;
> EXIT WHEN c_Tables%NOTFOUND;
> v_Stmt := 'DROP TABLE ' || v_Table.TABLE_NAME;
> DBMS_SQL.PARSE(v_Cursor, v_Stmt, DBMS_SQL.V7);
> END LOOP;
> CLOSE c_Tables;
>
> OPEN c_Views;
> LOOP
> FETCH c_Views INTO v_View;
> EXIT WHEN c_Views%NOTFOUND;
> v_Stmt := 'DROP VIEW ' || v_View.VIEW_NAME;
> DBMS_SQL.PARSE(v_Cursor, v_Stmt, DBMS_SQL.V7);
> END LOOP;
> CLOSE c_Views;
>
> OPEN c_Sequences;
> LOOP
> FETCH c_Sequences INTO v_Sequence;
> EXIT WHEN c_Sequences%NOTFOUND;
> v_Stmt := 'DROP SEQUENCE ' || v_Sequence.SEQUENCE_NAME;
> DBMS_SQL.PARSE(v_Cursor, v_Stmt, DBMS_SQL.V7);
> END LOOP;
> CLOSE c_Sequences;
>
> OPEN c_Synonyms;
> LOOP
> FETCH c_Synonyms INTO v_Synonym;
> EXIT WHEN c_Synonyms%NOTFOUND;
> v_Stmt := 'DROP SYNONYM ' || v_Synonym.SYNONYM_NAME;
> DBMS_SQL.PARSE(v_Cursor, v_Stmt, DBMS_SQL.V7);
> END LOOP;
> CLOSE c_Synonyms;
>
> OPEN c_Procs;
> LOOP
> FETCH c_Procs INTO v_Proc;
> EXIT WHEN c_Procs%NOTFOUND;
> v_Stmt := 'DROP ' || v_Proc.TYPE || ' ' || v_Proc.NAME;
> DBMS_SQL.PARSE(v_Cursor, v_Stmt, DBMS_SQL.V7);
> END LOOP;
> CLOSE c_Procs;
>
> DBMS_SQL.CLOSE_CURSOR(v_Cursor);
> END;
> /
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jan 07 2000 - 03:52:13 CST