CREATE PACKAGE

Function

CREATE PACKAGE creates a package.

Precautions

• The package can be used only in centralized databases and cannot be used in distributed databases.

• The functions or stored procedures declared in the package specification must be defined in the package body.

• During instantiation, the stored procedure with commit or rollback cannot be invoked.

• Package functions cannot be invoked in triggers.

• Variables in a package cannot be directly used in external SQL statements.

• Private variables and stored procedures in a package cannot be invoked outside the package.

• Usage that other stored procedures do not support are not supported. For example, if commit or rollback cannot be invoked in a function, commit or rollback cannot be invoked in the function of a package.

• The name of a schema cannot be the same as that of a package.

• Only A-version stored procedures and function definitions are supported.

• Variables with the same name in a package, including parameters with the same name in a package, are not supported.

• The global variables in a package are at the session level. The variables in packages cannot be shared in different sessions.

• When a function of an autonomous transaction is called in a package, the cursor variables in the package and recursive functions that use the cursor variables in the package are not allowed.

• The package does not declare the ref cursor variables.

• The default permission on a package is SECURITY INVOKER. To change the default permission to SECURITY DEFINER, set the GUC parameter behavior_compat_options to 'plsql_security_definer'.

• A user granted with the CREATE ANY PACKAGE permission can create packages in the public and user schemas.

• If the name of a package to be created contains special characters, the special characters cannot contain spaces. You are advised to set the GUC parameter behavior_compat_options to "skip_insert_gs_source". Otherwise, an error may occur.

Syntax

• CREATE PACKAGE SPECIFICATION

  CreatePackage ::= CREATE [ OR REPLACE ] PACKAGE [ schema'.' ] package_name
    [ invoker_rights_clause ] { IS | AS } item_list_1 END package_name;

  invoker_rights_clause can be declared as AUTHID DEFINER or AUTHID CURRENT_USER, which indicate the definer permission and invoker permission, respectively. item_list_1 can be a declared variable, stored procedure, or function.

  The package specification declares public variables, functions, and exceptions in a package, which can be invoked by external functions or stored procedures. It can only declare stored procedures and functions but cannot define them.

• CREATE PACKAGE BODY

  CreatePackageBody ::= CREATE [ OR REPLACE ] PACKAGE BODY [ schema'.' ] package_name
    { IS | AS } declare_section [ initialize_section ] END package_name;

  The package body defines private variables and functions in a package. If a variable or function is not declared by the package specification, it is a private variable or function.

  The package body also has an initialization part to initialize the package. For details, see the example.

Examples

• CREATE PACKAGE SPECIFICATION

  CREATE OR REPLACE PACKAGE emp_bonus IS
      var1 int:=1;-- Public variable
      var2 int:=2;
      PROCEDURE testpro1(var3 int);-- Public stored procedure, which can be called by external systems.
      END emp_bonus;
      /

• CREATE PACKAGE BODY

  drop table if exists test1;
  create or replace package body emp_bonus is
  var3 int:=3;
  var4 int:=4;
  procedure testpro1(var3 int)
  is
  begin
  create table if not exists test1(col1 int);
  insert into test1 values(var1);
  insert into test1 values(var4);
  end;
  begin: --The instantiation starts.
  var4:=9;
  testpro1(var4);
  end emp_bonus;
  /

• Example of ALTER PACKAGE OWNER

  -- Change the owner of PACKAGE emp_bonus to omm.
  ALTER PACKAGE emp_bonus OWNER TO omm;

• Example of calling a package

  call emp_bonus.testpro1(1); -- Use **call** to call the stored procedure of a package.
  select emp_bonus.testpro1(1); -- Use **select** to call the stored procedure of a package.
  --Call the stored procedure of a package in an anonymous block.
  begin
  emp_bonus.testpro1(1);
  end;
  /