Akdora’s Blog

Programming, Oracle, Life, Fun

ORA-06508 PL/SQL: could not find program unit being called September 11, 2009

When you search this error on the net, you will find out these :

Cause: An attempt was made to call a stored program that could not be found. The program may have been dropped or incompatibly modified, or have compiled with errors.
Action: Check that all referenced programs, including their package bodies, exist and are compatible.

Yes, that’s true. But if you have lots of objects depends to a lot of object🙂 You will need to more information to fix this error. If you face up with this error, you probably use SQLCODE and SQLERRM in your exception block of your PL/SQL code. Something  like this:

BEGIN
 NULL;
EXCEPTION
 WHEN OTHERS THEN
 ROLLBACK;
 INSERT_LOG(SQLCODE, SQLERRM);
END;

I do not advise to handle errors in this way. Because this error description does not give you enough information abour your error as ORA-06508 error. You don’t really know what triggered this exception. Operation of this code on a production database is also not easy. At this point, Oracle’s DBMS_UTILITY package will be included in out lives.🙂

If you write something like this:

BEGIN
 NULL;
EXCEPTION
 WHEN OTHERS THEN
 ROLLBACK;
 INSERT_LOG(SQLCODE,
 DBMS_UTILITY.FORMAT_ERROR_STACK || '@' ||
 DBMS_UTILITY.FORMAT_CALL_STACK);
END;

You will not get ORA-06508 error for the same error. You will get something like this explanation:

ORA-04045: errors during recompilation/revalidation of SIM.PKXXX
ORA-04052: error occurred when looking up remote object TCLCM.PRC_XX@ERP_APPS.WORLD
@----- PL/SQL Call Stack -----
 object      line  object
 handle    number  name
3dc3a9010      1565  package body SIM.PK_XXX
3d9571de8         3  anonymous block

Upss, that’s a db_link problem in real:)

Nice coding..

 

8 Responses to “ORA-06508 PL/SQL: could not find program unit being called”

  1. Chandra Kishore Kovuru Says:

    When I faced this problem, I checked for all procedures status in user_objects, to my surprise I found two packages with the same name one in valid state and another in invalid state. When I dropped the package both of them got dropped and later created the package the problem was resolved.

    • akdora Says:

      You are right Chandra,
      We call procedur_x, it calls procedure_y and procedure_y also calls procedure_z. If procedure_z is invalid beacause of any modification on the database, when we call procedure_x, we also get this error message.

      thx

  2. Yunni Says:

    Wow really helpful exception catch to track down the real problem. Thanks.

  3. cyan Says:

    thank u for sharing this! very helpful indeed!🙂

  4. […] aquí You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, […]

    • Hatim Says:

      I have one trigger which is after insert or update of some field values on one table.
      However we get an exemption that could not find program unit being called from the trigger itself.
      Question is that all dependencies of the objects are valid and this is intermittent issue occuring.
      For some calls it goes through even for the same data invoked twice thrice it gets success if it gives an error at first call.
      Kindly suggest. Your help would be appreciated

  5. Profeseor Says:

    I get this error when I called standard oracle API procedure.
    When I looked, the API is there.
    So how I solve this case? It confused me😦


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s