Scoping of Functions in PL/SQL Packages

There was this recent
discussion on comp.databases.oracle.misc about scoping of functions in PL/SQL.
I have set up a test script that you can send through SQL Plus. The script is here:

spool C:\Temp\pkg-test.txt
set echo on pagesize 100 linesize 150 serverout on
create package call_test
as
function f_pub(x in number) return number;
procedure p_pub_direct;
procedure p_pub_direct_qualified;
procedure p_pub_immediate;
procedure p_pub_immediate_qualified;
procedure p_priv_direct;
procedure p_priv_immediate;
procedure p_priv_immediate_qualified;
procedure p_priv_var_direct;
procedure p_priv_var_immediate;
end call_test;
/
show errors package call_test
create package body call_test
as

function f_pub(x in number) return number
as
begin
  return x * 10;
end f_pub;

function f_priv(x in number) return number
as
begin
  return x * 20;
end f_priv;

procedure p_pub_direct
as
begin
  dbms_output.put_line('direct call of public function');
  for c in ( select f_pub(2) as num from dual ) loop
    dbms_output.put_line('value ' || c.num);
  end loop;
end p_pub_direct;

procedure p_pub_direct_qualified
as
begin
  dbms_output.put_line('direct call of public function using qualified name');
  for c in ( select call_test.f_pub(2) as num from dual ) loop
    dbms_output.put_line('value ' || c.num);
  end loop;
end p_pub_direct_qualified;

procedure p_pub_immediate
as
  tmp number;
begin
  dbms_output.put_line('execute immediate with public function');
  execute immediate 'select f_pub(2) as num from dual' into tmp;
  dbms_output.put_line('value ' || tmp);
end p_pub_immediate;

procedure p_pub_immediate_qualified
as
  tmp number;
begin
  dbms_output.put_line('execute immediate with public function using qualified name');
  execute immediate 'select call_test.f_pub(2) as num from dual' into tmp;
  dbms_output.put_line('value ' || tmp);
end p_pub_immediate_qualified;

procedure p_priv_direct
as
begin
  dbms_output.put_line('direct call of private function');
  dbms_output.put_line('this will not compile');
  /*
  for c in ( select f_priv(2) as num from dual ) loop
    dbms_output.put_line('value ' || c.num);
  end loop;
  for c in ( select call_test.f_priv(2) as num from dual ) loop
    dbms_output.put_line('value ' || c.num);
  end loop;
  */
end p_priv_direct;

procedure p_priv_immediate
as
  tmp number;
begin
  dbms_output.put_line('execute immediate with private function');
  execute immediate 'select f_priv(2) as num from dual' into tmp;
  dbms_output.put_line('value ' || tmp);
end p_priv_immediate;

procedure p_priv_immediate_qualified
as
  tmp number;
begin
  dbms_output.put_line('execute immediate with private function using qualified name');
  execute immediate 'select call_test.f_priv(2) as num from dual' into tmp;
  dbms_output.put_line('value ' || tmp);
end p_priv_immediate_qualified;

procedure p_priv_var_direct
as
  foo number := 123;
begin
  dbms_output.put_line('direct call with private variable');
  for c in ( select foo as num from dual ) loop
    dbms_output.put_line('value ' || c.num);
  end loop;
end p_priv_var_direct;

procedure p_priv_var_immediate
as
  tmp number;
  foo number := 123;
begin
  dbms_output.put_line('execute immediate with private variable');
  execute immediate 'select foo as num from dual' into tmp;
  dbms_output.put_line('value ' || tmp);
end p_priv_var_immediate;

end call_test;
/
show errors package body call_test
set echo off
show release
exec call_test.p_pub_direct;
exec call_test.p_pub_direct_qualified;
exec call_test.p_pub_immediate;
exec call_test.p_pub_immediate_qualified;
exec call_test.p_priv_direct;
exec call_test.p_priv_immediate;
exec call_test.p_priv_immediate_qualified;
exec call_test.p_priv_var_direct;
exec call_test.p_priv_var_immediate;
drop package call_test
/
spool off

And here's how output looks from a 10.2.0.4 on Linux

release 1002000400
direct call of public function
value 20

PL/SQL procedure successfully completed.

direct call of public function using qualified name
value 20

PL/SQL procedure successfully completed.

execute immediate with public function
BEGIN call_test.p_pub_immediate; END;

*
ERROR at line 1:
ORA-00904: "F_PUB": invalid identifier
ORA-06512: at "RK.CALL_TEST", line 39
ORA-06512: at line 1


execute immediate with public function using qualified name
value 20

PL/SQL procedure successfully completed.

direct call of private function
this will not compile

PL/SQL procedure successfully completed.

execute immediate with private function
BEGIN call_test.p_priv_immediate; END;

*
ERROR at line 1:
ORA-00904: "F_PRIV": invalid identifier
ORA-06512: at "RK.CALL_TEST", line 72
ORA-06512: at line 1


execute immediate with private function using qualified name
BEGIN call_test.p_priv_immediate_qualified; END;

*
ERROR at line 1:
ORA-00904: "CALL_TEST"."F_PRIV": invalid identifier
ORA-06512: at "RK.CALL_TEST", line 81
ORA-06512: at line 1


direct call with private variable
value 123

PL/SQL procedure successfully completed.

execute immediate with private variable
BEGIN call_test.p_priv_var_immediate; END;

*
ERROR at line 1:
ORA-00904: "FOO": invalid identifier
ORA-06512: at "RK.CALL_TEST", line 101
ORA-06512: at line 1

I do not find these results surprising at all:

• You are never allowed to use a package private function in a SELECT because the SQL engine does not see package private items.


• When using EXECUTE IMMEDIATE this is like a call to a global function which cannot know anything about the package context, hence you need to use a qualified name.