james_weasel james_weasel - 6 months ago 24
SQL Question

ORACLE SQL Developer putting package into another package

Wanting to know if someone could explain to me how I can put a previously created package that contains a function I need into a new package I just created within oracle sql developer. Basically I need a function from another package that I have already created and I wanna put it into new package.

Answer

Assuming that you want to call a function defined in a package from within some other package, you only need to define it public, declaring it in the header of the package:

SQL> create or replace package pack1 is
  2      function publicFn( n number) return number;
  3  end pack1;
  4  /

Package created.

SQL> create or replace package body pack1 as
  2      --
  3      function privateFn ( n number) return number is
  4      begin
  5          return n * 10;
  6      end;
  7      --
  8      function publicFn( n number) return number is
  9      begin
 10          return privateFn(n);
 11      end;
 12
 13  end pack1;
 14  /

Package body created.

SQL>
SQL> create or replace package pack2 is
  2      procedure Proc ;
  3  end pack2;
  4  /

Package created.

SQL> create or replace package body pack2 as
  2      procedure Proc is
  3      begin
  4          for i in 1 .. 5 loop
  5              dbms_output.put_line('pack1.publicFn(' || i || ')= ' || pack1.publicFn(i));
  6          end loop;
  7      end;
  8  end pack2;
  9  /

Package body created.

You can use the public procedure, and it can use the public function defined in pack1:

SQL> begin
  2      pack2.proc;
  3  end;
  4  /
pack1.publicFn(1)= 10
pack1.publicFn(2)= 20
pack1.publicFn(3)= 30
pack1.publicFn(4)= 40
pack1.publicFn(5)= 50

PL/SQL procedure successfully completed.

You can directly use the public function:

SQL> select pack1.publicFn(1) from dual;

PACK1.PUBLICFN(1)
-----------------
               10

Of course, you can not use the private function:

SQL> select pack1.privateFn(1) from dual;
select pack1.privateFn(1) from dual
       *
ERROR at line 1:
ORA-00904: "PACK1"."PRIVATEFN": invalid identifier