Solved

PLSQL Question

Posted on 2013-06-30
62
826 Views
Last Modified: 2013-07-17
Hi,

Requirement is I am getting set of all items from one instance to another. Then I am calling a procedure which calls API to update container item flag. when I rum this program flag is being updated but in log i see a wierd error which says :
INV_ITEM_GRP.IOI_Process: Unexpexted error: ORA-06508: PL/SQL: could not find program unit being called
x_return_status=U
x_msg_count=1
x_msg_data =

Any help would be appreciated. Thanks.
0
Comment
Question by:pp89
  • 28
  • 26
  • 8
62 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39288988
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/
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39288992
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
0
 

Author Comment

by:pp89
ID: 39288996
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.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39289001
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 ='<<>>'
0
 

Author Comment

by:pp89
ID: 39289008
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289013
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289022
>>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
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39289023
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 ='<<>>'
0
 

Author Comment

by:pp89
ID: 39289030
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?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289042
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289051
>>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.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39289052
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 ?
0
 

Author Comment

by:pp89
ID: 39289053
I tried running only API outside the program and surprisingly I get the same error for API also...
0
 

Author Comment

by:pp89
ID: 39289057
"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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289059
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
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39289062
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 ?
0
 

Author Comment

by:pp89
ID: 39289082
Also I observed that I opened a new session and ran the API only again it updates successfully.
0
 

Author Comment

by:pp89
ID: 39289084
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.
0
 

Author Comment

by:pp89
ID: 39289085
select * from SYS.USER_ERRORS
where NAME = <object_name>
and type = <object_type>

No rows returned!
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39289091
try this, look at the status column they should all be valid, but you must provide the correct owner and starts with
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='????' -- owner name here
           START WITH NAME = '???' -- starts with name here
        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

Open in new window

0
 

Author Comment

by:pp89
ID: 39289100
Query is still running.. Will update you!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289111
still running?
yikes

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

Author Comment

by:pp89
ID: 39289112
Query is running and its like more than 13 minutes so terminated it. What do you want me to do? Allow it to run?
0
 

Author Comment

by:pp89
ID: 39289114
select object_name, object_type, status from user_objects where object_name = 'pkg_name'

I see status is valid.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289115
you most likely only want the problem package name as the 'starts with'
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39289117
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.
0
 

Author Comment

by:pp89
ID: 39289121
How do I review dependencies?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289124
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?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289125
>>How do I review dependencies?
dba_dependencies

these answers now appear to be recursive
0
 

Author Comment

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

Status is Valid.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289167
each and every object define in that package needs to be evaluated - not just the package itself...
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:pp89
ID: 39289191
When I check for procedures defined in my package it does not return any rows
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289198
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

Open in new window

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39289201
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289210
>>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
0
 

Author Comment

by:pp89
ID: 39289214
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289217
are you actually changing the names?

<<THE package name here >>
0
 

Author Comment

by:pp89
ID: 39289223
I am just giving the package name there
0
 

Author Comment

by:pp89
ID: 39289245
Also, I see records inserted in mtl_system_items_interface but no records in mtl_interface_errors are inserted.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289256
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.
0
 

Author Comment

by:pp89
ID: 39289258
OPAS_CRE_ITEMS
0
 

Author Comment

by:pp89
ID: 39289300
If the time stamp of package and concurrent program differs will that cause this issue?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289302
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

Open 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.
0
 

Author Comment

by:pp89
ID: 39289321
The records lying in mtl_system_items_interface with process_flag as 1...
0
 

Author Comment

by:pp89
ID: 39289322
Query you have given is taking longer than expected...
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289324
kill it
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289325
I truly don't know how to help further - sorry.
0
 

Author Comment

by:pp89
ID: 39289328
ok. Thanks Anyways for your time.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289335
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.
0
 

Author Comment

by:pp89
ID: 39289342
100 items are invalid
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39289493
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,
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289500
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:"
0
 

Author Comment

by:pp89
ID: 39289504
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?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289508
yes
0
 

Author Comment

by:pp89
ID: 39289513
In all_objects status is valid for this package.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289521
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
0
 

Author Comment

by:pp89
ID: 39290587
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...
0
 

Author Comment

by:pp89
ID: 39290601
what do you think patches should be applied or is it some other issue?

All these are in valid status... also tried recompiling them..
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39292058
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 :)
0
 

Author Comment

by:pp89
ID: 39292060
These are oracle standard PVT packages... Right now instance is down... will post ASAP..
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39292090
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
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39292223
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,
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Query criteria:  Count number of distinct months in date field 38 37
EXECUTE IMMEDIATE 5 36
SQL query 4 31
encyps queries mssql 15 27
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

744 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

13 Experts available now in Live!

Get 1:1 Help Now