Oracle Package Invalid Error
Contents |
Social Links Printer Friendly About Search 8i | 9i | 10g | 11g | 12c | 13c | Misc how to compile invalid package body in oracle 11g | PL/SQL | SQL | RAC | WebLogic | Linux Home
Compile All Invalid Objects In Oracle
» Articles » Misc » Here Recompiling Invalid Schema Objects Operations such as upgrades, patches and how to fix invalid objects in oracle DDL changes can invalidate schema objects. Provided these changes don't cause compilation failures the objects will be revalidated by on-demand automatic recompilation, but this can take an oracle utlrp unacceptable time to complete, especially where complex dependencies are present. For this reason it makes sense to recompile invalid objects in advance of user calls. It also allows you to identify if any changes have broken your code base. This article presents several methods for recompiling invalid schema objects. Identifying Invalid Objects The Manual
Warning: Package Body Altered With Compilation Errors.
Approach Custom Script DBMS_UTILITY.compile_schema UTL_RECOMP utlrp.sql and utlprp.sql Identifying Invalid Objects The DBA_OBJECTS view can be used to identify invalid objects using the following query. COLUMN object_name FORMAT A30 SELECT owner, object_type, object_name, status FROM dba_objects WHERE status = 'INVALID' ORDER BY owner, object_type, object_name; With this information you can decide which of the following recompilation methods is suitable for you. The Manual Approach For small numbers of objects you may decide that a manual recompilation is sufficient. The following example shows the compile syntax for several object types. ALTER PACKAGE my_package COMPILE; ALTER PACKAGE my_package COMPILE BODY; ALTER PROCEDURE my_procedure COMPILE; ALTER FUNCTION my_function COMPILE; ALTER TRIGGER my_trigger COMPILE; ALTER VIEW my_view COMPILE; Notice that the package body is compiled in the same way as the package specification, with the addition of the word "BODY" at the end of the command. An alternative approach is to use the DBMS_DDL package to perform the recompilations. EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE'); EXEC DBMS_DDL.alter_compile('PACKA
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this page. Please enter a title. You can not post a blank message. Please type your message and oracle recompile invalid objects automatically try again. More discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application DevelopmentPL/SQL and SQL This oracle compile procedure discussion is archived 1 2 Previous Next 24 Replies Latest reply on Apr 17, 2007 9:56 AM by Laurent Schneider Go to original
How To Compile Invalid Objects In Oracle Apps R12
post This content has been marked as final. Show 24 replies 15. Re: How to invalidate a package without modify? NicloeiW Apr 17, 2007 7:32 AM (in response to Satyaki_De) Drop one table, or view or proc https://oracle-base.com/articles/misc/recompiling-invalid-schema-objects or function which is begin used in the package, the package would get invalidate, without being modified, is this an Interview queston ? Like Show 0 Likes(0) Actions 16. Re: How to invalidate a package without modify? Laurent Schneider Apr 17, 2007 8:03 AM (in response to NicloeiW) whatever the objective is, I could imagine SQL> create or replace package p is $IF ($$x) $then INVALID $else $end end; 2 / Package created. SQL> https://community.oracle.com/thread/499121?start=15&tstart=0 select status from user_objects where object_name='P'; STATUS ------- VALID SQL> alter package p compile PLSQL_CCFLAGS='x:true'; Warning: Package altered with compilation errors. SQL> select status from user_objects where object_name='P'; STATUS ------- INVALID Like Show 0 Likes(0) Actions 17. Re: How to invalidate a package without modify? Satyaki_De Apr 17, 2007 8:21 AM (in response to Laurent Schneider) This is really good one. Regards. Satyaki De. Like Show 0 Likes(0) Actions 18. Re: How to invalidate a package without modify? NicloeiW Apr 17, 2007 8:37 AM (in response to Laurent Schneider) this really good one Like Show 0 Likes(0) Actions 19. Re: How to invalidate a package without modify? 387878 Apr 17, 2007 9:24 AM (in response to NicloeiW) Thanks Laurent, Looks like your solution is the best proposed. Restrictions: 1)Requires modification over an existing package 2)Since 10g Thanks a lot to all posting aldo Like Show 0 Likes(0) Actions 20. Re: How to invalidate a package without modify? 94799 Apr 17, 2007 9:31 AM (in response to 387878) Well there is a difference between marking a package as invalid (prompting successful re-compilation on next execution) or actually making it invalid, failing re-compilation. I thought you were asking for the former, but now it appears you want the latter. Like Show 0 Likes(0) Actions 21. Re: How to invalidate a package without modify? Laurent Schneider Apr
and Object Invalidation Manually Recompiling Invalid Objects with DDL Manually Recompiling Invalid Objects with PL/SQL Package Procedures About Object Dependencies and Object Invalidation Some types of https://docs.oracle.com/cd/B28359_01/server.111/b28310/general007.htm schema objects reference other objects. For example, a view contains a query http://www.workflowfaq.com/invalid-package-error-for-a-valid-package that references tables or other views, and a PL/SQL subprogram might invoke other subprograms and might use static SQL to reference tables or views. An object that references another object is called a dependent object, and an object being referenced is a referenced object. These in oracle references are established at compile time, and if the compiler cannot resolve them, the dependent object being compiled is marked invalid. Oracle Database provides an automatic mechanism to ensure that a dependent object is always up to date with respect to its referenced objects. When a dependent object is created, the database tracks dependencies between the invalid objects in dependent object and its referenced objects. When a referenced object is changed in a way that might affect a dependent object, the dependent object is marked invalid. An invalid dependent object must be recompiled against the new definition of a referenced object before the dependent object can be used. Recompilation occurs automatically when the invalid dependent object is referenced. It is important to be aware of changes that can invalidate schema objects, because invalidation affects applications running on the database. This section describes how objects become invalid, how you can identify invalid objects, and how you can validate invalid objects. Object Invalidation In a typical running application, you would not expect to see views or stored procedures become invalid, because applications typically do not change table structures or change view or stored procedure definitions during normal execution. Changes to tables, views, or PL/SQL units typically occur when an application is patched or upgraded using a patch script or ad-hoc DDL statements. Dependent objects might be left invalid a
Smiths Medical International Careers Services We Provide Consultancy Technical Delivery Training Design Review Implementation Review Support Blog Forum Contact Us Search Invalid package error for a valid package! May 30, 2008February 4, 2010 by Matt One of the most annoying "features" of Oracle Workflow is an inconsistent error message that fires occasionally that the package is invalid, yet if you check the state of the package it's valid. The problem only arises if you have compiled a new package header as well as package body, and the Workflow has run that package before. When the package is run the first time (with the old version of the package), the Workflow engine essentially obtains a "pin" to that particular version of the package in memory. When you recompile the package spec and body, this is fine and (assuming the package is valid!) it will compile with no errors. If you try to execute a call to the package in that SQL session, it will run fine because you are executing your code in a session which has only ever executed the current version of the package. When the Workflow engine attempts to execute the procedure, the version that it has cached is no longer valid, so you get an Oracle error: Existing state of package has been discarded. If you have configured the error handling correctly for the activity, this will invoke the standard error workflow, which will send a notification to a system administrator. If they open the notification and retry the node that is in error, the system will use the new version of the package, since it is now in a new session. However, this will only fix the one node - if a new process attempts to call the procedure again, you will get the same error. So, how do you avoid the error? There are only three suggestions here, each as impractical as each other. 1 - Don't use packages. Instead, use procedures for PL/SQL activities in Workflow. When you recompile the procedure, you shouldn't have the problem that you get in packages, since it should force a re-compilation. I've never even tried it, since as far as I am concerned, just using procedures isn't a sensible option. I can't think of the last time I just delivered one procedure or function - they tend to be parts of a much larger solution (particularly with a Workflow), and it makes sense to store them together in a package. 2 - You could create a new package every time you need to change it. This ensures that with every re-compilation, the Workflow engine can never have seen the package before and cached an old version. However, if the change is relatively minor, the impact on