How to create an Oracle package with private procedure?

Question

I'm creating an Oracle package and I wonder if it's possible to make a prcedure private, here is my sample code:

CREATE OR REPLACE PACKAGE MYSCHEMA.MyPackage AS
 PROCEDURE MyProcedureA(outputParam OUT VARCHAR2);
 PROCEDURE MyProcedureB(inputParam IN VARCHAR2);
END MyPackage;

/

CREATE OR REPLACE PACKAGE BODY MYSCHEMA.MyPackage AS

 PROCEDURE MyProcedureA(outputParam OUT VARCHAR2) AS
  myHello VARCHAR2(1000) := 'Hello';
 BEGIN
  MyProcedureB(myHello);
  outputParam := 'OK';
 END MyProcedureA;

 PROCEDURE MyProcedureB(inputParam IN VARCHAR2) AS
  myWorld VARCHAR2(1000) := 'World';
 BEGIN
  dbms_output.put_line(inputParam || myWorld);
 END MyProcedureB;

END MyPackage;

is it possible to make MyProcedureB 'private', I means it can be only called by the other procedures within the package (MyProcedureA)?

score 5 · Accepted Answer · answered Apr 10 '21 at 00:57

Sure. Anything not declared in the package spec is private.

Once you remove MyProcedureB from the package spec, however, you it to be declared before it is called in MyProcedureA. I generally prefer to do this by putting the implementation of MyProcedureB before the implementation of MyProcedureA in the package body.

CREATE OR REPLACE PACKAGE MYSCHEMA.MyPackage AS
 PROCEDURE MyProcedureA(outputParam OUT VARCHAR2);
END MyPackage;

/

CREATE OR REPLACE PACKAGE BODY MYSCHEMA.MyPackage AS

 PROCEDURE MyProcedureB(inputParam IN VARCHAR2) AS
  myWorld VARCHAR2(1000) := 'World';
 BEGIN
  dbms_output.put_line(inputParam || myWorld);
 END MyProcedureB;

 PROCEDURE MyProcedureA(outputParam OUT VARCHAR2) AS
  myHello VARCHAR2(1000) := 'Hello';
 BEGIN
  MyProcedureB(myHello);
  outputParam := 'OK';
 END MyProcedureA;

END MyPackage;

You could also leave the order as you have it and just declare the spec for the procedure at the top of the package body

CREATE OR REPLACE PACKAGE MYSCHEMA.MyPackage AS
 PROCEDURE MyProcedureA(outputParam OUT VARCHAR2);
END MyPackage;

/

CREATE OR REPLACE PACKAGE BODY MYSCHEMA.MyPackage AS
 PROCEDURE MyProcedureB(inputParam IN VARCHAR2);

 PROCEDURE MyProcedureA(outputParam OUT VARCHAR2) AS
  myHello VARCHAR2(1000) := 'Hello';
 BEGIN
  MyProcedureB(myHello);
  outputParam := 'OK';
 END MyProcedureA;

 PROCEDURE MyProcedureB(inputParam IN VARCHAR2) AS
  myWorld VARCHAR2(1000) := 'World';
 BEGIN
  dbms_output.put_line(inputParam || myWorld);
 END MyProcedureB;

END MyPackage;

How to create an Oracle package with private procedure?

1 Answers1