PLSQL Question

ORA-06508:      PL/SQL: could not find program unit being called
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.

http://ora-06508.ora-code.com/

note the "or have compiled with errors" in the above also

part of what you are doing either does not exist - or has not compiled. could be a stored proc, or function for example - we cannot tell you precisely what is not working from here

Hi,

Can you tell me what do you mean by incompatibly modified?
I actually migrated the code from one instance to another; it runs perfectly in one instance but in another instance I get this error.
Package spec and package body both have compiled without errors.

This "INV_ITEM_GRP.IOI_Process:" packaged proc/function could be calling other procs/functions which may have compiled with errors or is invalidated.

You need to open the source code of This "INV_ITEM_GRP.IOI_Process" and review what all other modules it is referring to and then verify whether they are in good and valid state.

you may also use dba_dependencies dict view to understand what all other objects are required for this INV_ITEM_GRP.IOI_Process and then start checking from there.

select * from dba_dependencies
where type = '<<>>'
and name ='<<>>'

Can you please elaborate on "INV_ITEM_GRP.IOI_Process:"

I have checked the dependencies everything looks good. I have compiled both spec and body without errors it is compiled.

in that package body there may be multiple individual items (e.g. functions), check each of those

as I said earlier - we are unable to tell you exactly where the problem lies from here

>>Can you tell me what do you mean by incompatibly modified?
that term isn't mine it's the Oracle error message text
My guess is that term means "a modification that won't compile"

tip: take any ora error "ORA-06508", add "ora-code.com", paste combined string into browser

did you check this....

You need to open the source code of This "INV_ITEM_GRP.IOI_Process" and review what all other modules it is referring to and then verify whether they are in good and valid state.

you may also use dba_dependencies dict view to understand what all other objects are required for this INV_ITEM_GRP.IOI_Process and then start checking from there.

select * from dba_dependencies
where type = '<<>>'
and name ='<<>>'

I am hard coding in my program for certain items to perform the testing. So can this be possible that in the instance these changes are not getting compiled and hence because of incompatibly modified as said I am getting this error?

you might try a recursive query on db_dependencies

SELECT NAME,
       TYPE,
       REFERENCED_NAME,
       REFERENCED_TYPE
  FROM DBA_DEPENDENCIES
 WHERE OWNER='WHO' -- change to suit
 START WITH NAME='WHAT'  -- change to suit
CONNECT BY NOCYCLE
  PRIOR REFERENCED_NAME = NAME;

more scripts on dba_dependencies
http://www.dba-oracle.com/d_dba_dependencies.htm

>>I am hard coding in my program for certain items to perform the testing
quite possibly
without more detail I'd be reluctant to give a definitive yes - but it's certainly possible

if the same objects are working elsewhere without these issues, and if the environments are the same (version etc.), chances are it's a "change" that is the cause.

agreed. probably should be due to that "I am hard coding in my program for certain items to perform the testing" but can you elaborate more on what kind of changes are those ?

I tried running only API outside the program and surprisingly I get the same error for API also...

"agreed. probably should be due to that "I am hard coding in my program for certain items to perform the testing" but can you elaborate more on what kind of changes are those ? "

I actually migrated the code from one instance to another instance. In first instance it ran perfectly. After migrating I wanted to do a round of testing. So in my select I hard coded it to few items and compiled the program; it compiled without errors. But when I ran the concurrent program it updated the flag for those items but gave me this warning.

So is it because I did hard coding in this instance(may be set like no changes allowed) or it has nothing to do with this error.?

Also, If i forget about the code and run only the API; even then I am getting this error.

make sure each and every item does in fact exist
and, assuming they do, the compile status of each

as suggested by nav_kum_v, use dba_dependencies to help

not much more I can add really

maybe?
select * from SYS.USER_ERRORS
where NAME = <object_name>
and type = <object_type>

types:
PROCEDURE
FUNCTION
PACKAGE
PACKAGE BODY
TRIGGER

So in my select I hard coded it to few items  --> Do you mean you removed few items in your select queries in the packaged code which is used/referred to by your concurrent programs ?

Also I observed that I opened a new session and ran the API only again it updates successfully.

So in my select I hard coded it to few items  --> Do you mean you removed few items in your select queries in the packaged code which is used/referred to by your concurrent programs ?


Concurrent program just calls this package. There are no parameters. Select clause fetches all the items so I just added where item_number in('abc','def'). These are getting updated as required but I get that warning in my log messages.

select * from SYS.USER_ERRORS
where NAME = <object_name>
and type = <object_type>

No rows returned!

Query is still running.. Will update you!

still running?
yikes

should be v.quick - you may a massive recursion going on

Query is running and its like more than 13 minutes so terminated it. What do you want me to do? Allow it to run?

select object_name, object_type, status from user_objects where object_name = 'pkg_name'

I see status is valid.

you most likely only want the problem package name as the 'starts with'

ok..good. "Concurrent program just calls this package. There are no parameters. Select clause fetches all the items so I just added where item_number in('abc','def'). These are getting updated as required but I get that warning in my log messages. " --> should should not cause the behavior you are seeing.

I think the only way here now is review all dependencies related to this package and sort them out accordingly.

How do I review dependencies?

pp89

give us more facts perhaps? (we have almost none)
what is the user name?
what is the package name that contains the item that caused the error?

nb: result from this will not be definitive:
select object_name, object_type, status from user_objects where object_name = 'pkg_name'

as you need the dependent items within that package (which was the purpose of the recursion).

perhaps another way to tackle this:
select object_name, object_type, status from user_objects where status <> 'VALID'

and nb: as this is user_objects it must be run by the relevant user

i.e. are there any invalid items listed?

>>How do I review dependencies?
dba_dependencies

these answers now appear to be recursive

select * from dba_dependencies D,USER_OBJECTS U where owner='OWNER' and name='PACKAGE NAME' AND U.OBJECT_NAME=D.NAME

Status is Valid.

each and every object define in that package needs to be evaluated - not just the package itself...

When I check for procedures defined in my package it does not return any rows

let's try it without any recursion...

SELECT D.* , UO.STATUS
FROM USER_DEPENDENCIES D
INNER JOIN USER_OBJECTS UO 
      ON D.REFERENCED_NAME = UO.OBJECT_NAME AND D.REFERENCED_TYPE = UO.OBJECT_TYPE
WHERE D.NAME = '<<THE package name here >>'
AND UO.STATUS <>'VALID'
AND ROWNUM < 500 -- just in case

Select allOpen in new window

can you share the code of this "INV_ITEM_GRP.IOI_Process" ? if not a confidential one. you can put some dummy table names etc instead of orig table names before sharing it here.

>>When I check for procedures defined in my package it does not return any rows

point is, the error tells you something is missing (AND/OR not compiled)
if something missing how would I know? (only you can determine this)
if something is not compiled, then each member of the package needs to be inspected (not just the package) so you use dba_dependencies for this

I mentioned this some while back:
"we cannot tell you precisely what is not working from here"
>> only you can access the dbms

SELECT D.* , UO.STATUS
FROM USER_DEPENDENCIES D
INNER JOIN USER_OBJECTS UO
      ON D.REFERENCED_NAME = UO.OBJECT_NAME AND D.REFERENCED_TYPE = UO.OBJECT_TYPE
WHERE D.NAME = '<<THE package name here >>'
AND UO.STATUS <>'VALID'
AND ROWNUM < 500

It does not return anything

are you actually changing the names?

<<THE package name here >>

I am just giving the package name there

Also, I see records inserted in mtl_system_items_interface but no records in mtl_interface_errors are inserted.

what IS the package name? (surely that's not a secret)

if  the package name is known we could re-try the recursive approach (as the invalid item may be beyond the direct contents of the package).

I've offered all that I have to offer here.
if it's not something that is INVALID then it is MISSING I believe.

OPAS_CRE_ITEMS

If the time stamp of package and concurrent program differs will that cause this issue?

This should be quick (not minutes)

SELECT
  UO.OBJECT_NAME 
, UO.OBJECT_TYPE 
, UO.LAST_DDL_TIME
, UO.STATUS 
, E.TEXT
FROM (
        SELECT DISTINCT
              REFERENCED_NAME
            , REFERENCED_TYPE
        FROM DBA_DEPENDENCIES
        WHERE OWNER='NIKU'
          AND level < 11
           START WITH NAME = 'OPAS_CRE_ITEMS'
        CONNECT BY NOCYCLE
           PRIOR REFERENCED_NAME = NAME
     ) D
INNER JOIN USER_OBJECTS UO ON D.REFERENCED_NAME = UO.OBJECT_NAME AND D.REFERENCED_TYPE = UO.OBJECT_TYPE
LEFT JOIN SYS.USER_ERRORS E ON D.REFERENCED_NAME = E.NAME AND D.REFERENCED_TYPE = E.TYPE

Select allOpen in new window

If it produces nothing useful (i.e. all valid) then I suspect it is something missing (which cannot be detected by queries). If it is something missing you could compare these results with the other environment perhaps - i.e. comparing lists produced it 2 environments might help identify a missing dependency.

beyond this I'm afraid I really cannot think of anything else.

The records lying in mtl_system_items_interface with process_flag as 1...

Query you have given is taking longer than expected...

kill it

I truly don't know how to help further - sorry.

ok. Thanks Anyways for your time.

np. my regret is you still have the problem

If me, I would concentrate on possible missing items right now.

one very last suggestion:

SELECT
      STATUS
    , COUNT(*) AS COUNT_OF
FROM USER_OBJECTS
GROUP BY STATUS

if there are no invalid objects there is no point looking for them.

100 items are invalid

very good. so you have 100 invalid items there and if you think one of these 100 items is related/have dependency to your concurrent request or the application functionality to which you are referring to here in this question, then you need to get them to a valid state by recompling or resolving the errors if there are any before recompiling.

If you do not want to take this route or if i am in your situation, i would straight away open the package body code and start debugging it to understand which piece of code is giving the error "INV_ITEM_GRP.IOI_Process: Unexpexted error: ORA-06508: PL/SQL: could not find program unit being called". I will then be able to find which piece of code is causing the problem and fix it accordingly.

Thanks,

the problem might be amongst those invalid items. I cannot tell you if this is true or not. List the invalid items, consider if they relate to your problem.

Basically you have to perform something of an 'audit'. Why does it work in one env but not another. The working env has something different to the other. It relates in some way to "INV_ITEM_GRP.IOI_Process:"

In this INV_ITEM_GRP.IOI_Process in exception block that error is written so I added DBMS_UTILITY.FORMAT_ERROR_BACKTRACE.. So now I get the error ORA-06508: PL/SQL: could not find program unit being called: "abc.def".

So i should go and check abc.def?

yes

In all_objects status is valid for this package.

Action:      Check that all referenced programs, including their package bodies, exist and are compatible.

OR: perhaps it's a security problem?

nb: please don't expect me to be online - I may be able to answer immediately

Now I am getting these errors:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "APPS.INV_ITEM_GRP", line 1213

ORA-04065: not executed, altered or dropped stored procedure "APPS.INV_ITEM_PVT"

ORA-06508: PL/SQL: could not find program unit being called: "APPS.INV_ITEM_PVT".

All these are PVT packages...

what do you think patches should be applied or is it some other issue?

All these are in valid status... also tried recompiling them..

at this stage I suggest you post the relevant code. i.e.

APPS.INV_ITEM_GRP
APPS.INV_ITEM_PVT

we really cannot read minds, nor read off your screen :)

These are oracle standard PVT packages... Right now instance is down... will post ASAP..

oh - I don't recognize them.
 if these are std Oracle items, and not working, not sure what I can do.

I'm going to suggest you verify you have the correct package code - sounds to me like you may have mis-matched patches (or something like that). please double-check the patching that is applied.

e.g. here is a "similar" forum
https://forums.oracle.com/thread/2537508
look at the last 2 entries for example.

& this is definitely outside my comfort zone.
if you are not getting any attention from others in this question please open a new question

I suggest you to contact oracle apps support with all the infromation/logs as i see that you have APPS.INV_ITEM_PVT and possibly more in the invalid status etc which may in turn have dependencies to other packages etc which may required recompliation or some oracle app inv/bom/mfg patches to be applied to your instance.

It may not be possible for someone to debug those large inventory/bom/mfg packages here to nail down the root cause without a test instance :)

Thanks,