erroneous error: ORA-21700: object does not exist or is marked for delete

[Tim Scott]

I'm using plsdev 9.0.6.1665 and have been working on this file for the past couple of days. This morning I started to get a most curious error when I try to build the package in a programme window, but the package builds OK when I run it as a Command Window ... taking the same code, right click - "Change Windows to" and switching between "Program Window" and "Command Window".

I've got the problem code down to a very simple:
CREATE OR REPLACE PACKAGE demo IS
TYPE mytype IS TABLE OF schema2.table1%ROWTYPE INDEX BY BINARY_INTEGER;
END;

If I change this to reference 'schema2.anothertable' then it works OK. I can query schema2.table1 from my login just fine.

I can build the exact problem code in schema2 without any problems.

I can carry on with the development, but can only build the package etc if I compile it using a Command Window or sqlplus. Whilst that gives me the line numbers for errors etc - I've then got to find the error lines myself. A tad annoying : reminds me of when we used to develop using Notepad (!).

This type line referencing schema2.table1 has been in the package since I started work on it first thing yesterday.

Any ideas/suggestions would be gratefully received.

From the Oracle documentation:
ORA-21700: object does not exist or is marked for delete
Cause: User attempted to perform an inappropriate operation to an object that is non-existent or marked for delete. Operations such as pinning, deleting and updating cannot be applied to an object that is non-existent or marked for delete.
Action: User needs to re-initialize the reference to reference an existent object or the user needs to unmark the object.

The database I'm connected to is a PDB within an Oracle 12.1.0.1.0 database on Linux x86-64.

 

[Marco Kalter]

Can you check if the "Safe compilation" preference is enabled (Tools > Preferences > Program Window)? If enabled, can you disable it and try again?

 

[Tim Scott]

Thanks for the reply.

It was not checked.
I checked it, but it made no difference.
Ever the optimist, I unchecked it and tried again.
That made no difference, either.

 

[Tim Scott]

Update: I restarted PL/SQL Developer now that I've finished that chunk of work (it had been a few days) and the problem seems to have gone away.

Hopefully it stays away ...

 

[Claus Pedersen]

I have experienced the same problem several times. The problem occurs when Oracle's internal data dictionary becomes corrupted.

When I encounter this problem, it is not related to using tables in other schemas (I have all tables in the same schema), but when I in a package refer to record types defined in other packages (either 'hardcoded' records or records of type %rowtype).
The cure for me is to list all packages referenced by the current package, drop all the referenced packages and re-load all the dropped packages again.

I have found other forums on the internet, where people suggest that if the package is loaded in a command window, the problem will not appear. But I suspect that this is because of the missing debug info. I believe that when you try to add debug information to the package, the problem re-appears.

The problem seems to be related to the version 12 database, I have not (as far as I can recall) experienced this on other database versions (10 or 11).

Does anybody here in the forum have similar experiences with this kind of error?