Compile a type with dependants.

I want to compile a type which is having dependents.

How to accomplish this? Any standard script available?
sakthikumarAsked:
Who is Participating?
 
sdstuberCommented:
assuming you had at least one other attribute defined within ssn you could drop the n_ attribute with the alter type command


alter type ssn drop attribute n_;
0
 
slightwv (䄆 Netminder) Commented:
There is an Oracle provided script that is supposed to compile objects in dependancy order:
%ORACLE_HOME%/rdbms/admin/utlrp.sql
0
 
sakthikumarAuthor Commented:
I tried executing  the above with the following :


@ D:\oracle\product\10.2.0\client_1\RDBMS\ADMIN\utlrp.sql

But I am getting the following error.

ORA-00904: "DBMS_REGISTRY_SYS"."TIME_STAMP": invalid identifier

Does that mean my doesn't have privileges to execute DBMS_REGISTRY_SYS.?

IF so, What is the role I should get for this privilege?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sakthikumarAuthor Commented:
Also will this script ask the type i want to compile.

or what is the procedure/steps to use this script.
0
 
Geert GOracle dbaCommented:
run it with sys

if you still get errors, restart your database and run it with sys again
0
 
slightwv (䄆 Netminder) Commented:
>>Also will this script ask the type i want to compile.

It doesn't ask.  It just recompies all invalid objects.


I also guess I'm not fully understanding the issue.  I get that the first object won;t compile because the dependant object might not have been created yet but after you create it, the first one should compile.

http://docs.oracle.com/cd/B28359_01/server.111/b28310/general007.htm#BABJAIFA

If you can post an example I'm sure we can assist.
0
 
sakthikumarAuthor Commented:
I have a type and type body like below

CREATE OR REPLACE TYPE ssn AS OBJECT (
 n_ CHAR(11),
CONSTRUCTOR FUNCTION ssn(ssn_in IN VARCHAR2) RETURN self AS result,
MEMBER FUNCTION get_ssn RETURN CHAR);
 /
 
 CREATE OR REPLACE TYPE BODY ssn IS
  CONSTRUCTOR FUNCTION ssn(ssn_in IN VARCHAR2)
   RETURN self AS RESULT IS
   BEGIN
     IF validate_ssn(ssn_in) THEN
       n_ := ssn_in;
       RETURN;
     ELSE
       RAISE_APPLICATION_ERROR(-20001, 'INVALID SSN');
     END IF;
   END;

   MEMBER FUNCTION get_ssn RETURN CHAR IS
   BEGIN
     RETURN n_;
   END;
END;

 CREATE OR REPLACE FUNCTION validate_ssn(ssn_in IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
   IF TRANSLATE(ssn_in, 'A0123456789', 'BAAAAAAAAAA') = 'AAA-AA-AAAA' THEN
     RETURN TRUE;
   ELSE
     RETURN FALSE;
   END IF ;
END validate_ssn;
 /

And created a table based on the object like below

CREATE TABLE person (
 per_name VARCHAR2(20),
 per_ssn  SSN);

+++++++++++++++++++++

When I tried to compile type ssn , It is not compiling, I am getting ORA - 02303

Then I searched in google, and dropped type using "force" keyword and then compiled it.

But now, when I query the table person I just have only one column, column based on type is not available. I dropped the table again and recreated to see all columns.

Is there anyway, I can compile the types without this problem?
0
 
slightwv (䄆 Netminder) Commented:
Running the example I am unable to reproduce the ora-02303.

I am able to produce an ora-02311 but that is because the type is valid.  What happened to invalidate the type that made you need to recompile it?
0
 
sakthikumarAuthor Commented:
I wanted to remove one attribute in the type.
0
 
sdstuberCommented:
>>> I wanted to remove one attribute in the type.


you can't  - it's a requirement of Oracle object model that each root type has at least one attribute.
0
 
sakthikumarAuthor Commented:
This type is referenced in another type like this

CREATE OR REPLACE TYPE tab_itt_reqdtl_submit IS TABLE OF rec_itt_reqdtl_submit;

and tab_itt_reqdtl_submit is used inside a table.

what could be the complications here?
Whether records will get deleted and if so,
How I can take backup from the table, and re-insert with out that column.
0
 
Geert GOracle dbaCommented:
during recompile check the dba_errors table for information

maybe that will give you more info
0
 
sdstuberCommented:
how does

CREATE OR REPLACE TYPE tab_itt_reqdtl_submit IS TABLE OF rec_itt_reqdtl_submit;

relate to this question?


you can copy the ssn strings from the object type into a backup table with the first query in the attached file.


after dropping and creating the column you can update the values with second statement in the file.
ee.txt
0
 
sakthikumarAuthor Commented:
Thank you, This is fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.