1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help Pls: Invalid packages, table, utlrp.sql recompile error

Discussion in 'Server Administration and Options' started by Popeye.Tom, Apr 1, 2011.

  1. Popeye.Tom

    Popeye.Tom Active Member

    Messages:
    27
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Left Hand Coast, USA
    What tool (and syntax) should I use to compile three packages that have status=INVALID and I cannot seem to get to recompile?

    (warning...kind of long explanation of what I've tried so far.)

    This is a new test system, dump from production system imported. 3rd party PDM (PTC's Windchill) sits on top of Oracle. Had problem in windchill that I could not change the Revision on a CAD object in the vault. This is something I've seen before. I ran sql>select owner, object_name from dba_objects where object_type='PACKAGE BODY' and status='INVALID';

    This found the two expected guest.BASELINEPK and guest.EPMWORKSPACEPK. ('guest' is Windchill's Oracle user, the sid is 'wind') I fixed these by running:
    sql>alter package guest.<packagename> compile body;

    Three other packages were also found to be INVALID. These are:
    system.EPMSUPPORTINGDATAPK
    system.EPMFTVALIDATORPK
    system.EPM_FAMILY_TABLE_UTILS

    When I attempt sql>alter package system.<pakagename> compile body; I get the message "Warning: Package Body Altered with compilation errors."

    PTC recommended I run utlrp.sql. I read up on this and ran this script as sys. This did run, it found 6 objects with errors. It did report 1 error during recompilation. I ran sql>select * from UTL_RECOMP_ERRORS; and this is what I got:

    OBJ#
    ERROR_AT
    COMPILE_ERR
    14403
    01-APR-11 11.27.04.157000 AM
    ORA-04045: errors during recompilation/revalidation of SYSTEM.TABLE_OF_TABLE_OF_NUMBER
    ORA-06545: PL/SQL: compilation error - compilation aborted


    ++++++++++
    Checking the errors document, what it told me doesn't help me. Here are these two:
    ORA-04045: errors during recompilation/revalidation of string.string
    Cause: This message indicates the object to which the following errors apply. The errors occurred during implicit recompilation/revalidation of the object.
    Action: Check the following errors for more information, and make the necessary corrections to the object.

    ORA-06545: PL/SQL: compilation error - compilation aborted
    Cause: A pl/sql compilation error occurred and the compilation was aborted completely without the compilation unit being written out to the backing store. Unlike ora-06541, the user will always see this error along with the accompaning PLS-nnnnn error messages.
    Action: See accompanying PLS-nnnnn error messages.
    ++++++++++

    I then performed a shutdown immediate; followed by startup open;

    I logged back in as sys as sysdba. Running the sql>select owner, object_name from dba_objects where status='INVALID'; I receive this complete list:
    SYSTEM.EPMSUPPORINGDATAPK
    SYSTEM.TABLE_OF_TABLE_OF_NUMBER
    SYSTEM.EPMFTVALIDATORPK
    SYSTEM.EPM_FAMILY_TABLE_UTILS

    <<<<<<<<<<<<WOW, that was a lot! If you got this far, thank you already for wading through all that!>>>>>>>>>>>>>>>>>

    Any suggestions on what command or script I should use to compile these 4? Should I simply be using the sql>alter user.package compile body; as a different user (I've tried both system/manager@wind and sys as sysdba)

    Sincerely,
    Tom
     
  2. Popeye.Tom

    Popeye.Tom Active Member

    Messages:
    27
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Left Hand Coast, USA
    Advice from other Oracle forum is that the three packages and one table that report status='INVALID' and do not recompile need to be dropped and (re)created.

    I was having trouble actually getting to see actual errors since >SHOW ERRORS only works immediately after compile (OK) >and< when you own the object (uh-oh) was able to get at errors using >select * from dba_errors where owner='<owner>' and name='<package name>'

    Common errors for all 4 problem objects are PLS-00201, PLS-00304 and PLS-00905. Error files have been supplied to PTC (3rd party software sitting above Oracle). All 4 objects created by their software, awaiting their advise on (re)creating the three packages and one table.

    P. T.