PSM SQL References

ALTER FUNCTION

Function

It recompiles a function.

Syntax

<alter function statement> ::=
    ALTER FUNCTION function_name COMPILE
    ;

Invocation and Access Rules

One of the following privileges is required to perform <alter function statement>.

Syntax Rules and Parameters

function_name

It is a name of function to be compiled.
It can define the schema to which the function belongs, such as schema_name.func_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

Description

It recompiles a specified schema-level function.

Examples

gSQL> ALTER FUNCTION FUNC1 COMPILE;

Function altered.

Compatibility

It is a statement altering characteristics specified when performing CREATE in the SQL standard, and astatement recreating a plan of a function in GOLDILOCKS.
SQL standard compatibility

Feature ID

Description

Compatibility

F381

Extended schema manipulation

O

For More Information

Refer to the following.

ALTER PACKAGE

Function

It recompiles a package.

Syntax

<alter package statement> ::=
    ALTER PACKAGE package_name <package compile clause>
    ;

<package compile clause> ::=
    COMPILE [PACKAGE|SPECIFICATION|BODY]

Invocation and Access Rules

One of the following privileges is required to perform <alter package statement>.

Syntax Rules and Parameters

package_name

It is a name of package to be compiled.
It can define the schema to which the package belongs, such as schema_name.package_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

<package compile clause>

It specifies the target to compile for a package.
If the target is omitted, it is the same as specifying PACKAGE, and both the package specification and package body are recompiled.

Description

It recompiles the specified package.
The execution code of the compiled package is stored in the plan cache.

Examples

ALTER PACKAGE PKG1 COMPILE;
Package altered.
ALTER PACKAGE PKG1 COMPILE PACKAGE;
Package altered.
ALTER PACKAGE PKG1 COMPILE BODY;
Package altered.

Compatibility

It is ALTER MODULE statement in the SQL standard.

For More Information

Refer to the following.

ALTER PROCEDURE

Function

It recompiles a procedure.

Syntax

<alter procedure statement> ::=
    ALTER PROCEDURE proc_name COMPILE
    ;

Invocation and Access Rules

One of the following privileges is required to perform <alter procedure statement>.

Syntax Rules and Parameters

proc_name

It is a name of procedure to be compiled.
It can define the schema to which the procedure belongs, such as schema_name.proc_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

Description

It recompiles a specified schema-level procedure.

Examples

gSQL> CREATE OR REPLACE PROCEDURE PROC1( A1 INTEGER )
IS
BEGIN
  INSERT INTO T1 VALUES( A1 );
END;
/

ERR-01000(16409): Warning: Routine definition has compilation errors
ERR-HY000(17032): PSM compilation error : 
(1) at (5:15): ERR-17053: schema or table object does not exist
Procedure created.

gSQL> CALL PROC1(1);

ERR-HY000(17032): PSM compilation error : 
(1) at (5:15): ERR-17053: schema or table object does not exist

gSQL> CREATE TABLE T1( I1 INTEGER );

Table created.

gSQL> COMMIT;

Commit complete.

gSQL> ALTER PROCEDURE PROC1 COMPILE;

Procedure altered.

gSQL> COMMIT;

Commit complete.

gSQL> CALL PROC1(2);

Procedure Call complete.

gSQL> SELECT * FROM T1;

I1
--
 2

1 row selected.

Compatibility

It is a statement altering characteristics specified when performing CREATE in the SQL standard, and astatement recreating a plan of a procedure in GOLDILOCKS.
SQL standard compatibility

Feature ID

Description

Compatibility

F381

Extended schema manipulation

O

For More Information

Refer to the following.

ALTER TRIGGER name COMPILE

Function

It recompiles the trigger.

Syntax

<alter trigger compile statement> ::=
    ALTER TRIGGER <trigger name> COMPILE
    ;

Invocation and Access Rules

One of the following privileges is required to perform <alter trigger compile statement>.

Syntax Rules and Parameters

<trigger name>

It is a name of trigger to be compiled.
It can define the schema to which the trigger belongs, such as schema_name.trigger_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

Description

It recompiles the specified trigger.

Examples

-- This is a trigger that records the changes in orders to order_status_history when the orders table is updated.
gSQL> 
CREATE OR REPLACE TRIGGER trg_orders_status_audit
AFTER UPDATE OF status ON orders
REFERENCING OLD ROW AS o_row
            NEW ROW AS n_row            
FOR EACH ROW
BEGIN
  INSERT INTO order_status_history VALUES( o_row.order_id,
                                           o_row.status,
                                           n_row.status,
                                           SYSDATE );
END;
/

ERR-01000(16659): Warning: trigger "PUBLIC"."TRG_ORDERS_STATUS_AUDIT" has compilation errors : 
(1) at (8:3): ERR-42000(16040): table or view does not exist
Trigger created.

-- The UPDATE statement failed because the trigger was invalid.
gSQL>
UPDATE orders
   SET status = 'Shipped', updated_at = SYSDATE
 WHERE order_id = 1;

ERR-0W000(17134): TRIGGER(TRG_ORDERS_STATUS_AUDIT) compilation error : 
(1) at (4:3): ERR-42000(16040): table or view does not exist

-- Created the order_status_history table referenced by the trigger.
gSQL>
CREATE TABLE order_status_history( order_id   NUMBER,
                                   old_status VARCHAR2(20),
                                   new_status VARCHAR2(20),
                                   changed_at DATE );

Table created.

-- Recompiled the trigger to check its status.
gSQL> ALTER TRIGGER trg_orders_status_audit COMPILE;

Trigger altered.

-- Successfully performed the UPDATE on the orders table.
gSQL>
UPDATE orders
   SET status = 'Shipped', updated_at = SYSDATE
 WHERE order_id = 1;

1 row updated.

gSQL> SELECT * FROM order_status_history;

ORDER_ID OLD_STATUS     NEW_STATUS CHANGED_AT
-------- -------------- ---------- ----------
       1 Order Received Shipped    2025-08-12

1 row selected.

Compatibility

It is not defined in the SQL standard.

For More Information

Refer to the following.

ALTER TRIGGER name ENABLE/DISABLE

Function

It can enable or disable the trigger.

Syntax

<alter trigger enforcement statement> ::=
    ALTER TRIGGER <trigger name> <trigger enforcement>
    ;

<trigger enforcement> ::=
      { ENABLE | ENFORCED }
    | { DISABLE | NOT ENFORCED }

Invocation and Access Rules

One of the following privileges is required to perform <alter trigger enforcement statement> .

Syntax Rules and Parameters

<trigger name>

It is the name of the trigger whose enablement status is to be changed.
It can define the schema to which the trigger belongs, such as schema_name.trigger_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

<trigger enforcement>

ENABLE and ENFORCED have the same meaning.
DISABLE and NOT ENFORCED have the same meaning.

Description

It can enable or disable the trigger.

Examples

-- Create an invalid trigger.
gSQL>
CREATE OR REPLACE TRIGGER trg_orders_status_audit
AFTER UPDATE OF status ON orders
REFERENCING OLD ROW AS o_row
            NEW ROW AS n_row            
FOR EACH ROW
BEGIN
  INSERT INTO order_status_history VALUES( o_row.order_id,
                                           o_row.status,
                                           n_row.status,
                                           SYSDATE );
END;
/

ERR-01000(16659): Warning: trigger "PUBLIC"."TRG_ORDERS_STATUS_AUDIT" has compilation errors : 
(1) at (7:3): ERR-42000(16040): table or view does not exist
Trigger created.

-- The UPDATE statement failed because the trigger was invalid.
gSQL> 
UPDATE orders
   SET status = 'Shipped', updated_at = SYSDATE
 WHERE order_id = 1;

ERR-0W000(17134): TRIGGER(TRG_ORDERS_STATUS_AUDIT) compilation error : 
(1) at (4:3): ERR-42000(16040): table or view does not exist

-- Disable the trigger.
gSQL> ALTER TRIGGER trg_orders_status_audit DISABLE;

Trigger altered.

-- Successfully performed the UPDATE.
gSQL> UPDATE orders
   SET status = 'Shipped', updated_at = SYSDATE
 WHERE order_id = 1;

1 row updated.
-- Create an invalid trigger in a disabled state.
gSQL>
CREATE OR REPLACE TRIGGER trg_orders_status_audit
AFTER UPDATE OF status ON orders
REFERENCING OLD ROW AS o_row
            NEW ROW AS n_row
FOR EACH ROW
DISABLE
BEGIN
  INSERT INTO order_status_history VALUES (o_row.order_id, o_row.status, n_row.status, SYSDATE);
END;
/

ERR-01000(16659): Warning: trigger "PUBLIC"."TRG_ORDERS_STATUS_AUDIT" has compilation errors : 
(1) at (8:3): ERR-42000(16040): table or view does not exist
Trigger created.

-- The trigger does not execute upon creation because it is disabled.
gSQL>
UPDATE orders
   SET status = 'Processing Order', updated_at = SYSDATE
 WHERE order_id = 1;

1 row updated.

gSQL> SELECT * FROM order_status_history;

no rows selected.

-- Create the order_status_history table referenced by the trigger.
gSQL>
CREATE TABLE order_status_history( order_id   NUMBER,
                                   old_status VARCHAR2(20),
                                   new_status VARCHAR2(20),
                                   changed_at DATE );

Table created.

-- Recompiled the trigger to check its status.
gSQL> ALTER TRIGGER trg_orders_status_audit COMPILE;

Trigger altered.

-- Enable the trigger.
gSQL> ALTER TRIGGER trg_orders_status_audit ENABLE;

Trigger altered.

-- The trigger executes on UPDATE.
gSQL>
UPDATE orders
   SET status = 'Shipped', updated_at = SYSDATE
 WHERE order_id = 1;

1 row updated.

gSQL> SELECT * FROM order_status_history;

ORDER_ID OLD_STATUS       NEW_STATUS CHANGED_AT
-------- ---------------- ---------- ----------
       1 Processing Order Shipped    2025-08-12

1 row selected.

Compatibility

It is not defined in the SQL standard.

For More Information

Refer to the following.

ALTER TRIGGER name RENAME TO

Function

It renames the trigger.

Syntax

<alter trigger rename statement> ::=
    ALTER TRIGGER <trigger name> RENAME <new trigger name>
    ;

Invocation and Access Rules

One of the following privileges is required to perform <alter trigger rename statement>.

Syntax Rules and Parameters

<trigger name>

It is the name of the trigger to be renamed.
It can define the schema to which the trigger belongs, such as schema_name.trigger_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

<new trigger name>

It is the name of the trigger to be renamed, and must be unique within the schema.
The new trigger name must be less than 128 bytes in length.

Description

It renames the specified trigger.

Examples

gSQL> ALTER TRIGGER t1 RENAME TO new_t1;

Trigger altered.

Compatibility

It is not defined in the SQL standard.

For More Information

Refer to the following.

CALL Statement

Function

It performs a schema-level procedure or a function.

Syntax

<call statement> ::= 
    <sql call statement> | <odbc procedure call escape sequence>
    ;

<sql call statement> ::=
    CALL proc_name [ ( value_expr [ , value_expr ] .. ) ]  [ INTO { '?' | { host_param [ indicator_param ] } } ]

<odbc procedure call escape sequence> ::=
    '{' [ ? = ] CALL proc_name [ ( value_expr [ , value_expr ] .. ) ] '}'

Invocation and Access Rules

One of the following privileges is required to perform <call statement>.

Syntax Rules and Parameters

proc_name

value_expr

It expresses an argument value that were transferred to the procedure. 
It can use a bind parameter such as '?' or ':V1'.

Description

It executes a schema-level SQL procedure or a function by using specified arguments.
A function of <sql call statement> form returns the result value by using a host variable expression or a dynamic bind parameter (?) after INTO clause.
<odbc procedure call escape sequence> form is a standard statement to call PROCEDURE in  ODBC/ JDBC, and GOLDILOCKS supports this statement in a server. (It can also be used in a tool such as gsql.) A function returns the result value by using assign expressions ( ? = ) at the front.

Examples

Call Procedure

gSQL> CREATE OR REPLACE PROCEDURE PROC1
(
  A1 INTEGER
)
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('A1=' || A1);
END;
/

Procedure created.

gSQL> \var v1 INTEGER;
gSQL> \exec :v1 := 123;
gSQL> CALL PROC1(:v1);
A1=123

Procedure Call complete.

Call Function

CREATE OR REPLACE FUNCTION FUNC1
(
  A1 INTEGER
)
RETURN INTEGER
IS
BEGIN
    return A1;
END;
/

Function created.


gSQL> \var v1 INTEGER;
gSQL> \var v2 INTEGER;
gSQL> \exec :v1 := 123;
gSQL> CALL FUNC1(:v1) INTO :v2;
Procedure Call complete.

gSQL> \print v2;
 V2
---
123

Compatibility

The SQL standard allows only the call for a PROCEDURE, so it does not define below [INTO] clause.

CREATE FUNCTION

Function

It defines a schema-level function.

Syntax

<create function statement> ::= 
        CREATE [ OR REPLACE ] FUNCTION <function name> 
        [ ( <parameter list> ) ]
        <return clause>
        [ <function option list> ]
        { IS | AS }
        <routine body>
        ; 

<parameter list> ::=
      <parameter> [ , ... ]

<parameter> ::=
      <parameter name>
      [ <parameter mode> ]
      <datatype>
      [ <parameter default> ]

<parameter mode> ::= 
      IN 
    | OUT 
    | IN OUT

<parameter default> ::= 
      { := | DEFAULT } <value expression>

<function option list> ::=
      <function option> [ ... ]

<function option> ::=
      <invoker rights clause>
    | <function characteristics>

<invoker rights clause> ::=
      AUTHID CURRENT_USER 
    | AUTHID DEFINER

<function characteristics> ::=
      <deterministic characteristic>
    | <null-call clause>
    | <SQL-data access indication>

<routine body> ::=
      <SQL body>
    | <external body>

<SQL body> ::=
      [ <declare item> ]
      <body>

<external body> ::=
      <call specification>

Invocation and Access Rules

The user should satisfy the following conditions to perform <create function statement>.

Syntax Rules and Parameters

OR REPLACE

It replaces an existing function with a new function when the function already exists.

function name

It is a name of function to be created, and it should be a unique name in a schema.
It can define the schema to which the function belongs, such as schema_name.function_name. If schema_name is omitted, the default schema name of the user performing the statement is used.
The length of a function name should be shorter than 128 bytes.

parameter name

It defines a parameter name of the function. 
The name of each parameter should be unique in a function. 
In other words, the function's parameter and PL item can not have the same name. 
The length of a parameter name should be shorter than 128 bytes. 
The maximum number of parameters available in a single function is limitless.

parameter mode

It sets each parameter mode. 
The parameter modes are IN, OUT, and IN OUT.
If the parameter mode is not specified, the default mode is IN.

parameter default

It is the default value of the parameter.
The parameter with the specified parameter default can be omitted when executing the function. 
If the parameter is not specified but omitted, then the default value is <value expression> specified when defining the parameter.
The datatype of <value expression> should be the datatype of the parameter.
All parameters defined after the parameter having <parameter default> should have <parameter default>.

return clause

It defines the return type of the function. 
It is defined as follows in <return clause>.

table function column list

It is the column name of the result set returned by the table function.
The length of a column name should be shorter than 128 bytes.
The number of columns are limitless.
Each column name is unique in <table function column list>.
The column name can be the same as the parameter name and the declare item name.
The column defined in <table function column list> can not be referenced in PL block of the function.

invoker rights clause

It specifies whether to execute the name interpretation and authority of the object referred when executing the function from the perspective of DEFINER or the perspective of CURRENT_USER.
If <invoker rights clause> is omitted, then the default value is AUTHID DEFINER.

function characteristics

<function characteristics> specifies the characteristics of the function.
The redundant characteristics are not allowed.
For more information, refer to Routine Characteristics.

routine body

Description

It defines a schema-level SQL function. The created function can be called from all expressions.
The definition of a function can be viewed in ROUTINES table of INFORMATION_SCHEMA. The definition of a function parameter can be viewed in PARAMETERS table of INFORMATION_SCHEMA.
If a function becomes temporarily unstable due to absences of related objects, then it can try to recreate a plan by using ALTER FUNCTION statement.
The created function can be dropped by using DROP FUNCTION statement.
The maximum number of functions to be created is not limited. Therefore, they can be created as many as the storage space is available.

Examples

gSQL> CREATE OR REPLACE FUNCTION FUNC1( A1 INTEGER, A2 INTEGER )
  RETURN INTEGER
  IS
    V1 INTEGER;
  BEGIN

    SELECT COUNT(*)
      INTO V1
      FROM T1
      WHERE T1.I1 >= A1 AND T1.I1 <= A2;

    RETURN V1;
  END;
  /

Function created.

Compatibility

The SQL standard does not define OR REPLACE clause.
SQL standard compatibility

Feature ID

Description

Compatibility

T471

Result sets return value

X

T341

Overloading of SQL-invoked functions and SQL-invoked procedures

X

S023

Basic structured types

X

S241

Transform functions

X

S024

Enhanced structured types

X

T571

Array-returning external SQL-invoked functions

X

T572

Multiset-returning external SQL-invoked functions

X

S201

SQL routines on arrays

X

S202

SQL-invoked routines on multisets

X

T323

Explicit security for external routines

X

S231

Structured type locators

X

S232

Array locators

X

S233

Multiset locators

X

T041

Basic LOB data type support

X

S027

Create method by specific method name

X

T041

Basic LOB data type support

X

T324

Explicit security for SQL routines

O

T326

Table functions

O

T651

SQL-schema statements in SQL routines

X

T652

SQL-dynamic statements in SQL routines

O

T653

SQL-schema statements in external routines

X

T654

SQL-dynamic statements in external routines

X

T655

Cyclically dependent routines

X

T272

Enhanced savepoint management

X

T522

Default values for IN parameters of SQL-invoked procedures

O

B121

Routine language Ada

X

B122

Routine language C

O

B123

Routine language COBOL

X

B124

Routine language Fortran

X

B125

Routine language MUMPS

X

B126

Routine language Pascal

X

B127

Routine language PL/I

X

B128

Routine language SQL

O

B129

Routine language Ada: VARCHAR and NUMERIC support

X

For More Information

Refer to the following.

CREATE LIBRARY

Function

It creates a library which is a schema object related to the shared library of C language program.

Syntax

<create library statement> ::=
      CREATE [ OR REPLACE ] LIBRARY <library name> 
      { IS | AS }
      '<file path name>';

Invocation and Access Rules

The user should satisfy the following conditions to perform <create library statement>.

Syntax Rules and Parameters

OR REPLACE

It replaces an existing library with a new library when the library already exists.

library name

It is a name of library to be created, and it should be a unique name in a schema.
It can define the schema to which the library belongs, such as schema_name.library_name. If schema_name is omitted, the default schema name of the user performing the statement is used.
The length of a library name should be shorter than 128 bytes.

file path name

It can specify the name or full path of the shared library of C language program.
When creating a Library object, if only the filename is specified, the file must be located in the folder set by the EXTLIB_DIR property. Conversely, if the full path is specified, the shared library at that path will be executed.
When it is used in the cluster system, the same shared library files should be managed on each node.

Description

It creates a library which is a schema object related to the shared library of C language program.
The library is called from Call Specification.
The created library can be dropped by using DROP LIBRARY statement.

Examples

gSQL>
CREATE LIBRARY lib1 AS 'add.so';
/

Library created.
gSQL>
CREATE LIBRARY lib2 AS '/home/user1/files/add.so';
/

Library created.

Compatibility

It is not defined in the SQL standard.

For More Information

Refer to DROP LIBRARY.

CREATE PACKAGE

Function

It defines the spec about public items to be used in a package.

Syntax

<create package statement> ::=
      CREATE [ OR REPLACE ] PACKAGE <package name>
      [ <invoker rights clause> ]
      { IS | AS }
      <declare item> 
      END [ <package name> ]
      ;

<invoker rights clause> ::=
      AUTHID CURRENT_USER 
    | AUTHID DEFINER

<declare item> ::=
      <variable declaration>
    | <type definition>
    | <explicit cursor declaration>
    | <explicit cursor definition>
    | <exception declaration>
    | <exception init pragma>
    | <procedure declaration>
    | <function declaration>

Invocation and Access Rules

The user should satisfy the following conditions to perform <create package statement>.

Syntax Rules and Parameters

OR REPLACE

It replaces an existing package specification when the package already exists.

PACKAGE NAME

It is a name of package to be created, and it should be a unique name in a schema. 
It can define the schema to which the package belongs, such as schema_name.package_name. If schema_name is omitted, the default schema name of the user performing the statement is used. 
The length of a package name should be shorter than 128 bytes.

invoker rights clause

It specifies whether to execute the name interpretation and authority of the object referred when executing the package from the perspective of DEFINER or the perspective of CURRENT_USER.
If <invoker rights clause> is omitted, then the default value is AUTHID DEFINER.

declare item

It declares an item which is accessible from out of the package. It is called as a public package item. 
A function and a procedure declared in the package spec should be defined in the create package body statement.
An explicit cursor declared in the package spec without SQL should be defined in the create package body statement. 
For more information about the declarable item, refer to declare item of  Block (BEGIN .. END).

Description

It creates the schema-level package specification. 
The information about package creation can be viewed in INFORMATION_SCHEMA.MODULES table.
The list of each public procedure and function in the package can be viewed in INFORMATION_SCHEMA.ROUTINES table.
The definitions of parameters of each public procedure and function in the package can be viewed in INFORMATION_SCHEMA.PARAMETERS table.
All public package items of the created package can be referred by another PSM object or the anonymous block.

If the package becomes invalid because the status of the object referred by the package is changed, then it can be recompiled with the following statements.

Examples

CREATE OR REPLACE PACKAGE PKG1
IS
  V1 INTEGER;
  PROCEDURE PROC1;
  FUNCTION FUNC1 RETURN INTEGER;
END;
/

Package created.

Compatibility

It is CREATE MODULE statement in the SQL standard.

For More Information

Refer to the following.

CREATE PACKAGE BODY

Function

It creates the definition about procedure/ function/ cursors to be used in the package.

Syntax

<create package body statement> ::=
      CREATE [ OR REPLACE ] PACKAGE BODY <package name>
      { IS | AS }
      <declare item>
      [ <initialization part> ]
      END [ <package name> ]
      ;

<declare item> ::=
    <variable declaration>
    | <type definition>
    | <explicit cursor declaration>
    | <explicit cursor definition>
    | <exception declaration>
    | <exception init pragma>
    | <procedure declaration>
    | <procedure definition>
    | <function declaration>
    | <function definition>

<initialization part> ::=
      BEGIN
      <pl statement list>
      [ <exception block> ]

Invocation and Access Rules

The user should satisfy the following conditions to perform <create package body statement>.

Syntax Rules and Parameters

OR REPLACE

It replaces an existing package body definition when the package body already exists.

PACKAGE NAME

It is a name of package body to be created, and the name the same as the name used in creating a package spec should be used. It should be a unique name in a schema. 
It can define the schema to which the package belongs, such as schema_name.package_name. If schema_name is omitted, the default schema name of the user performing the statement is used. 
The length of a package name should be shorter than 128 bytes.

declare item

It declares an item which can be used in the package body. It is called as a private package item. 
The name of private package item and the public package item should not be the same.
The routines declared in the package spec should be defined in the package body.
A cursor declared in the package spec without SQL should be defined in the package body. 
For more information about the declarable item, refer to declare item of  Block (BEGIN .. END).

Initialization Part

It describes statements which are performed only once to initialize internal variables while creating a package instance.
For more information about the declarable item, refer to declare item of  Block (BEGIN .. END).

Description

It creates the schema-level package specification. 
The information of creating the package body can be viewed in INFORMATION_SCHEMA.MODULE_BODY table.
The private package item declared in the created package body can not be referred by another PSM object or the anonymous block.
If the package body becomes invalid because the status of the object referred by the package body is changed, then it can be recompiled with the following statements.
The created package body can be dropped with the following statements.

Examples

CREATE OR REPLACE PACKAGE PKG1
IS
  V1 INTEGER;
  PROCEDURE PROC1;
  FUNCTION FUNC1 RETURN INTEGER;
END;
/

Package created.

CREATE OR REPLACE PACKAGE BODY PKG1
IS
  FUNCTION FUNC1 RETURN INTEGER
  IS
  BEGIN
      RETURN V1;
  END;

  PROCEDURE PROC1
  IS
  BEGIN
      IF V1 IS NULL
      THEN
          V1 := 10;
      ELSE
          V1 := V1 + 10;
      END IF;
  END;

END;
/

Package created.

Compatibility

The SQL standard does not define it.

For More Information

Refer to the following.

CREATE PROCEDURE

Function

It defines a schema-level procedure.

Syntax

<create procedure statement> ::= 
        CREATE [ OR REPLACE ] PROCEDURE <procedure name> 
        [ ( <parameter list> ) ]
        [ <procedure option list> ]
        { IS | AS }
        <routine body>
        ; 

<parameter list> ::=
      <parameter> [ , ... ]

<parameter> ::=
      <parameter name>
      [ <parameter mode> ]
      <datatype>
      [ <parameter default> ]

<parameter mode> ::= 
      IN 
    | OUT 
    | IN OUT

<parameter default> ::= 
      { := | DEFAULT } <value expression>

<procedure option list> ::=
      <procedure option> [ ... ]

<procedure option> ::=
      <invoker rights clause>
    | <procedure characteristics>

<invoker rights clause> ::=
      AUTHID CURRENT_USER 
    | AUTHID DEFINER

<procedure characteristics> ::=
      <deterministic characteristic>
    | <SQL-data access indication>

<routine body> ::=
      <SQL body>
    | <external body>

<SQL body> ::=
      [ <declare item> ]
      <body>

<external body> ::=
      <call specification>

Invocation and Access Rules

The user must meet the following conditions to execute the <create procedure statement>.

Syntax Rules and Parameters

OR REPLACE

It replaces an existing procedure with a new function when the procedure already exists.

procedure name

It is a name of procedure to be created, and it should be a unique name in a schema.
It can define the schema to which the procedure belongs, such as schema_name.procedure_name. If schema_name is omitted, the default schema name of the user performing the statement is used.
The length of a procedure name should be shorter than 128 bytes.

parameter name

It defines a parameter name of the procedure. 
The name of each parameter should be unique in a procedure. 
In other words, the procedure's parameter and PL item can not have the same name. 
The length of a parameter name should be shorter than 128 bytes. 
The maximum number of parameters available in a single procedure is limitless.

parameter mode

It sets each parameter mode. 
The parameter modes are IN, OUT, and IN OUT.
If the parameter mode is not specified, the default mode is IN.

parameter default

It is the default value of the parameter.
The parameter with the specified parameter default can be omitted when executing the procedure. 
If the parameter is not specified but omitted, then the default value is <value expression> specified when defining the parameter.
The datatype of <value expression> should be the datatype of the parameter.
All parameters defined after the parameter having <parameter default> should have <parameter default>.

invoker rights clause

It specifies whether to execute the name interpretation and authority of the object referred when executing the procedure from the perspective of DEFINER or the perspective of CURRENT_USER.
If <invoker rights clause> is omitted, then the default value is AUTHID DEFINER.

procedure characteristics

<procedure characteristics> specifies the characteristics of the procedure.
The redundant characteristics are not allowed.
For more information, refer to Routine Characteristics.

routine body

Description

It defines a schema-level SQL procedure. The created procedure can be called from CALL statement, anonymous block, or other procedure/ function.
The definition of a procedure can be viewed in ROUTINES table of INFORMATION_SCHEMA. The definition of a procedure parameter can be viewed in PARAMETERS table of INFORMATION_SCHEMA.
If a procedure becomes temporarily unstable due to absences of related objects, then it can try to recreate a plan by using ALTER PROCEDURE statement.
The created procedure can be dropped by using DROP PROCEDURE statement.
The maximum number of procedures to be created is not limited. Therefore, they can be created as many as the storage space is available.

Examples

gSQL> CREATE OR REPLACE PROCEDURE PROC1( A1 INTEGER, A2 INTEGER )

  IS
    V1 INTEGER;
  BEGIN

    SELECT COUNT(*)
      INTO V1
      FROM T1
      WHERE T1.I1 >= A1 AND T1.I1 <= A2;

    DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 );
  END;
  /

Procedure created.

Compatibility

The SQL standard does not define the following clauses.
SQL standard compatibility

Feature ID

Description

Compatibility

T471

Result sets return value

X

T341

Overloading of SQL-invoked functions and SQL-invoked procedures

X

S023

Basic structured types

X

S241

Transform functions

X

S024

Enhanced structured types

X

T571

Array-returning external SQL-invoked functions

X

T572

Multiset-returning external SQL-invoked functions

X

S201

SQL routines on arrays

X

S202

SQL-invoked routines on multisets

X

T323

Explicit security for external routines

X

S231

Structured type locators

X

S232

Array locators

X

S233

Multiset locators

X

T041

Basic LOB data type support

X

S027

Create method by specific method name

X

T041

Basic LOB data type support

X

T324

Explicit security for SQL routines

O

T326

Table functions

O

T651

SQL-schema statements in SQL routines

X

T652

SQL-dynamic statements in SQL routines

O

T653

SQL-schema statements in external routines

X

T654

SQL-dynamic statements in external routines

X

T655

Cyclically dependent routines

X

T272

Enhanced savepoint management

X

T522

Default values for IN parameters of SQL-invoked procedures

O

B121

Routine language Ada

X

B122

Routine language C

O

B123

Routine language COBOL

X

B124

Routine language Fortran

X

B125

Routine language MUMPS

X

B126

Routine language Pascal

X

B127

Routine language PL/I

X

B128

Routine language SQL

O

B129

Routine language Ada: VARCHAR and NUMERIC support

X

For More Information

Refer to the following.

CREATE TRIGGER

Function

It creates a trigger.

Syntax

<create trigger statement> ::=
      CREATE [ OR REPLACE ] TRIGGER <trigger name>
      <trigger action time>
      <trigger event list> ON <table name>
      [ REFERENCING <transition table or variable list> ]
      <triggered action>
      ;

<trigger action time> ::=
        BEFORE 
      | AFTER

<trigger event list> ::=
      <trigger event> [ OR <trigger event> [ ... ] ]

<trigger event> ::=
        INSERT
      | DELETE
      | UPDATE [ OF <trigger column list> ]

<trigger column list> ::=
      <column name list> 

<column name list> ::=
     <column name> [ { <comma> <column name> }... ]

<transition table or variable list> ::=
        OLD [ ROW ] [ AS ] <old transition variable name>
      | NEW [ ROW ] [ AS ] <new transition variable name>
      | OLD TABLE [ AS ] <old transition table name>
      | NEW TABLE [ AS ] <new transition table name>

<triggered action> ::=
        [ FOR EACH { ROW | STATEMENT } ]
        [ <trigger enforcement> ]
        [ <triggered when clause> ]
        <trigger body>

<trigger enforcement> ::=
        ENABLE
      | DISABLE
      | ENFORCED
      | NOT ENFORCED

<triggered when clause> ::=
      WHEN <left paren> <search condition> <right paren>

<trigger body> ::= 
        <PSM block>
      | CALL <procedure name>

Invocation and Access Rules

The user must meet the following conditions to execute the <create trigger statement>.

Syntax Rules and Parameters

OR REPLACE

If a trigger with the same name already exists, the existing trigger is replaced with the new one.

<trigger name>

It is a name of trigger to be created, and must be unique within the schema.
It can define the schema to which the trigger belongs, such as schema_name.trigger_name. If schema_name is omitted, the default schema name of the user performing the statement is used.
The trigger name must be less than 128 bytes in length.

<trigger action time>

It defines the timing of trigger execution as follows:

<trigger event>

The execution of a trigger is determined by the DML performed on the event table.
One or more <trigger event>s can be specified.
The same <trigger event> cannot be specified more than once.
The types of <trigger event> are as follows:

<trigger column list>

It is a clause that specifies the trigger to execute only when the designated columns are updated.
Column names cannot be duplicated.
All specified columns must actually exist in the event table.

<table name>

It is the name of the base table object that is the target of the DML event detected by the trigger.
The base table name may include a schema name in the form of schema_name.table_name.
If the schema name is omitted, the default schema name of the user executing the statement is used.

REFERENCING <transition table or variable list>

The REFERENCING transition tables and transition variables are used in a trigger to reference data before and after the execution of DML operations.
After the REFERENCING keyword, transition tables or transition variables can be declared as shown below, and duplicate declarations are not allowed.
The names of OLD transition table, NEW transition table, OLD transition variable, and NEW transition variable cannot be duplicated.
Transition tables or transition variables declared by the user can only be used within the <triggered action>.

FOR EACH ROW/ FOR EACH STATEMENT

It specifies the execution unit of the trigger.
If not specified, the default is FOR EACH STATEMENT.

<trigger enforcement>

It specifies whether the trigger is created in an enabled or disabled state.
If not specified, the trigger is created in the enabled state by default.
ENABLE and ENFORCED have the same meaning.
DISABLE and NOT ENFORCED have the same meaning.

<triggered when clause>

It specifies a conditional clause that controls whether the trigger is executed.
If the result of the <triggered when clause> is TRUE, the trigger is executed; if the result is FALSE, it is not executed.
If no <triggered when clause> is specified, the trigger is always executed.
Functions can be used in the expression of the <triggered when clause>, but such functions must not have the MODIFIES SQL DATA attribute.

<trigger body>

It defines the statements to be executed by the trigger.
It consists of a PSM block or a CALL statement.

Description

When a trigger is defined, it is executed when a DML event occurs on the specified base table.
The definition of a trigger can be viewed in the TRIGGERS table of the INFORMATION_SCHEMA.
If a related object is modified and the trigger becomes invalid, it can be recompiled using the ALTER TRIGGER .. COMPILE statement.

The name of a created trigger can also be changed using the ALTER TRIGGER .. RENAME statement.

The activation state of a trigger can be changed by executing the following statements:

A created trigger can be dropped using the DROP TRIGGER statement.
If the event table object is dropped, the corresponding trigger is also dropped.

Examples

-- System log table
gSQL>
CREATE TABLE system_log( log_time   DATE,
                         action     VARCHAR2(100),
                         table_name VARCHAR2(50) );

Table created.

-- Order status history table
gSQL>
CREATE TABLE order_status_history( order_id   NUMBER,
                                   old_status VARCHAR2(20),
                                   new_status VARCHAR2(20),
                                   changed_at DATE );

Table created.

-- Administrator notification table
gSQL>
CREATE TABLE admin_notifications( message    VARCHAR2(200),
                                  created_at DATE );

Table created.

-- ORDERS table
gSQL>
CREATE TABLE orders( order_id    NUMBER PRIMARY KEY,
                     customer_id NUMBER,
                     amount      NUMBER,
                     status      VARCHAR2(20),
                     created_at  DATE,
                     updated_at  DATE );

Table created.
-- DML attempt logging
gSQL>
CREATE OR REPLACE TRIGGER trg_orders_check_before_stmt
BEFORE INSERT OR UPDATE OR DELETE ON orders
DECLARE 
  dml_event VARCHAR(10);
BEGIN
  IF INSERTING THEN
    dml_event := 'INSERT';
  END IF;

  IF UPDATING THEN
    dml_event := 'UPDATE';
  END IF;

  IF DELETING THEN
    dml_event := 'DELETE';
  END IF;

  INSERT INTO system_log VALUES( SYSDATE, dml_event, 'ORDERS' );
END;
/

Trigger created.

-- Automatically set created_at on INSERT
gSQL>
CREATE OR REPLACE TRIGGER trg_orders_set_created_at
BEFORE INSERT ON orders
REFERENCING NEW ROW AS n_row
FOR EACH ROW
BEGIN
  n_row.created_at := NVL(n_row.created_at, SYSDATE);
END;
/

Trigger created.

-- Save history on status changes
gSQL>
CREATE OR REPLACE TRIGGER trg_orders_status_audit
AFTER UPDATE OF status ON orders
REFERENCING OLD ROW AS o_row
            NEW ROW AS n_row            
FOR EACH ROW
WHEN( o_row.status IS DISTINCT FROM n_row.status )
BEGIN
  INSERT INTO order_status_history VALUES (o_row.order_id, o_row.status, n_row.status, SYSDATE);
END;
/

Trigger created.

-- Send notification after status changes
gSQL> 
CREATE OR REPLACE TRIGGER trg_orders_bulk_update_log
AFTER UPDATE ON orders
BEGIN
  INSERT INTO admin_notifications VALUES ('Order status in the ORDERS table has been updated.', SYSDATE);
END;
/

Trigger created.
-- BEFORE STATEMENT and BEFORE ROW triggers executed on INSERT
gSQL> 
INSERT INTO orders (order_id, customer_id, amount, status)
VALUES (1, 1001, 50000, 'Order Received ');

1 row created.

gSQL> SELECT * FROM system_log;

LOG_TIME   ACTION TABLE_NAME
---------- ------ ----------
2025-08-08 INSERT ORDERS    

1 row selected.
  
gSQL> SELECT * FROM orders;

ORDER_ID CUSTOMER_ID AMOUNT STATUS          CREATED_AT UPDATED_AT
-------- ----------- ------ --------------- ---------- ----------
       1        1001  50000 Order Received  2025-08-13 null       

1 row selected.

-- BEFORE STATEMENT, AFTER ROW and AFTER STATEMENT triggers executed on UPDATE
gSQL> 
UPDATE orders
   SET status = 'Shipped', updated_at = SYSDATE
 WHERE order_id = 1;

1 row updated.

gSQL> SELECT * FROM system_log;

LOG_TIME   ACTION TABLE_NAME
---------- ------ ----------
2025-08-08 INSERT ORDERS    
2025-08-08 UPDATE ORDERS    

2 rows selected.

gSQL> SELECT * FROM order_status_history;

ORDER_ID OLD_STATUS      NEW_STATUS CHANGED_AT
-------- --------------- ---------- ----------
       1 Order Received  Shipped    2025-08-13

1 row selected.

gSQL> SELECT * FROM admin_notifications;

MESSAGE                                            CREATED_AT
-------------------------------------------------- ----------
Order status in the ORDERS table has been updated. 2025-08-13

1 row selected.

gSQL> SELECT * FROM orders;

ORDER_ID CUSTOMER_ID AMOUNT STATUS  CREATED_AT UPDATED_AT
-------- ----------- ------ ------- ---------- ----------
       1        1001  50000 Shipped 2025-08-13 2025-08-13

1 row selected.
gSQL>
CREATE TABLE employees( emp_id NUMBER PRIMARY KEY,
                        name   VARCHAR2(50),
                        salary NUMBER );

Table created.

gSQL> INSERT INTO employees VALUES (1001, 'Alice', 5000);

1 row created.

gSQL> INSERT INTO employees VALUES (1002, 'Bob', 6000);

1 row created.

gSQL> COMMIT;

Commit complete.

gSQL>
CREATE TABLE audit_log( EMP_ID     NUMBER,
                        OLD_SALARY NUMBER,
                        NEW_SALARY NUMBER,
                        CHANGED_AT TIMESTAMP );

Table created.
gSQL>
CREATE OR REPLACE PROCEDURE log_salary_change(
         p_emp_id     IN NUMBER,
         p_old_salary IN NUMBER,
         p_new_salary IN NUMBER )
AS
BEGIN
  INSERT INTO audit_log VALUES( p_emp_id,
                                p_old_salary,
                                p_new_salary,
                                SYSTIMESTAMP );
END;
/

Procedure created.

gSQL>
CREATE OR REPLACE TRIGGER trg_log_salary_change
AFTER UPDATE OF salary ON employees
REFERENCING OLD ROW AS o_row
            NEW ROW AS n_row
FOR EACH ROW
CALL log_salary_change( o_row.emp_id, o_row.salary, n_row.salary );
/

Trigger created.
gSQL>
UPDATE employees
   SET salary = 5500
 WHERE emp_id = 1001;

1 row updated.

gSQL> SELECT * FROM audit_log;

EMP_ID OLD_SALARY NEW_SALARY CHANGED_AT                
------ ---------- ---------- --------------------------
  1001       5000       5500 2025-08-08 17:21:35.522820

1 row selected.
gSQL>
CREATE TABLE employees( emp_id INTEGER PRIMARY KEY,
                        name   VARCHAR(100),
                        salary INTEGER );

Table created.

gSQL> INSERT INTO employees VALUES( 101, 'Alice', 8000 );

1 row created.

gSQL> COMMIT;

Commit complete.

gSQL>
CREATE TABLE salary_log( emp_id     INTEGER,
                         old_salary INTEGER, 
                         new_salary INTEGER,
                         log_time   TIMESTAMP );

Table created.

gSQL> COMMIT;

Commit complete.
gSQL>
CREATE OR REPLACE TRIGGER trg_log_high_salary
AFTER UPDATE ON employees
REFERENCING OLD ROW AS o_row 
            NEW ROW AS n_row
FOR EACH ROW
WHEN( n_row.salary >= 10000 AND n_row.salary > o_row.salary )
BEGIN
  INSERT INTO salary_log VALUES( o_row.emp_id,
                                 o_row.salary,
                                 n_row.salary,
                                 CURRENT_TIMESTAMP );
END;
/

Trigger created.
-- If the WHEN clause condition is not satisfied → The trigger body is not executed
gSQL> UPDATE employees SET salary = 9000;

1 row updated.

gSQL> SELECT * FROM salary_log;

no rows selected.
-- If the WHEN clause condition is satisfied → The trigger body is executed
gSQL> UPDATE employees SET salary = 12000;

1 row updated.

gSQL> SELECT * FROM salary_log;

EMP_ID OLD_SALARY NEW_SALARY LOG_TIME                  
------ ---------- ---------- --------------------------
   101       8000      12000 2025-08-08 17:39:38.391413

1 row selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T200

Trigger DDL

O

T211

Basic trigger capability

O

T212

Enhanced trigger capability

O

T213

INSTEAD OF triggers

X

T214

BEFORE triggers

O

T215

AFTER triggers

O

T216

Ability to require true search condition before trigger is invoked

O

T217

TRIGGER privilege

O

T218

Multiple triggers for the same event executed in the order created

O

For More Information

Refer to the following.

DROP FUNCTION

Function

It drops a function.

Syntax

<drop function statement> ::=
    DROP FUNCTION [ IF EXISTS ] func_name
    ;

Invocation and Access Rules

One of the following privileges is required to perform <drop function statement>.

Syntax Rules and Parameters

IF EXISTS

Even when the function does not exist, an error does not occur.

FUNC NAME

It is the function name to be dropped.
It can define the schema to which the function belongs, such as schema_name.func_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

Description

It drops a specified schema-level function.

Examples

gSQL> CREATE OR REPLACE FUNCTION FUNC1
RETURN INTEGER
 IS
    V1 INTEGER;
  BEGIN
    V1 := 10;
    RETURN V1;
  END;
  /

Function created.


COMMIT;

Commit complete.
gSQL> DROP FUNCTION FUNC1;

Function dropped.

Compatibility

The SQL standard does not define the following clauses.
SQL standard compatibility

Feature ID

Description

Compatibility

F032

CASCADE drop behavior

X

S024

Enhanced structured types

X

For More Information

Refer to the following.

DROP LIBRARY

Function

It drops a library.

Syntax

<drop library statement> ::=
    DROP LIBRARY [ IF EXISTS ] <library name>
    ;

Invocation and Access Rules

One of the following privileges is required to perform <drop library statement>.

Syntax Rules and Parameters

IF EXISTS

Even when the library does not exist, an error does not occur.

library name

It is the library name to be dropped.
It can define the schema to which the library belongs, such as <schema name>.<library name>. If <schema name> is omitted, the default schema name of the user performing the statement is used.

Description

It drops a specified library.

Examples

gSQL>
CREATE LIBRARY lib1 AS 'add.so';
/

Library created.

gSQL> DROP LIBRARY lib1;

Library dropped.

gSQL> DROP LIBRARY IF EXISTS lib2;

Library dropped.

Compatibility

It is not defined in the SQL standard.

For More Information

Refer to CREATE LIBRARY.

DROP PACKAGE

Function

It drops a package (of only body or both spec/ body).

Syntax

<drop package statement> ::=
    DROP PACKAGE [BODY] [ IF EXISTS ] package_name
    ;

Invocation and Access Rules

One of the following privileges is required to perform <drop package statement>.

Syntax Rules and Parameters

BODY

It drops only the body object in the package of the given name. If the keyword BODY is not specified it drops both the package specification and the body.

IF EXISTS

Even when the package does not exist, an error does not occur.

PACKAGE NAME

It is the package name to be dropped.
It can define the schema to which the package belongs, such as schema_name.package_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

Description

It drops the specified package object.

Examples

CREATE PACKAGE PKG1
  IS
    V1 INTEGER;
    FUNCTION FUNC1 (A1 INTEGER) RETURN INTEGER;
  END;
  /

Package created.

DROP PACKAGE IF EXISTS PKG1;

Package dropped.

Compatibility

It is DRO MODULE statement in the SQL standard.

For More Information

Refer to the following.

DROP PROCEDURE

Function

It drops a procedure.

Syntax

<drop procedure statement> ::=
    DROP PROCEDURE [ IF EXISTS ] proc_name
    ;

Invocation and Access Rules

One of the following privileges is required to perform <drop procedure statement>.

Syntax Rules and Parameters

IF EXISTS

Even when the procedure does not exist, an error does not occur.

PROC NAME

It is the procedure name to be dropped.
It can define the schema to which the procedure belongs, such as schema_name.proc_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

Description

It drops a specified schema-level procedure.

Examples

gSQL> CREATE OR REPLACE PROCEDURE PROC1( A1 INTEGER, A2 INTEGER )
  IS
    V1 INTEGER;
  BEGIN

    SELECT COUNT(*)
      INTO V1
      FROM T1
      WHERE T1.I1 >= A1 AND T1.I1 <= A2;

    DBMS_OUTPUT.PUT_LINE( 'V1 = ' || V1 );
  END;
  /

Procedure created.


COMMIT;

Commit complete.


gSQL> DROP PROCEDURE PROC1;

Procedure dropped.

Compatibility

The SQL standard does not define the following clauses.
SQL standard compatibility

Feature ID

Description

Compatibility

F032

CASCADE drop behavior

X

S024

Enhanced structured types

X

For More Information

Refer to the following.

DROP TRIGGER

Function

It drops a trigger.

Syntax

<drop trigger statement> ::=
    DROP TRIGGER [ IF EXISTS ] <trigger name>
    ;

Invocation and Access Rules

One of the following privileges is required to perform <drop trigger statement>.

Syntax Rules and Parameters

IF EXISTS

Even when the trigger does not exist, an error does not occur.

<trigger name>

It is a name of trigger to be dropped. 
It can define the schema to which the trigger belongs, such as schema_name.trigger_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

Description

It drops the specified trigger.
If the event table is dropped using DROP TABLE, the corresponding trigger is also dropped.

Examples

gSQL>
CREATE OR REPLACE TRIGGER trg_orders_status_audit
AFTER UPDATE OF status ON orders
REFERENCING OLD ROW AS o_row
            NEW ROW AS n_row            
FOR EACH ROW
WHEN( o_row.status IS DISTINCT FROM n_row.status )
BEGIN
  INSERT INTO order_status_history VALUES (o_row.order_id, o_row.status, n_row.status, SYSDATE);
END;
/

Trigger created.

gSQL> DROP TRIGGER trg_orders_status_audit;

Trigger dropped.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T200

Trigger DDL

O

For More Information

Refer to the following.