• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 836
  • Last Modified:

Compile a type with dependants.

I want to compile a type which is having dependents.

How to accomplish this? Any standard script available?
0
sakthikumar
Asked:
sakthikumar
  • 6
  • 3
  • 3
  • +1
1 Solution
 
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
 
sakthikumarAuthor Commented:
Also will this script ask the type i want to compile.

or what is the procedure/steps to use this script.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Geert GruwezOracle 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
 
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
 
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 GruwezOracle 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now