Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06508: PL/SQL: could not find program unit being called: (oracle)
ORA-06508: PL/SQL: could not find program unit being called: [message #573700] Wed, 02 January 2013 08:14 Go to next message
partha.bhunia
Messages: 6
Registered: January 2013
Junior Member
I am facing below Oracle error when ever I am changing the contents of the package within it.
%ORA-06508: PL/SQL: could not find program unit being called:

The packages and the dependents are recopmiled successfully and there are no invalid objects.
The problem is resolved only if the database is reastarted.

What might be the problem?

I am using a PL/SQL collection of record datatype in this package.

Re: ORA-06508: PL/SQL: could not find program unit being called: [message #573702 is a reply to message #573700] Wed, 02 January 2013 08:54 Go to previous messageGo to next message
BlackSwan
Messages: 23195
Registered: January 2009
Senior Member
>What might be the problem?
The code contains a bug.
This answered is as detailed as can be provided based upon posted details or lack thereof.

my car won't go.
tell me how to make my car go.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: ORA-06508: PL/SQL: could not find program unit being called: [message #573760 is a reply to message #573702] Thu, 03 January 2013 01:23 Go to previous messageGo to next message
partha.bhunia
Messages: 6
Registered: January 2013
Junior Member
I am executing the package procedure from a trigger.

like.

<package name>.<procedure name> and the error says it does not get the procedure name.

version details:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for HPUX: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
Re: ORA-06508: PL/SQL: could not find program unit being called: [message #573762 is a reply to message #573760] Thu, 03 January 2013 01:34 Go to previous messageGo to next message
Littlefoot
Messages: 19905
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

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.


Can you call that procedure from SQL*Plus? Do you own the package?
Re: ORA-06508: PL/SQL: could not find program unit being called: [message #573763 is a reply to message #573762] Thu, 03 January 2013 01:36 Go to previous messageGo to next message
partha.bhunia
Messages: 6
Registered: January 2013
Junior Member
Yes I can call that procedure from Toad and I have developed the package recently.
Re: ORA-06508: PL/SQL: could not find program unit being called: [message #573764 is a reply to message #573763] Thu, 03 January 2013 01:38 Go to previous messageGo to next message
Littlefoot
Messages: 19905
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You still didn't say who owns what.

Saying that you call it from a trigger: is it a database trigger, or a form trigger?
Re: ORA-06508: PL/SQL: could not find program unit being called: [message #573765 is a reply to message #573763] Thu, 03 January 2013 01:40 Go to previous messageGo to next message
partha.bhunia
Messages: 6
Registered: January 2013
Junior Member
If we restart the oracle database this error reapperas but new error comes as 'ORA-21700: object does not exist or is marked for delete:'

I am using a PL/SQL collection of record datatype in this package and declared in package spec . Can the handling of this datatype be a problem.

Re: ORA-06508: PL/SQL: could not find program unit being called: [message #573770 is a reply to message #573765] Thu, 03 January 2013 01:56 Go to previous messageGo to next message
Littlefoot
Messages: 19905
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

ORA-21700: object does not exist or is marked for delete

Cause: User attempted to perform an inappropriate operation to an object that is non-existent or marked for delete. Operations such as pinning, deleting and updating cannot be applied to an object that is non-existent or marked for delete.

Action: User needs to re-initialize the reference to reference an existent object or the user needs to unmark the object.


This is pointless. Discussion takes at least 2 persons, talking and listening to each other. Here, I have an impression that nobody listens to what I say. So, happy monologue to you!
Re: ORA-06508: PL/SQL: could not find program unit being called: [message #573773 is a reply to message #573764] Thu, 03 January 2013 02:04 Go to previous messageGo to next message
partha.bhunia
Messages: 6
Registered: January 2013
Junior Member
Sorry I have seen this reply before I post the last message

"You still didn't say who owns what.

Saying that you call it from a trigger: is it a database trigger, or a form trigger? Mad "

I am the owner of the package and This is a database trigger.

Re: ORA-06508: PL/SQL: could not find program unit being called: [message #573821 is a reply to message #573773] Thu, 03 January 2013 06:28 Go to previous messageGo to next message
cookiemonster
Messages: 11287
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you also the owner of the trigger?
Re: ORA-06508: PL/SQL: could not find program unit being called: [message #573827 is a reply to message #573821] Thu, 03 January 2013 07:14 Go to previous messageGo to next message
partha.bhunia
Messages: 6
Registered: January 2013
Junior Member
Yes I am the owner of the trigger.
Re: ORA-06508: PL/SQL: could not find program unit being called: [message #573829 is a reply to message #573827] Thu, 03 January 2013 07:38 Go to previous messageGo to next message
cookiemonster
Messages: 11287
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think you need to show us the code at this point.
Re: ORA-06508: PL/SQL: could not find program unit being called: [message #573838 is a reply to message #573773] Thu, 03 January 2013 08:30 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2091
Registered: January 2010
Senior Member
partha.bhunia wrote on Thu, 03 January 2013 03:04
I am the owner of the package and This is a database trigger.


I have a feeling you are not giviong us full error stack. Most likely full error stack starts with:

ORA-04068: existing state of packages has been discarded
ORA-04065: existing state of package body "SCOTT.XXX" has been invalidated
ORA-04065: not executed, altered or dropped package body "SCOTT.XXX"
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.XXX"
ORA-06512: at line 1


You have a package, e.g. pkg1:

create or replace
package pkg1
is
g_n number := 0;
procedure p1;
end;
/ 
create or replace
package body pkg1
is
procedure p1
is
begin
for rc in (select rownum rn from dual) loop
g_n := g_n + rc.rn;
end loop;
end;
end;
/


Now some session executes this package:

SQL> set serveroutput on
SQL> exec dbms_output.put_line('pkg1.g_n = "' || pkg1.g_n || '"');
pkg1.g_n = "0"

PL/SQL procedure successfully completed.

SQL> exec pkg1.p1;

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line('pkg1.g_n = "' || pkg1.g_n || '"');
pkg1.g_n = "1"

PL/SQL procedure successfully completed.

SQL> exec pkg1.p1;

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line('pkg1.g_n = "' || pkg1.g_n || '"');
pkg1.g_n = "2"

PL/SQL procedure successfully completed.

SQL>  


What happens here? Package was executed by this session for the first time. Therefore package was instantiated in session memory. Now every time this session references the package reference is resolved to package instance within this session (that's one of package advantages comparing to standalone procedures/functions - package code is loaded into session once only). That's why package global variable pkg1.g_n keeps increasing. Now some other session modified of simply recompiled package body:

SQL> alter package pkg1 compile body;

Package body altered.

SQL> 


Now first session needs to execute pkg1.p1 again. But package changed (recompile means possible change)since first session referenced it last time. Oracle senses that and throws exception:

SQL> exec pkg1.p1;
BEGIN pkg1.p1; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SCOTT.PKG1" has been invalidated
ORA-04065: not executed, altered or dropped package body "SCOTT.PKG1"
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.PKG1"
ORA-06512: at line 1


SQL> 


And while raising this exception Oracle will re-instantiate package, so next time first session references new instance of pkg1 and package global variable pkg1.g_n is again set to 0:

SQL> set serveroutput on
SQL> exec dbms_output.put_line('pkg1.g_n = "' || pkg1.g_n || '"');
pkg1.g_n = "0"

PL/SQL procedure successfully completed.

SQL> 


Now, since package is re-instantiated everyting is back to normal:

SQL> set serveroutput on
SQL> exec dbms_output.put_line('pkg1.g_n = "' || pkg1.g_n || '"');
pkg1.g_n = "0"

PL/SQL procedure successfully completed.

SQL> exec pkg1.p1;

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line('pkg1.g_n = "' || pkg1.g_n || '"');
pkg1.g_n = "1"

PL/SQL procedure successfully completed.

SQL> exec pkg1.p1;

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line('pkg1.g_n = "' || pkg1.g_n || '"');
pkg1.g_n = "2"

PL/SQL procedure successfully completed.

SQL> 


So, if my assumptions are correct, there is no need to restart database. What you need to do is to develop deployment process or run package changes off hours.

SY.
Re: ORA-06508: PL/SQL: could not find program unit being called: [message #573847 is a reply to message #573838] Thu, 03 January 2013 09:01 Go to previous message
BlackSwan
Messages: 23195
Registered: January 2009
Senior Member
recompile the trigger after the procedure has been modified.
Previous Topic: execute immediate vs ref cursor
Next Topic: Need equivalent Decode for this Case
Goto Forum:
  


Current Time: Mon Dec 29 10:43:40 CST 2014

Total time taken to generate the page: 0.13468 seconds