CREATE MODULE statement

The CREATE MODULE statement creates a module at the application server.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the module does not exist.
  • CREATEIN privilege on the schema, if the schema name of the module refers to an existing schema.
  • SCHEMAADM authority on the schema, if the schema name of the module refers to an existing schema.
  • DBADM authority
To replace an existing module, the authorization ID of the statement must be the owner of the existing module (SQLSTATE 42501).

Syntax

Read syntax diagramSkip visual syntax diagramCREATEOR REPLACE MODULEmodule-name

Description

OR REPLACE
Specifies replacing the definition for the module if one exists at the current server. The existing module definition is effectively dropped, including all the objects in the module, before the new definition is replaced in the catalog, with the exception that privileges that were granted on the module are not affected. This option is ignored if a definition for the module does not exist at the current server. This option can be specified only by the owner of the object.
module-name
Names the module. The name, including the implicit or explicit qualifier, must not identify an existing module at the current server. The module name and the schema name must not begin with the characters 'SYS' (SQLSTATE 42939) and use of SESSION is not recommended.

Notes

  • A module is intended to be a collection of other database objects. Once a module is created, objects in the module are managed using the ALTER MODULE statement. A module can include functions, procedures, types, global variables and conditions. The objects in a module can be published to make them available for reference from outside the module. If an object is not published, it can only be referenced from within the module. A module can be considered to consist of 2 parts:
    • The module specification consists of all the published objects excluding the bodies of any routines.
    • The module body which consists of all objects that are not published and the bodies of any published routines.
    The module management actions include
    • ADD to add an object to the module without publishing it or to replace a routine prototype with the implemented routine definition.
    • PUBLISH to add an object to the module and publish it.
    • COMMENT on objects in the module.
    • DROP to drop an object within the module or drop the module body.
    At least one published object should exist in a module in order to have some way to reference the module.

Example

Create a module named salesModule
   CREATE MODULE salesModule