Re: Drop table in sql

Hopefully you (and any one else reading this) will find this utility procedure I have written to be of use;

create or replace procedure drop_schema_objs(obj_owner varchar2) is  

  del_objs      integer;
  obj_del       integer;
 
  cursor get_objs is
         select object_type,
                object_name||decode(object_type,'TABLE'
                                    ,' cascade constraints',null)
obj_nam
         from   dba_objects
         where  owner = upper(obj_owner)
         and    object_type in ('TABLE',


                                'VIEW',

                                'PACKAGE',

                                'SEQUENCE',

                                'PROCEDURE',

                                'SYNONYM');

 

begin
  del_objs := dbms_sql.open_cursor;
  for objrec in get_objs loop

      dbms_sql.parse(del_objs,'drop '||objrec.object_type||'  '
                             

||obj_owner||'.'||objrec.obj_nam,dbms_sql.v7);   end loop;
end;

In the format listed all the objects within a schema will be droppped. I suggest changing/parameterising the script to ensure that only the object types you desire are dropped.
Note, that as the statement is DDL rather than DML the act of parsing the statement causes the action to occur without need for a separate execution step.

Cheers,

Ian Cary Received on Mon Sep 22 1997 - 00:00:00 CDT