| ORA-06508: PL/SQL: could not find program unit being called: [message #573700] |
Wed, 02 January 2013 08:14  |
 |
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 #573838 is a reply to message #573773] |
Thu, 03 January 2013 08:30  |
Solomon Yakobson
Messages: 1402
Registered: January 2010
|
Senior Member |
|
|
partha.bhunia wrote on Thu, 03 January 2013 03:04I 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.
|
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
"