ORA-06508 after compiling package body in program window

[guenther]

Hi,

I've noticed that their are differents compiling package body in program window or in command window.
Under specific conditions this result in ORA-06508 after compiling body in program window.

Here is a short scenario: let the script below run in command window.
The script will create 2 sinple packages eache with one function.
Package A functions should not call directly by users but by package B functions.
Package B is the user interface and call package A functions.

After runnning script in command window please copy the below 'create or replace package body A'- statement in a new blank program window and execute.
Then let execute the funtioncall B.f02 again in command window. This will result in ORA-06508 and so return -6508!
Strange to say that this will only be happend, when a constant or variable is defined in package body A.

Is their any explanation for that?

Thanks
frank

I use pl/sql dev 6.0.5 and oracle 8.1.7

/*
| create package A with function f01
| This function will be called only by function B.f02
| (or in test for having a pint of origin)
*/
create or replace package A
as
function f01 return pls_integer;
end A;
/

create or replace package body A
is
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
pbcsVersion constant varchar2(10) := '0.0.2';
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

function f01 return pls_integer
is
begin
return (0);
exception
when others then
return sqlcode;
end f01;
end A;
/

/*
| create package B with function f02 that will call B.f01
| This function will be called by users
*/
create or replace package B
as
function f02 return pls_integer;
end B;
/

create or replace package body B
as
function f02 return pls_integer
is
fnReturn pls_integer;
begin
fnReturn := A.f01;
return (fnReturn);
exception
when others then
return sqlcode;
end f02;
end B;
/

/*
| call function A.f01 for pint of origin
*/
declare
result pls_integer;
begin
-- Call the function
result := A.f01;
end;
/

/*
| run function B.f02
| all is ok
*/
declare
result pls_integer;
begin
-- Call the function
result := B.f02;
DBMS_OUTPUT.put_line('result = '||to_char(result));
end;
/

/*
| recompile package body A and run function A.f01.
| then call B.f02 will result as expected. all fine.
*/
create or replace package body A
is
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
pbcsVersion constant varchar2(10) := '0.0.2';
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

function f01 return pls_integer
is
begin
return (0);
exception
when others then
return sqlcode;
end f01;
end A;
/

declare
result pls_integer;
begin
-- Call the function
result := A.f01;
end;
/

declare
result pls_integer;
begin
-- Call the function
result := B.f02;
DBMS_OUTPUT.put_line('result = '||to_char(result));
end;
/

/*
| recompile package body A but do not run function A.f01
| then call B.f02 will result in ORA-06508 if you do that in program window.
| In command window everything is ok!
|
| Please copy the below 'create or replace package body'- statement in a
| new blank program window and execute.
| Then let execute the funtioncall B.f02 (see below) again in command window.
| This will result in ORA-06508 and so return -6508
|
*/
create or replace package body A
is
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
pbcsVersion constant varchar2(10) := '0.0.2';
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

function f01 return pls_integer
is
begin
return (0);
exception
when others then
return sqlcode;
end f01;
end A;
/

declare
result pls_integer;
begin
-- Call the function
result := B.f02;
DBMS_OUTPUT.put_line('result = '||to_char(result));
end;
/

 

[Marco Kalter]

The difference between the Program Window and Command Window lies in the debugger preference "Add debug information when compiling". If enabled, the session of the Program Window has been altered as follows:

alter session set plsql_debug=true

This is never the case for the Command Window.

 

[guenther]

Hi Marco,

I've switched preference "Add debug information when compiling" to disable and restart plsd
but without any changes in the result.
In which way influences the existence of package body constant/variable compilation of package in dependence of session is in plsql_debug mode or not?

Any suggestions?
frank