The package specification defines the user interface for a package (the API). The specification lists the functions, procedures, types, exceptions, and cursors that are visible to a user of the package.

The following syntax is used to define an interface for a package:

CREATE [ OR REPLACE ] PACKAGE package_name
  [ authorization_clause ]
  { IS | AS }
  [ declaration; ] ...
 [ procedure_or_function_declaration; ] ...
 [ package_name ] ;

Where

 authorization_clause :=
{ AUTHID DEFINER } | { AUTHID CURRENT_USER }

Where

procedure_or_function_declaration :=
procedure_declaration | function_declaration

Where

procedure_declaration :=
PROCEDURE proc_name[ argument_list ] [restriction_pragma];

Where

function_declaration :=
FUNCTION func_name [ argument_list ]
  RETURN rettype [ restriction_pragma ];

Where

argument_list :=
( argument_declaration [, ...] )

Where

argument_declaration :=
argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ]

Where

restriction_pragma :=
PRAGMA RESTRICT_REFERENCES(name, restrictions)

Where

restrictions :=
restriction [, ... ]

Parameters

Parameter Description
package_name The identifier assigned to the package. Each package must have a unique name within the schema.
AUTHID DEFINER If you omit the AUTHID clause or specify the AUTHID DEFINER parameter, permissions of the package owner are used to determine permissions of accessing database objects.
AUTHID CURRENT_USER If you specify the AUTHID CURRENT_USER parameter, permissions of the current user who runs a program in the package are used to determine access permissions.
declaration The identifier of a public variable. A public variable can be accessed from outside the package by using the package_name.variable syntax. There can be zero, one, or more public variables. You must define public variables before you declare procedures or functions.

Valid values:

  • Variable declaration
  • Record declaration
  • Collection declaration
  • REF CURSOR and CURSOR variable declaration
  • TYPE definitions for records, collections, and REF CURSORs
  • Exception
  • Object variable declaration
proc_name The name of a public procedure.
argname The name of an argument. The argument is referenced by this name within a function or procedure body.
IN | IN OUT | OUT The argument mode. IN: The argument is only used for input. This is the default value. IN OUT: The argument is used to receive a value and return a value. OUT: The argument is only used for output.
argtype The data types of an argument. An argument type can be a basic data type, a copy of the type of an existing column that uses %TYPE, or a user-defined type such as a nested table or an object type. The basic data type cannot be specified a length. For example, you must specify VARCHAR2 instead of VARCHAR2(10) as the data type.

You can write tablename.columnname%TYPE to reference the type of a column. This enables a procedure be independent of changes of the definition of a table sometimes.

DEFAULT value If the input argument is not provided when you call the procedure, the DEFAULT clause provides a default value for the input argument. You cannot specify DEFAULT for arguments that are in the IN OUT or OUT modes.
func_name The name of a public function.
rettype The data type returned.
DETERMINISTIC DETERMINISTIC a synonym for IMMUTABLE. A DETERMINISTIC function cannot be used to modify the database and always returns the same result when you input the same argument values. This function is not used to query database and does not use the information that is not in the argument list. If you include this clause, any call of the function with all-constant arguments can be replaced with the function value.
restriction The following keywords are supported for compatibility and can be ignored.
  • RNDS
  • RNPS
  • TRUST
  • WNDS
  • WNPS