Solved

Problem running binary packages in Oracle 9i

Posted on 2004-03-28
12
1,662 Views
Last Modified: 2007-12-19
Hi,
 
I am having problem running the package specification and body files in Oracle 9i.
I am having 2 files util_pkg.pks for specifications and util_pkg.pkb for package body. I am able to run these files successfully in Oracle 8i but when run in Oracle 9i it gives the following compilation error.

1780/28  PLS-00593: default value of parameter "ROLLUP_TYPE" in body must
         match that of spec

3333/22  PLS-00593: default value of parameter "JOB_OPTION" in body must
         match that of spec

Any idea whats happening?
0
Comment
Question by:smita_kulkarni
  • 6
  • 5
12 Comments
 
LVL 47

Expert Comment

by:schwertner
ID: 10701877
1. Shut down the database

2. Edit the init.ora file by adding the following:
event="10932 trace name context level 32768"
event="10933 trace name context level 512"
event="10943 trace name context level 16384"

3. Restart the database

4. Invalidate the package bodies and package specifications

5. Recompile the objects.



Note from Metalink:

·      fact: Oracle Server - Enterprise Edition 9.0
·      
·      symptom: Compilation Error during Compilation of Package Body
·      
·      symptom: PLS-00593: default value of parameter %s in body must match that
·      of spec
·      
·      change: Upgrade of database from Oracle 8.x to Oracle 9.x
·      
·      cause: PL/SQL checking has been increased in Oracle 9i due to the fix to
·      <bug:425551>,
·      the specification in the package header must now exactly match that of the
·      definition in the body.
·      
·      The following example will show the 2 cases possible:
·      
·      Case 1: default value present in body but not specified in header:
·      
·      SQL> create or replace package test as
·           procedure abc (n in number);
·           end test;
·           /
·      SQL> show errors
·      
·      SQL> create or replace package body test as
·           procedure abc (n in number default 1) is
·           begin
·           null;
·           end abc;
·           end test;
·           /
·      SQL> show errors
·      
·      ===> PLS-00593: default value of parameter "N" in body must match
·      that of spec
·      
·      Case 2: default value in body is not identical to default value in header:
·      
·      SQL> create or replace package test as
·           procedure abc (n in number default 2);
·           end test;
·           /
·      SQL> show errors
·      
·      SQL> create or replace package body test as
·           procedure abc (n in number default 1) is
·           begin
·           null;
·           end abc;
·           end test;
·           /
·      SQL> show errors
·      
·      ===> PLS-00593: default value of parameter "N" in body must match
·      that of spec
·      
·      


fix:

Change the code so that the declaration of the function or procedure is
identical in the package header and the package body.

Workaround:

In not all cases the source code is available of the package in question (3th
party/wrapped code/...). Enforcement of the old rules/behaviour can be done
with:
SQL> alter session set events = '10932 trace name context level 32768';

Alternatively, specify the event in the init.ora parameter file:

event="10932 trace name context level 32768"

Note that this event works differently in 8i versions as compared to 9i :
- In 8i this event causes the rigid type checking to be enforced.
- In 9i the behaviour is reversed and the event disables the rigid type
checking.

Keep in mind that setting the event is only a workaround, the code in the
package
has to be adjusted eventually.



0
 

Author Comment

by:smita_kulkarni
ID: 10702154
Hi schwertner,
Thanks.I tried the solution you mentioned, but it didn't worked.Although i havn't done the step 4 you mentioned , since i didn't got it.
So, what i did is stopeed db, added entries in init.ora file and started db again.But still I am getting the same errors.
Also,is it possible to know how to match the specification entries against the body entries?

Thanks,
Smita
0
 
LVL 47

Accepted Solution

by:
schwertner earned 500 total points
ID: 10702750
Change the code of the packages if it is availabel.

If this is not the case:

In not all cases the source code is available of the package in question (3th
party/wrapped code/...). Enforcement of the old rules/behaviour can be done
with:
SQL> alter session set events = '10932 trace name context level 32768';

Alternatively, specify the event in the init.ora parameter file:

event="10932 trace name context level 32768"

0
 

Author Comment

by:smita_kulkarni
ID: 10702812
Hi schwertner,

Thanks a lot.It did helped me a lot.
But want to ask one question out of curiosity, how to identify default parameters has same values in both specification and body files for packages? I have the binary source code for the package specification and package body but I am not able to understand them. If you can help me out of this, it will be very greatful.
If you want sourcecode, I can give you that.

Thanks again for your help,
Smita
0
 
LVL 47

Expert Comment

by:schwertner
ID: 10703138
If you have only the binaries it is not possible to see the keywords there. You can do changes only if you have the source codes.
I am sure that one of the solutuions  i post will work. You have to test them very carefull. If you did atempts to workaround then delete the changes you made before using the next option.
0
 

Author Comment

by:smita_kulkarni
ID: 10703233
They are not binaries but .pks and .pkb files with keywords and binary code in them.
Anyway, it did worked with your earlier suggestion.I was curious to know the .pks and .pkb files and how they are written,so I asked you. Is there any way I can send you these files,so you can take a look at them?
Thanks a lot,
Smita
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:schwertner
ID: 10703622
There is a wrapper in Oracle which creates these files. The binaries are coded, so even if you send them to me, I do not know how to decode them.
Wait.
M`ay be other Experts will be able to help.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10704428
@smita_kulkarmi: ---->Any idea whats happening?


Yes sure. The answer is obvious...

your package is not self-contained , i.e., it depends on some other UDTs (user definied TYPEs or objects)..that you might need to port from Oracle8i to oracle9i.

Compare the data schema difference between these two database,

for example: do you have a ROLLUP_TYPE data type in Oracle9i database?

do you have DBMS_JOB package installed in oracle9i?


0
 

Author Comment

by:smita_kulkarni
ID: 13419226
Hi,

By doing :
alter session set events = '10932 trace name context level 32768';
the package status becomes valid for some time.But if we access the package code through some other application it gives error again that the package status is invalid.
How to solve this problem permanently?

Thanks,
Smita
0
 
LVL 47

Expert Comment

by:schwertner
ID: 13420345
The package will get invalid if you change something in the object (tables, views, functions, procedures, etc.)
cited in the package.
It seems that one or more object got recreated.
The only fix is to schedule a job which will recompile the specification and the body in some time interval, e.g. 1 hours.
It will be not time consuming.
0
 

Author Comment

by:smita_kulkarni
ID: 13437738
Hi,
I didn't quite understand what do you mean by "The package will get invalid if you change something in the object (tables, views, functions, procedures, etc.)
cited in the package. "
Following are the things I am doing :
1.Creating a package using binary package code.(Here before executing *.pks and *.pkb I am doing alter session command,thats why it is not giving error while creation.)
2.After that when my application starts,it calls some triggers.This triggers are internally calling function from the package.
Here it is throwing error for invalid package.

Also,if I schedule the job to recompile it,still there will be some time for which the functionality from the package will not work.

Is there any other solution to it?

Thanks,
Smita
0
 
LVL 47

Expert Comment

by:schwertner
ID: 13438185
If you have stored procedure in Oracle and if in this procedure is statement which regards an Oracle object (e.g. table) and you change the structure of the table (i.e. rename or drop column, drop the table) the procedure will get INVALIDE. You have to recompile it to get valid. Think the same is for packages.

Read this:

Frequently Asked Questions about Invalid Objects.
 
 
Scope & Application
-------------------
This document is intended for any user who has a working knowledge of SQL and
the database that they are working with.
 
 
Contents
--------
1.  Why do I have invalid objects?  What causes them?
2.  Why does Oracle Support always tell me to recompile my invalid objects?
3.  Are invalid objects ever acceptable?  How many is too many?
4.  If we do not use the application with the invalid objects, can we delete
    them?
5.  Which OBJECTS table is best to use for queries?  ALL, USER or DBA?
6.  How can I get a quick count of my invalid objects (if any) for regular
    maintenance?
7.  I have invalid objects.  How can I get a full or partial detailed list of
    them?
8.  How can I manually recompile individual invalid objects?
9.  How can I use adadmin to recompile my invalid objects?
10. How can I recompile all my invalid objects using ADCOMPSC.pls?
11. What if I still have invalid objects than can not be resolved by  
    ADCOMPSC.pls?
 
 
Questions & Answers
-------------------
1.  Why do I have invalid objects?  What causes them?
 
    Invalid objects can and will occur for many reasons.  You will usually find
    invalid objects after running (or failing to run) adprepdb, doing an
    export/import, upgrading, or applying patches.  Invalid objects are usually
    caused by missing grants, synonyms, views, tables or packages, but can also
    be caused by corrupted packages.  
 
2.  Why does Oracle Support always tell me to recompile my invalid objects?
 
    Compiling invalid objects on your database is almost the equivalent of
    running scandisk on a PC hard drive.  This should be one of the first
    things you check if you start experiencing problems with your Oracle
    database.  It is also a good idea to schedule regular checks for invalid
    objects.
 
    When you call in to Oracle Support with a database or installation issue,
    one of the first questions they will probably ask is whether you have
    checked for and resolved any invalid objects.
 
3.  Are invalid objects ever acceptable?  How many is too many?
 
    If the invalid objects exist for a product or application that is not
    installed, it may be ok to have some, but it is preferable to have no
    invalid objects existing at all.  If invalid objects exist for a product
    or application that you do have installed and are using, then it should be
    considered unacceptable and any existing invalid objects should be resolved
    before further issues can occur.
 
    There is no set number of invalid objects that could be considered
    'acceptable' as each situation will vary widely from one database to the
    next.  You could just have a few invalid objects or they could number in
    the hundreds or even thousands, but every effort should be made to resolve
    them one way or another.
 
4.  If we do not use the application with the invalid objects, can we delete
    them?
 
    There are times when invalid objects have occurred where you may opt to
    simply delete them, but you must ensure that they are in a product or
    application that is not used.
 
5.  Which OBJECTS table is best to use for queries?  ALL, USER or DBA?
 
    You should normally use DBA_OBJECTS, but there may be occasions when you
    will want to use USER_OBJECTS.  It is not recommended to use ALL_OBJECTS.
 
      USER_OBJECTS - returns objects owned by the user (schema) you are
                     connected as.
      DBA_OBJECTS  - returns every object in the Database.
 
 
6.  How can I get a quick count of my invalid objects (if any) for regular
    maintenance?
 
    To get a quick count of the number of existing invalids (if any), use the
    following select statement:
 
      SELECT COUNT(*)
      FROM DBA_OBJECTS
      WHERE STATUS = 'INVALID';
 
    For a more detailed query, use the following script:
 
      SELECT OWNER, OBJECT_TYPE, COUNT(*)
      FROM DBA_OBJECTS
      WHERE STATUS = 'INVALID'
      GROUP BY OWNER, OBJECT_TYPE;
 
7.  I have invalid objects.  How can I get a full or partial detailed list of
    them?
 
    Run the following script to get a detailed listing of all invalid objects:
 
      COLUMN OWNER FORMAT A16 HEADING 'OWNER'
      COLUMN OBJECT_NAME FORMAT A30 HEADING 'OBJECT NAME'
      COLUMN OBJECT_TYPE FORMAT A16 HEADING 'OBJECT TYPE'
      SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
      FROM DBA_OBJECTS
      WHERE STATUS = 'INVALID'
      ORDER BY OWNER, OBJECT_TYPE;
 
    If you are looking for more specific queries such as by particular
    application object names or specific object types, modify your script as
    needed.  The following two examples will find invalid PA objects or invalid
    package bodies:
 
      COLUMN OWNER FORMAT A16 HEADING 'OWNER'
      COLUMN OBJECT_NAME FORMAT A30 HEADING 'OBJECT NAME'
      COLUMN OBJECT_TYPE FORMAT A16 HEADING 'OBJECT TYPE'
      SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
      FROM DBA_OBJECTS
      WHERE STATUS = 'INVALID'
      AND OBJECT_NAME LIKE 'PA%'
      ORDER BY OBJECT_TYPE, OWNER;
 
 
      COLUMN OWNER FORMAT A16 HEADING 'OWNER'
      COLUMN OBJECT_NAME FORMAT A30 HEADING 'OBJECT NAME'
      COLUMN OBJECT_TYPE FORMAT A16 HEADING 'OBJECT TYPE'
      SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
      FROM DBA_OBJECTS
      WHERE STATUS = 'INVALID'
      AND OBJECT_TYPE LIKE 'PACKAGE BODY'
      ORDER BY OWNER, OBJECT_NAME;  
 
8.  How can I manually recompile individual invalid objects?
 
    To recompile an individual object, connect to SQL*PLUS as the owner of the
    object (generally apps).  Use one of the following depending on the object
    type:
 
      SQL> alter package <package_name> compile;       (package specification)
      SQL> alter package <package_name> compile body;  (package body)
      SQL> alter view <view_name> compile;             (view)
 
    If the object compiles with warnings, use either of the following to see
    the errors that caused the warnings:
 
      SQL> show errors
        or
      SQL> select * from user_errors where name = '<OBJECT_NAME>';
 
 
9.  How can I use adadmin to recompile my invalid objects?
 
    Another way to correct invalid objects is to run the adadmin utility as
    follows:
 
    UNIX OPERATING PLATFORM
    -----------------------
    a. Log in as applmgr:  <applmgr username>/<applmgr password>
    b. Start the utility from the Unix prompt with this command:
 
       $ adadmin
 
    The utility will then ask you a series of questions.
 
    c. Under the Maintain Applications Database Objects Menu, run Task 7 which
       is Compile APPS schema(s)
 
       This task spawns parallel workers to compile invalid database objects in
       your APPS schema(s). It uses the same parallel phases as AutoInstall.
 
    WINDOWS NT OPERATING PLATFORM
    -----------------------------
    a. Log in as applmgr:  <applmgr username>/<applmgr password>
    b. Start the utility from the Dos prompt with this command:
 
       > adadmin
 
    c. Under the Maintain Applications Database Objects Menu, run Task 5 which
       is Compile APPS schema(s)
 
10. How can I recompile all my invalid objects using ADCOMPSC.pls?
 
    Within Applications, there is a script to compile INVALID objects - called
    ADCOMPSC.pls
 
      Arguments for ADCOMPSC.pls:
      1 - Schema to run in
      2 - Password for schema
      3 - Check errors for objects starting with #3
 
    NOTE:  The order in which to compile Invalid Objects in schemas is
           SYS, SYSTEM, APPS and then all others.  APPS_DDL and APPS_ARRAY_DDL
           should exist in all schema's.  If you encounter an ORA-1555 error
           while running adcompsc.pls, just restart the script.
 
    The script can be run as follows:
 
      cd $AD_TOP/sql
      sqlplus @adcompsc.pls SCHEMA_NAME SCHEMA_PASSWORD %
 
      Example:  SQL> @adcompsc.pls apps apps %
 
    After the script completes, check for invalid objects again.  If the number
    has decreased, but you still have invalid objects, you should run
    adcompsc.pls again.  Keep running adcompsc.pls until number of invalid
    objects stops decreasing.
 
11. What if I still have invalid objects than can not be resolved by
    ADCOMPSC.pls?
 
    If there are any objects still left INVALID, you can verify them by using
    aderrchk.sql to record the remaining INVALID objects.  Aderrchk.sql use the
    same syntax as adcompsc.pls.  This script is also supplied with the
    Applications. You can send the aderrchk.sql to a file using the
    spool <file> command in sqlplus.
 
      e.g. sqlplus x/y @aderrchk.sql SCHEMA_NAME SCHEMA_PASSWORD %
 
    For objects will not compile, try the following:  
 
      select text
      from user_source
      where name = 'OBJECTNAME'
      and text like '%Header%';
 
    This script will give you the sql that creates the packages.  You can then
    recreate the packages.  SQL>@packageheader     SQL>@packagebody
    If recreating the package does not make the package valid you will have to
    analyze the user_errors table to try to determine the cause of the invalid
    package.
 
      select text
      from user_errors
      where name = 'PACKAGENAME';
 

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now