CODEWITHSUNDEEP
oracle-databaseplsqluser-defined-typesobject-type
Laura Ritchey
Laura Ritchey

Reputation: 711

Oracle PL/SQL table of parent object type breaks when I add another inheriting child object type

I am using custom object types with Oracle PL/SQL, including several object types that inherit from a parent object. I have a TP_DOCUMENTS parent object, and child document types, such as TP_PUBLICATION, TP_CONTRACT, etc. We successfully created a table of TP_DOCUMENT and have added records of TP_PUBLICATION, TP_CONTRACT, and other child document records. However, I needed to create an additional type of document. Once I did this, it broke the DOCUMENTS table. How can I create additional child types, without breaking the table of the parent object (making me lose all the data previously contained in the parent object table!!)?

Here is some of my code:

create or replace TYPE           "TP_DOCUMENT" AS OBJECT 
( 
...fields go here
) NOT FINAL

create or replace TYPE           "TP_PUB_INSTRUCTION" UNDER TP_DOCUMENT()

CREATE TABLE DOCUMENTS OF TP_DOCUMENT

After creating these types (and others with additional fields), I created the table DOCUMENTs as shown above. I tried to create another sub-type, and the DOCUMENTS table broke.

MORE INFORMATION:

The error code message is as follows:

ORA-04063: table/view has errors 

Cause:  Attempt to execute a stored procedure or use a view that has errors.  For stored procedures, the problem could be syntax errors or references to other, non-existent procedures.    For views,  the problem could be a reference in the view's defining query to a non-existent table.  Can also be a table which has references to non-existent or inaccessible types.   

Action: Fix the errors and/or create referenced objects as necessary.

Thank you!

UPDATE WITH ANSWER FROM COMMENTER BELOW:

I had unfortunately dropped a Sub-Type using the Force option. That likely was the cause for why my Documents table was corrupted. In the future, I will use the Validate command (see answer below).

Upvotes: 1

Views: 887

Answers (1)

Jon Heller
Jon Heller

Reputation: 36817

Instead of FORCE you should use the VALIDATE option when dropping types:

VALIDATE

If you specify VALIDATE when dropping a type, then Oracle Database checks for stored instances of this type within substitutable columns of any of its supertypes. If no such instances are found, then the database completes the drop operation.

This clause is meaningful only for subtypes. Oracle recommends the use of this option to safely drop subtypes that do not have any explicit type or table dependencies.

Here's an example:

create or replace type tp_document as object 
( 
    a number
) not final;

create or replace type tp_pub_instruction under tp_document();

create table documents of tp_document;

--This fails with this error message:
--ORA-02303: cannot drop or replace a type with type or table dependents
drop type tp_pub_instruction;

--This works since there's no data with that type.
drop type tp_pub_instruction validate;

Upvotes: 1

Related Questions