What does it means when there are two functions with the same name but different parameter in the package body?

Question

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;

Answer 1

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.