Inside an Oracle 12 “package”, how do I make a variable or function or procedure accessible or not accessible?

Question

I'm a former Java developer, using Oracle's SQL-Developer to create Oracle "packages."

Oracle's websites indicate that it's possible to create an Oracle "package" in which some objects (variables, functions, procedures) are accessible "outside" the scope of the package, while others are only accessible "inside" the package.

I.e., I'm trying to do something like this (pseudocode!), which intentionally but superficially resembles Java.

So, I'm asking, "How does one implement functionality that is similar to (java's) PUBLIC and PRIVATE in an Oracle PL SQL package"? ("See 'Oracle keyword' is enough to point me in the right direction.)

Thanks in advance!

CREATE PACKAGE a 
// header
**PUBLIC** NUMBER nVisibleOutside := 1;
**PRIVATE** NUMBER nNOTVisibleOutside := 14922016;

PUBLIC PROCEDURE pVisibleOutside ();
PUBLIC FUNCTION fNOTVisibleOutside();

/* other stuff */

// body

/* actual code of pVisibleOutside and fNOTVisibleOutside(); */


END a;

Anything in the specification will be available to external callers. Anything included in the body, but not defined in the specification will only be internally callable. Bearing in mind that the two are created by different statements. See the documentation for details on each CREATE statement. — Chris Hep
Yes I've successfully created a couple of packages, I just don't (didn't !) know how the visible/invisible feature worked. Thanks again. — posaunist0205

Amy Grange Amy Grange · Accepted Answer · 2016-09-13T14:23:15

Oracle uses a package specification and package body. Effectively, the specification defines all of the procedures/functions/variables/etc that you want to be accessible by other areas of code (for example, another package). The body then contains definitions of other procedures and functions that are "private" and also the logic behind the definitions in your package specification.

The example that you have provided above would go as follows:

CREATE OR REPLACE PACKAGE XX_EXAMPLE_PACKAGE AS

  -- Variable that is visible to any code
  n_visible_outside NUMBER := 1;

  -- Procedure that is visible to any code
  PROCEDURE p_visible_outside ( );


END XX_PACKAGE_SPECIFICATION;
/

CREATE OR REPLACE PACKAGE BODY XX_EXAMPLE_PACKAGE AS

    -- A variable that is visible to the whole package body but not
    -- to external code
    v_dummy VARCHAR2(100);

  -- Procedure visible to any code through the specification
  PROCEDURE p_visible_outside AS

    -- declaring a variable that is only visible to this procedure
    n_not_visible_outside NUMBER := 14922016;

  BEGIN

    -- Logic   

  END p_visible_outside;

  -- Function not visible outside as not declared in specification
  -- can be accessed by code within this package
  FUNCTION f_not_visible_outside RETURN BOOLEAN IS

    --Logic

  END f_not_visible_outside;

END;
/

You could then call the "visible" procedure in this way from another package:

XX_EXAMPLE_PACKAGE.p_visible_outside;

And, likewise, you can call the "visible" global variable in this way from another package:

-- You could use this in your code as say, a parameter
XX_EXAMPLE_PACKAGE.n_visible_outside;

-- You could assign the value to another variable in your code
v_dummy := XX_EXAMPLE_PACKAGE.n_visible_outside;

As a beginner it isn't as important to follow development standards, however, in the future you should. Hopefully, you have some local development standards however, you could probably find some Oracle PL/SQL standards somewhere on google.

Inside an Oracle 12 “package”, how do I make a variable or function or procedure accessible or not accessible?

1 Answers