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

  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 INVOKER, 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

  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

• Example of 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;
  /

• Example of 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

  ALTER PACKAGE emp_bonus OWNER TO omm;
  -- Change the owner of PACKAGE emp_bonus 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;
  /