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.
This example calls the function f1 from the package test1:
But this can also mean the function f1 from the schema test1.
- 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.
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:
Functions defined in a package must be referenced with the package name qualifier:
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.
Package related statements include:
- CREATE PACKAGE
- CREATE PACKAGE BODY
- ALTER PACKAGE
- DROP PACKAGE BODY
- DROP PACKAGE
DROP PACKAGE drops both body and specification.
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:
One can query the metadata views DBA_PROCEDURES, ALL_PROCEDURES, USER_PROCEDURES to list routines defined inside packages.
Now this query:
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:
returns no records.
The ALL_SOURCE metadata view can be used to see the source code of packages and their bodies, as well as stand-alone routines.
Notice, comment lines inside CREATE PACKAGE and CREATE PACKAGE BODY are preserved.
The exact grammar can slightly vary between Oracle version.
Note, it's not desirable to use package as the new table name, because it's a reserved keyword in Oracle.
The apprimate structure of the new table:
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.
- 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
- 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.
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
- 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.
- 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.
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.
- 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.
- 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.
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:
- 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'
- 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).
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
- think about MDL to avoid concurrent CREATE PACKAGE pkg1, DROP PACKAGE pkg1, CALL pkg1.proc1().