DECLARE TYPE name_list ISTABLEOFVARCHAR2(40); TYPE type_list ISTABLEOFVARCHAR2(20); Tab_name name_list:=name_list(); Tab_type type_list:=type_list(); sql_str VARCHAR2(500); BEGIN sql_str :='select uo.object_name,uo.object_type from user_objects uo where uo.object_type not in(''INDEX'',''LOB'') order by uo.object_type desc'; EXECUTE IMMEDIATE sql_str BULK COLLECT INTO tab_name,tab_type; FOR i IN Tab_name.FIRST.. Tab_name.LAST LOOP sql_str :='DROP '|| Tab_type(i) ||''|| Tab_name(i); EXECUTE IMMEDIATE sql_str; END LOOP; END;
