Oracle-style packages

General information

A package is a schema object that groups logically related PL/SQL data types, items (e.g. variables, cursors, exceptions) and subprograms.

Packages usually have two parts:

• a specification
• a body

Sometimes the body is unnecessary.

The specification describes the interface of the package; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the specification.
A package can have an initialization block which is executed once, when the package is referenced for the first time.

If we think of packages in terms of C++, the specification is a class definition in a .h file, and the body is method implementations in .cc files.

All routines that have prototypes in CREATE PACKAGE must be further implemented in CREATE PACKAGE BODY. An attempt to create an incomplete package body returns an error.

The body can have its own declarations (e.g. types, items, subprograms) not specified in the interface. In terms of C++, these declarations are similar to private declarations inside a class.

Name resolution

This example calls the function f1 from the package test1:

SELECT test1.f1() FROM DUAL;

But this can also mean the function f1 from the schema test1.

Rules:

• Packages and schemas with the same name can co-exist.
• If a package with the name test1 exists in the current schema, then Oracle searches for function f1 in this package test1 only. It doesn't search in the schema test1 any more. So packages hide shemas with the same name.

Name space

Package names use the same name space with functions and procedures. For example, a CREATE FUNCTION test1 followed by CREATE PACKAGE test1 returns an error:

DROP FUNCTION test1;

DROP PACKAGE test1;

DROP PROCEDURE test1;

CREATE FUNCTION test1 RETURN INT

AS

  a INT :=10;

BEGIN

  RETURN a;

END;

/

CREATE PACKAGE test1

AS

  a INT := 10;

FUNCTION f1 RETURN INT;

END test1;

/

ORA-00955: name is already used by an existing object

Name references

Functions defined in a package must be referenced with the package name qualifier:

SELECT test1.f1() FROM DUAL;

There is no a way to call the function f1 from outside of the package without using the test1 qualifier.

Function implementations defined in the same CREATE PACKAGE BODY test1 do not have to use the qualifier test1 to refer to each other.

DDL statements

Package related statements include:

• CREATE PACKAGE
• CREATE PACKAGE BODY
• ALTER PACKAGE
• DROP PACKAGE BODY
• DROP PACKAGE

DROP PACKAGE drops both body and specification.

SHOW statements

We'll implement the following SHOW statements:

• SHOW PACKAGE STATUS [LIKE 'pkg_name'];
• SHOW PACKAGE BODY STATUS [LIKE 'pkg_name']
• SHOW CREATE PACKAGE pkg_name;
• SHOW create PACKAGE BODY pkg_name;

Qualified package names will also be supported:

SHOW CREATE PACKAGE db_name.pkg_name;

SHOW CREATE PACKAGE BODY db_name.pkg_name;

DBA_PROCEDURES, ALL_PROCEDURES, USER_PROCEDURES metadata views.

One can query the metadata views DBA_PROCEDURES, ALL_PROCEDURES, USER_PROCEDURES to list routines defined inside packages.

DROP PACKAGE test1;

DROP FUNCTION test1;

DROP PROCEDURE test1;

CREATE PACKAGE test1 AS

  FUNCTION f1 RETURN INT;

  FUNCTION f2 RETURN INT;

END test1;

/

Now this query:

SELECT OBJECT_NAME || ' ' ||OBJECT_TYPE ||' '|| PROCEDURE_NAME AS c FROM USER_PROCEDURES WHERE OBJECT_NAME='TEST1';

returns

C

--------------------------------------------------------------------------------

TEST1 PACKAGE F1

TEST1 PACKAGE F2

TEST1 PACKAGE

Note, the routines defined in a CREATE PACKAGE statement are immediately seen in the XXX_PROCEDURES metadata views, even before the corresponding CREATE PACKAGE BODY statement.

Note, stand-alone routines are not visible in the XXX_PROCEDURES metadata views. This script:

DROP PACKAGE test1;

DROP FUNCTION f1;

CREATE FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;

/

SELECT OBJECT_NAME || ' ' ||OBJECT_TYPE ||' '|| PROCEDURE_NAME AS c FROM USER_PROCEDURES WHERE PROCEDURE_NAME='F1';

returns no records.

ALL_SOURCE metadata view

The ALL_SOURCE metadata view can be used to see the source code of packages and their bodies, as well as stand-alone routines.

DROP FUNCTION f1;

DROP PACKAGE test1;

CREATE FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;

/

CREATE PACKAGE test1 AS

  // This is a comment

  FUNCTION f2 RETURN INT;

END;

/

CREATE PACKAGE BODY test1 AS

  // This is one more comment

  FUNCTION f2 RETURN INT AS BEGIN RETURN 11; END;

END;

/

SELECT OWNER || ' ' || TYPE || ' ' || NAME || ' '|| LINE || ' '|| TEXT AS l

FROM all_source

WHERE name IN ('TEST1','F1') ORDER BY NAME, TYPE, LINE;

L

--------------------------------------------------------------------------------

SYSTEM FUNCTION F1 1 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;

SYSTEM PACKAGE TEST1 1 PACKAGE test1 AS

SYSTEM PACKAGE TEST1 2	 // This is a comment

SYSTEM PACKAGE TEST1 3	 FUNCTION f2 RETURN INT;

SYSTEM PACKAGE TEST1 4 END;

SYSTEM PACKAGE BODY TEST1 1 PACKAGE BODY test1 AS

SYSTEM PACKAGE BODY TEST1 2   // This is one more comment

SYSTEM PACKAGE BODY TEST1 3   FUNCTION f2 RETURN INT AS BEGIN RETURN 11; END;

SYSTEM PACKAGE BODY TEST1 4 END;

Notice, comment lines inside CREATE PACKAGE and CREATE PACKAGE BODY are preserved.

Grammar for the CREATE PACKAGE statement

The exact grammar can slightly vary between Oracle version.

create_package_statement ::=

CREATE [ OR REPLACE ] PACKAGE [ schema. ] package

   [ invoker_rights_clause ]

   { IS | AS } [ item_list_1 ] END [ package_name ] ;

invoker_rights_clause ::= AUTHID { CURRENT_USER | DEFINER }

item_list_1 ::= item_list_1_initial {item_list_1_initial | pragma}...

item_list_1_initial ::= 

    type_definition

  | item_declaration

  | function_declaration

  | procedure_declaration

type_definition ::=

    record_type_definition

  | ref_cursor_type_definition

  | subtype_definition

  | collection_type_definition

record_type_definition ::=

  TYPE type_name IS RECORD ( field_declaration [, field_declaration]... ) ;

ref_cursor_type_definition ::= 

  TYPE type_name IS REF CURSOR

  [ RETURN

    { {db_table_name | cursor_name | cursor_variable_name}%ROWTYPE

    | record_name%TYPE

    | record_type_name

    | ref_cursor_type_name

    }

  ] ;

subtype_definition ::= SUBTYPE subtype_name IS base_type [ ( constraint ) ] [ NOT NULL ]

collection_type_definition ::= TYPE type_name IS collection_type_def

collection_type_def ::= 

    assoc_array_type_def

  | nested_table_type_def

  | varray_type_def

assoc_array_type_def ::= TABLE OF element_type [ NOT NULL ]

   [ INDEX BY { PLS_INTEGER | BINARY_INTEGER | VARCHAR2 ( v_size ) } ]

nested_table_type_def ::= TABLE OF element_type [ NOT NULL ]

element_type ::=

    cursor_name%ROWTYPE

  | db_table_name{%ROWTYPE | .column_name%TYPE}

  | object_name%TYPE

  | [REF] object_type_name

  | record_name[.field_name]%TYPE

  | record_type_name

  | scalar_datatype_name

  | variable_name%TYPE

item_declaration ::=

  collection_variable_declaration

| constant_declaration

| cursor_declaration

| cursor_variable_declaration

| exception_declaration

| object_declaration      -- TODO: add grammar

| object_ref_declaration  -- TODO: add grammar

| record_declaration      -- TODO: add grammar

| variable_declaration

collection_variable_declaration ::= collection_name type_name

constant_declaration ::= constant_name CONSTANT datatype [NOT NULL] { := | DEFAULT } expression ;

cursor_declaration ::= CURSOR cursor_name

 [ ( cursor_parameter_declaration [, cursor_parameter_declaration ]... )]

   [ RETURN rowtype] IS select_statement

cursor_variable_declaration ::= TYPE type IS REF CURSOR

  [ RETURN

    { {db_table_or_view | cursor | cursor_variable}%ROWTYPE

    | record%TYPE

    | record_type

    | ref_cursor_type

    }

  ]

exception_declaration ::= exception_name EXCEPTION

variable_declaration ::=

  variable_name datatype [ [ NOT NULL] {:= | DEFAULT} expression ]

Grammar for the CREATE PACKAGE BODY statement

create_package_body_statement ::=

CREATE [ OR REPLACE ] PACKAGE BODY [ schema. ] package

   { IS | AS } [ declare_section ] { body | END package_name } ;

declare_section ::=

   item_list_1

 | item_list_2

 | item_list_1 item_list_2

item_list_2 ::=

{ function_declaration

| function_definition

| procedure_declaration

| procedure_definition

}

  [ { function_declaration

    | function_definition

    | procedure_declaration

    | procedure_definition

    | pragma

    }

  ]...

function_declaration ::=

  function_heading [ DETERMINISTIC | PIPELINED | RESULT_CACHE ]... ;

function_heading ::=

  FUNCTION function_name [ ( parameter_declaration ) ] RETURN datatype

function_definition ::=

  function_heading [ DETERMINISTIC

                   | PIPELINED

                   | result_cache_clause

                   ]... { IS | AS } [ declare_section ] body

procedure_declaration ::= procedure_heading

procedure_heading ::=

  PROCEDURE procedure_name

  [ ( parameter_declaration [, parameter_declaration ]... ) ] ;

procedure_definition ::=

  procedure_heading { IS | AS } [ declare_section ] body

body ::=

  BEGIN statement [ statement | pragma ]...

  [ EXCEPTION exception_handler [ exception_handler ]... ]

  END [ name ] ;

Possible implementation solutions.

1. Store the entire package definition in a new system table packages.

Note, it's not desirable to use package as the new table name, because it's a reserved keyword in Oracle.

a. Store the entire package as a record in the new system table, in binary format.

The apprimate structure of the new table:

CREATE TABLE packages (

  db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  name char(64) NOT NULL,

  security_type enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',

  interface longblob NOT NULL,

  body longblob,

  definer char(141) NOT NULL,

  sql_mode set (<the same definition with sql_mode in mysql.proc>) NOT NULL DEFAULT '',

  character_set_client char(32) NOT NULL,

  collation_connection char(32) NOT NULL,

  db_collation char(32) NOT NILL,

  body_utf8 longblob,

  PRIMARY KEY (db,name)

) DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

The meaning of the columns sql_mode, character_set_client, character_set_connection, db_collation, body_utf8 is the same with the meaning of similar columns in the table mysql.proc.

Data flow:

• CREATE PACKAGE will create a new record in the table mysql.packages and populate the column packages.interface, while the column packages.body will remain NULL.
• CREATE PACKAGE BODY will populate the column packages.body.
• DROP PACKAGE will delete the corresponding record in packages.
• DROP PACKAGE BODY will set packages.body to NULL
• ALTER PACKAGE BODY will replace packages.body

Open questions:

• Q1. What should we do if the character set variables during the CREATE PACKAGE and CREATE PACKAGE BODY are different. We could return an error, or apply some conversion. But both seems to be potentially buggy.

b. Similar to #2, but store packages.interface and packages.body in the utf8 character set:

CREATE TABLE packages (

  db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  name char(64) NOT NULL,

  security_type enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',

  interface longtext NOT NULL,

  body longtext,

  definer char(141) NOT NULL,

  sql_mode set (<the same definition with sql_mode in mysql.proc>) NOT NULL DEFAULT '',

  PRIMARY KEY (db,name)

) DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

The CREATE PACKAGE and CREATE PACKAGE BODY statements will convert the information from the client character set to utf8 and store in the columns interface and body.

This simplifies the things and solves the question Q1 in the section a, however will impose some minor limitations in the first version: some tricky string literals won't be able to be stored. Later we'll implement Unicode Escape Sequences as described in MySQL WL#3529 to overcome this minor limitation. This should be a more future proof solution than having the four extra character set conversion related columns in the new table.

2. Store the entire package definiton in files in the database directory (like views and triggers do).

This is very similar to 1.a and 1.b, however the interface and the implementation of a package are stored in files in the database directory. The interface is stored in a file with the extension .pin, and the implementation is stored in the file with the extension .pif.

As in 1, two formats are possible:

• a. Binary format with character set information
• b. utf8 format

Data flow:

• CREATE PACKAGE pkg1 stores information in the file pkg1.pin in the database directory.
• CREATE PACKAGE BODY pkg1 stored information in the fike pkg1.pif in the database directory.
• DROP PACKAGE pgk1 deletes both files pkg1.pin and pkg1.pif
• DROP PACKAGE BODY pgk1 deletes the file pkg1.pif
• ALTER PACKAGE BODY pkg1 replaces information in the file pkg1.pif

3. Do not store the entire package implementation as a single object. Store definition of every package object separately. Every function and procedure will generate a record in the table mysql.proc.

This will need some changes in the table mysql.proc.

ALTER TABLE proc DROP PRIMARY KEY;

ALTER TABLE proc ADD ordinal_position INT NOT NULL DEFAULT 0;

ALTER TABLE proc ADD package_name VARCHAR(64) NOT NULL DEFAULT '';

ALTER TABLE proc ADD PRIMARY KEY (db,package_name,name,type);

Open questions:

• Where to store the package interface? It's useful for fast object lookup.
• What should we do on errors if during a CREATE PACKATE BODY query n routines have been already created and the n+1-th routine fails to create for some reasons (e.g. disk full). All created routine definitions should be deleted.
• It's not clear what to with with comments inside CREATE PACKAGE [BODY]. It would be nice to preserve them somehow.
• This will bring some data duplication. All routines inside a package use the same SQL SECURITY (i.e. INVOKER or DEFINER, as well as the same sql_mode. It's not clear what to do if somebody manually modifies the security_type or the sql_mode for some routine in a package without modifying the other routines of the same package.

4. Store packages in preudo-databases

Every CREATE PACKAGE could create a preudo-database with a special name which will be a combination of the owning database name and the package name. For example, a CREATE PACKAGE pkg1 in a database db1 could create a pseudo database with name `db1#pkg1`, while a later CREATE PACKAGE BODY pkg1 could create routines in this new pseudo-database. Preudo databases won't be visible for normal SHOW DATABASES.

Data flow:

• CREATE PACKAGE creates a pseudo-database.
• CREATE PACKAGE BODY creates routines in the preuso-database.
• DROP PACKAGE drops the pseudo-database.
• DROP PACKAGE BODY drops all routines in the pseudo-database.
• ALTER PACKAGE BODY drops all routines in the preuso-database and creates them again.

Open questions:

• The column proc.db has a limit of 64 characters. Using it to store the combination of the database name and the package name will most likely require to extent the column proc.db to 129 characters, to fit both.

5. Store the entire package interfaces and bodies in mysql.proc

In Oracle routines and packages reuse the same name space. We cannot exactly reproduce this, because mysql.proc has a primary key on (db,name,type) and thus PROCEDURE p1 and FUNCTION p1 can co-exists.

But we could store package interfaces and package bodies in the same table mysql.proc. It is very similar to 1a, however every package will generate two records instead of one record (i.e. one record for the interface and one record for the body). No records for the individual package routines will be inserted into mysql.proc.

This will need a change in the table:

ALTER TABLE mysql.proc MODIFY type ENUM('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY');

Data flow:

• CREATE PACKAGE pkg1 inserts a record with type 'PACKAGE' and name 'pkg1'
• CREATE PACKAGE BODY pkg1 inserts a record with type 'PACKAGE BODY' and name 'pkg1'
• ALTER PACKAGE BODY pkg1 updates the record with type 'PACKAGE BODY' and name 'pkg1'
• DROP PACKAGE BODY pkg1 deletes the record with type 'PACKAGE BODY' and name 'pkg1'
• DROP PACKAGE pkg1 deletes the records with name 'pkg1' and types 'PACKAGE' and 'PACKAGE BODY'

Open questions:

• Some columns, e.g. is_deterministic, param_list are not applicable to packages. These columns can be set to NULL for the package related records. However, this brings some data redundancy.
• We'll have to keep maintaining the body_utf8, character_set_client, collation_connection columns for packages. This makes it harder to get rid of theme eventually (to use the column body for both SHOW and I_S purposes).

Conclusions

The solutions 1.b and 2.b look the most promising.

2017-01-20 UPDATE: during a discussion with Monty and Bar, it was decided that we'd go 1.b. This will help to avoid storing two definitions:

• binary definition for parsing purposes
• utf8 definition for INFORMATION_SCHEMA purposes

TODO:

• think about MDL to avoid concurrent CREATE PACKAGE pkg1, DROP PACKAGE pkg1, CALL pkg1.proc1().