In the package spec, the function name only appears once therefore it's not overloaded. In the package body, the same function name appears twice using different set of parameters. One of them has the same parameter as the one in the package spec. My question is, is the "first func" in the package body actually being invoked and if so, what exactly is it doing? It's trying to return itself.
--package specification
CREATE OR REPLACE
PACKAGE jtestpkg
IS
FUNCTION testfunc(p_num IN NUMBER, p_out1 IN out varchar2, p_out2 IN out varchar2)
RETURN NUMBER;
END jtestpkg;
--package body
CREATE OR REPLACE
PACKAGE BODY jtestpkg
IS
--first func
function testfunc
(p_num IN NUMBER,
p_out1 IN OUT varchar2)
return number is
v_out2 varchar2(50);
BEGIN
dbms_output.put_line('invoking first func');
RETURN testfunc(
p_num,
p_out1,
v_out2);
END;
--second func
FUNCTION testfunc(
p_num IN NUMBER,
p_out1 IN OUT varchar2,
p_out2 IN OUT varchar2)
RETURN NUMBER
IS
v_num number;
BEGIN
IF 1=p_num THEN
p_out1:='FirstOUT_1';
p_out2:='SecondOUT_1';
dbms_output.put_line(v_num||p_out1||p_out2);
RETURN 1;
elsif 2=p_num THEN
p_out1:='FirstOUT_2';
p_out2:='SecondOUT_2';
dbms_output.put_line(v_num||p_out1||p_out2);
RETURN 2;
ELSE
p_out1:='FirstOUT_3';
p_out2:='SecondOUT_3';
dbms_output.put_line(v_num||p_out1||p_out2);
return 3;
END IF;
------
p_out1:='FirstOUT_0';
p_out2:='SecondOUT_0';
dbms_output.put_line(v_num||p_out1||p_out2);
RETURN 0;
END testfunc;
END jtestpkg;
A function declared in the specification is public and can be invoked from outside the package. A function which is defined in the body but not declared in the specification is private and can only be called from within that package.
In your example the second overloaded version of your function in the package body, which you've labelled 'second func', matches the declaration in the specification, so that is the one that is involved when you call the function from elsewhere:
declare
rc number;
in_out_1 varchar2(20) := 'A';
in_out_2 varchar2(20) := 'B';
begin
rc := jtestpkg.testfunc(42, in_out_1, in_out_2);
end;
/
FirstOUT_3SecondOUT_3
PL/SQL procedure successfully completed.
The first overloaded function in your body, which you've labelled 'first func', does not have a matching declaration in the specification, so you can't call it externally:
declare
rc number;
in_out_1 varchar2(20) := 'A';
begin
rc := jtestpkg.testfunc(42, in_out_1);
end;
/
ORA-06550: line 5, column 9:
PLS-00306: wrong number or types of arguments in call to 'TESTFUNC'
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
My question is, is the "first func" in the package body actually being invoked
No. In your code the 'first func' is not ever being invoked.
It's trying to return itself.
No it isn't. Your 'first func' would call 'second func' if it was itself called from somewhere else inside the package, but you aren't currently doing that.
The debug in there says 'invoking first func' but that isn't true, it's invoking second func, since the call it makes has three arguments - matching the 'second func' argument list. (That happens to be public, but it wouldn't matter if it wasn't, since it's internal to the package anyway).
Just as an example, you could call the private function as part of the package instantiation and initialization:
...
END testfunc;
-- initialization, called on instantiation (for each session)
BEGIN
dbms_output.put_line('Initialization start');
declare
rc number;
in_out_1 varchar2(20) := 'A';
begin
dbms_output.put_line('Initialization: calling first func');
rc := testfunc(1, in_out_1);
end;
dbms_output.put_line('Initialization end');
END jtestpkg;
/
then calling anything public in the package the first time in a session instantiates it, which initializes it, which runs that package-level block. So with the same anonymous block:
declare
rc number;
in_out_1 varchar2(20) := 'A';
in_out_2 varchar2(20) := 'B';
begin
rc := jtestpkg.testfunc(42, in_out_1, in_out_2);
end;
/
you see (first time in the session only):
Initialization start
Initialization: calling first func
invoking first func
FirstOUT_1SecondOUT_1
Initialization end
FirstOUT_3SecondOUT_3
PL/SQL procedure successfully completed.
You still see the same FirstOUT_3SecondOUT_3 output as before, from the value 42 passed in that call; but before that you see the output FirstOUT_1SecondOUT_1 from 'first func' calling 'second func' with value 1 as part of that initialization process.
It is allowed for a function to call itself, i.e. recursively, but it would need to change the call each time or it would get stuck in an infinite loop, and eventually be killed. You aren't doing that either here though.