CREATE PACKAGE

The CREATE PACKAGE statement creates the specification for a standalone package, which is an encapsulated collection of related procedures, functions, and other program objects stored together in your database. The package specification declares these objects. The package body defines these objects.

Required Privilege

CREATE PROCEDURE (if owner) or CREATE ANY PROCEDURE (if not owner).

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL Syntax

CREATE [OR REPLACE] PACKAGE [Owner.]PackageName
      [InvokerRightsClause] [AccessibleByClause]
      {IS|AS}
      PlsqlPackageSpec

InvokerRightsClause::=
AUTHID {CURRENT_USER | DEFINER}

AccessibleByClause::=
ACCESSIBLE BY (accessor[,...])

accessor::= 
[UnitKind][Owner.]UnitName

You can specify InvokerRightsClause or AccessibleByClause in any order.

Parameters

Parameter Description

OR REPLACE

Specify OR REPLACE to recreate the package specification if it already exists. Use this clause to change the specification of an existing package without dropping and recreating the package. When you change a package specification, TimesTen recompiles it.

PackageName

Name of the package.

InvokerRightsClause

Lets you specify whether the SQL statements in PL/SQL functions or procedures execute with definer's or invoker's rights. The AUTHID setting affects the name resolution and privilege checking of SQL statements that a PL/SQL procedure or function issues at runtime, as follows:

  • Specify DEFINER so that SQL name resolution and privilege checking operate as though the owner of the procedure or function (the definer, in whose schema it resides) is running it. DEFINER is the default.

  • Specify CURRENT_USER so that SQL name resolution and privilege checking operate as though the current user (the invoker) is running it.

For more information, see Definer's Rights and Invoker's Rights (AUTHID Clause) in the Oracle TimesTen In-Memory Database Security Guide.

AccessibleByClause

Use this clause to specify one or more accessors (PL/SQL units) that can invoke the package directly. The list of accessors that can access the package is called a white list. A white list gives you the ability to add an extra layer of security to your PL/SQL objects. Specifically, you can restrict access to the package to only those objects on the white list.

AccessibleByClause can appear only once in the CREATE PACKAGE statement.

Syntax: ACCESSIBLE BY (accessor [,...])

accessor

Used in AccessibleByClause. An accessor is a PL/SQL unit that can invoke the package.

An accessor can appear more than once in the AccessibleByClause clause.

Syntax: [UnitKind][Owner.]UnitName

UnitKind

Used in the accessor clause (which is part of the AccessibleByClause clause). Specifies the kind of PL/SQL unit that can invoke the package.

  • UnitKind is optional, but if specified, valid options are:

  • FUNCTION

  • PROCEDURE

  • PACKAGE

[Owner.]UnitName

Used in the accessor clause (which is part of the AccessibleByClause clause). Specifies the name of the PL/SQL unit that can invoke the package. If you specify UnitKind, then UnitName must be a name of a unit of that kind. For example, if you specify PROCEDURE for UnitKind, then UnitName must be the name of a procedure. UnitName is required.

You can optionally specify Owner. If you specify Owner, then UnitName must reside in that owner's schema. If you do not specify Owner, UnitName must be in the schema that contains the package.

IS|AS

Specify either IS or AS to declare the body of the function.

PlsqlPackageSpec

Specifies the package specification. Can include type definitions, cursor declarations, variable declarations, constant declarations, exception declarations and PL/SQL subprogram declarations.

Description

  • AccessibleByClause:

    • AccessibleByClause is valid at the top-level package definition. You cannot specify AccessibleByClause in the individual procedures or functions within the package. In addition, you cannot specify AccessibleByClause in the CREATE PACKAGE BODY statement.

    • You can use this clause to restrict access to helper packages. For example, assume your PL/SQL package defines an API for a given functionality and that functionality is implemented using a set of helper procedures and functions. You want to limit applications to only be able to call the API procedure or function that is defined in your package, and to not be able to call the helper procedures and functions directly. You can use the ACCESSIBLE BY clause to achieve this.

    • The compiler checks the validity of the syntax of the ACCESSIBLE BY clause, but does not check that the accessor exists. Therefore, you can define an accessor that does yet exist in the owner's schema.

    • When you invoke the package, the compiler first does the normal permission checks on the invocation. If any check fails, the invocation fails, even if the invoker is an accessor. If all normal permission checks on the invocation succeed, and the package has no ACCESSIBLE BY clause, the invocation succeeds. If the package has an ACCESSIBLE BY clause, the invocation succeeds only if the invoker is an accessor.

  • When you create or replace a package, the privileges granted on the package remain the same. If you drop and recreate the object, the object privileges that were granted on the original object are revoked.

  • In a replicated environment, the CREATE PACKAGE statement is not replicated. For more information, see Creating a New PL/SQL Object in an Existing Active Standby Pair and Adding a PL/SQL Object to an Existing Classic Replication Scheme in the Oracle TimesTen In-Memory Database Replication Guide.

Examples

Illustrating the Correct Usage of the Accessible By Clause

This example illustrates the correct usage of the AccessibleByClause. The clause is specified at the top-level of the CREATE PACKAGE statement. Note that the CallingProc procedure does not need to exist. 

Command> CREATE OR REPLACE PACKAGE ProtectedPkg
           ACCESSIBLE BY (PROCEDURE CallingProc)
         AS
           PROCEDURE ProtectedProc;
         END;
         /
 
Package created.

Illustrating the Incorrect Usage of the Accessible By Clause

These examples show the incorrect use of the AccessibleByClause. The first example attempts to use AccessibleByClause in the packaged procedure, resulting in a compilation error. The second example attempts to use AccessibleByClause in the CREATE PACKAGE BODY statement, resulting in a compilation error.

This example uses the ACCESSIBLE BY clause in the packaged procedure. 

Command> CREATE OR REPLACE PACKAGE ProtectedPkg1
         AS
           PROCEDURE ProtectedProc1 
           ACCESSIBLE BY (PROCEDURE CallingProc)
         END;
         /
         
Warning: Package created with compilation errors.
 
Command> SHOW ERRORS
Errors for PACKAGE PROTECTEDPKG1:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PLS-00157: Only schema-level programs allow ACCESSIBLE BY

This example uses the ACCESSIBLE BY clause in the CREATE PACKAGE BODY statement. 

Command> CREATE OR REPLACE PACKAGE ProtectedPkg3
           ACCESSIBLE BY (PROCEDURE CallingProc3)
         AS
           PROCEDURE ProtectedProc3;
         END;
         /
        
Package created.

Command> CREATE OR REPLACE PACKAGE BODY ProtectedPkg3
           ACCESSIBLE BY (PROCEDURE CallingProc3)
         AS
           PROCEDURE ProtectedProc3 AS
           BEGIN
             NULL;
           END;
           ;
         /
 
Warning: Package body created with compilation errors.
 
Command> SHOW ERRORS
Errors for PACKAGE BODY PROTECTEDPKG3:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1      PLS-00103: Encountered the symbol "ACCESSIBLE" when expecting one of the
following:
 
  is as compress compiled wrapped

Ensuring Only the API Can Access the Helper Package

This example walks through a series of steps to illustrate the use of the AccessibleByClause. The example creates the SampleAPI package and the SampleHelper package. The ACCESSIBLE BY clause is specified on the SampleHelper to ensure that only the SampleAPI package can access the SampleHelper package.

Steps:

  1. Create the SampleHelper package. Specify the ACCESSIBLE BY clause, giving the SampleAPI package access to the SampleHelper package. The SampleAPI package is in the white list. 

    Command> CREATE OR REPLACE PACKAGE SampleHelper
           ACCESSIBLE BY (SampleAPI)
         AS
           PROCEDURE SampleH1;
           PROCEDURE SampleH2;
         END;
         /
 
Package created.

  2. Create the SampleHelper package body. 

    Command> CREATE OR REPLACE PACKAGE BODY SampleHelper
         AS
           PROCEDURE SampleH1 AS
           BEGIN
             DBMS_OUTPUT.PUT_LINE('Sample helper procedure SampleH1');
           END;
           PROCEDURE SampleH2 AS
           BEGIN
             DBMS_OUTPUT.PUT_LINE('Sample helper procedure SampleH2');
           END;
         END;
         /
 
Package body created.

  3. Create the SampleAPI package. 

    Command> CREATE OR REPLACE PACKAGE SampleAPI
         AS
           PROCEDURE p1;
           PROCEDURE p2;
         END;
         /
 
Package created.

  4. Create the SampleAPI package body. The p1 procedure references the SampleHelper.SampleH1 procedure. The p2 procedure references the SampleHelper.SampleH2 procedure. 

    Command> CREATE OR REPLACE PACKAGE BODY SampleAPI
         AS
           PROCEDURE p1 AS
           BEGIN
             DBMS_OUTPUT.PUT_LINE('SampleAPI procedure p1');
             SampleHelper.SampleH1;
           END;
           PROCEDURE p2 AS
           BEGIN
             DBMS_OUTPUT.PUT_LINE('SampleAPI procedure p2');
             SampleHelper.SampleH2;
           END;
         END;
         /
 
Package body created.

  5. Call the SampleAPI.p1 and the SampleAPI.p2 procedures. The SampleAPI package is in the white list of the SampleHelper package, resulting in successful execution. 

    Command> SET SERVEROUTPUT ON
Command> BEGIN
           SampleAPI.p1;
           SampleAPI.p2;
         END;
         /
SampleAPI procedure p1
Sample helper procedure SampleH1
SampleAPI procedure p2
Sample helper procedure SampleH2
 
PL/SQL procedure successfully completed.

  6. Call the SampleHelper.SampleH1 procedure directly. An error is returned due to insufficient access privileges. 

    Command> BEGIN
           SampleHelper.SampleH1;
         END;
        /
 8503: ORA-06550: line 2, column 3:
PLS-00904: insufficient privilege to access object SAMPLEHELPER
 8503: ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
The command failed.