Problem solve Get help with specific problems with your technologies, process and projects.

Dropping objects without ORA-00942 errors

This tip enables you to install or drop objects without getting an Oracle error, even if the object doesn't exist.

This procedure enables to drop any object in an Oracle database if you have the rights for it. It will give you a clean spool file -- no ORA-00942 messages -- when installing or dropping objects where you are not sure if they exist or not. This produces no error messages even if the object does not exist. It may be useful when looking for any other errors in big spool files after installing hundreds of tables, views, packages, etc. The script has been tested on v. 8.1.7.
create or replace procedure dropObject (ownerName in varchar2, objectName in varchar2) is 

  
  cursor C1 is 
  select 'DROP '||o.object_type||' "' || o.owner || '"."' || o.object_name ||'"' 
    from sys.dba_objects o 
   where o.owner       = ownerName 
     and o.object_name = objectName ; 

  DDL_CURSOR integer; 
  ddl_statement varchar2(500); 

BEGIN 

 DDL_CURSOR := dbms_sql.open_cursor; 
 OPEN C1; 

 LOOP 

   FETCH C1 INTO ddl_statement; 
   EXIT WHEN C1%NOTFOUND; 
   dbms_sql.parse(DDL_CURSOR, ddl_statement, dbms_sql.native); 
     
 END LOOP; 

 dbms_sql.close_cursor(DDL_CURSOR); 
 CLOSE C1; 

END; 

This was last published in February 2004

Dig Deeper on Oracle error messages

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close