Oracle Package in Data Dictionary that can’t be dropped

oracleoracle-11gplsql

I've encountered a very interesting problem that I can't figure out how to fix. I have a package that I wanted to create on my database with a wrapped body. I mistakenly omitted the word "BODY" from the create or replace statement for the package body. Running the script that way created the package in such way that it shows up as invalid but cannot be dropped. The package shows up in queries to user_objects, etc but when attempting to drop the package I get ora-04043 not exist errors.

Anyone ever seen this before and know how to drop that corrupted package? 

Select * from user_objects where object_type = 'PACKAGE'

OBJECT_NAME = 'POSTING_PROCS'
SUBOBJECT_NAME = NULL   
OBJECT_ID =  134412
DATA_OBJECT_ID =    NULL
OBJECT_TYPE = 'PACKAGE'
CREATED = 12/29/2014 9:57:32 PM
LAST_DDL_TIME = 1/22/2015 11:08:52 AM
TIMESTAMP = 2015-01-22:11:08:52
STATUS =    INVALID
TEMPORARY = N
GENERATED = N
SECONDARY = N
NAMESPACE = 2
EDITION_NAME = NULL

in addition to not being able to drop the package I also can't run another create or replace because I get an error that the object already exists

select dump(object_name) from dba_objects where object_id=134412:

Typ=1 Len=13: 80,79,83,84,73,78,71,95,80,82,79,67,83

More info – I can run a create or replace for the package spec, but get the object with that name already exists error when doing create or replace package body

Best Answer

I am suspecting with the case sensitive name or special character(i.e. ' or white-space) included with the package name unknowingly while creating package. I tried to get object name from dictionary itself which ensures correct object name in your drop command in the following script, it can help in your context:

select 
'DROP PACKAGE ' || '"' || OBJECT_NAME || '";' as "Command" 
from 
USER_OBJECTS 
WHERE 
OBJECT_NAME LIKE '%POSTING_PROCS%'
AND OBJECT_TYPE='PACKAGE';

CAUTION - REMEMBER THAT ABOVE SCRIPT GENERATES DROP COMMANDS WHICH CAN CONTAINS POSTING_PROCS AS THE PART OF OTHER PACKAGE NAME, PLEASE CHECK EACH DROP STATEMENTS BEFORE EXECUTING THEM IN YOUR SERVER.

Alternatively - You might be using client tool like TOAD, SQL Tool, SQL Developer which display your invalid object name in a GUI format. You can try to delete your object by right click on the object and follow with the drop option.

Related Question