The STANDARD package defines the PL environment. The package specification declares public types, variables, exceptions, and subprograms that can be used in a PL program.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
The components of the STANDARD package are directly visible to applications. You can call the components from database triggers, stored subprograms, or OceanBase tools without adding a prefix to the package name.
Note
The system functions of OceanBase Database are not registered in the STANDARD package. At present, the STANDARD package supports only predefined data types, constants, and exceptions.
Predefined data types
The following table shows the predefined data subtypes supported by the STANDARD package.
| Predefined subtype | Return type |
|---|---|
| DOUBLE PRECISION | FLOAT |
| DEC | DECIMAL |
| STRING | VARCHAR2(32760) |
| LONG | VARCHAR2(32760) |
| CHARACTER VARYING | VARCHAR(32767) |
| CHAR VARYING | VARCHAR(32767) |
| CHARACTER LARGE OBJECT | CLOB |
| BINARY LARGE OBJECT | BLOB |
| CHAR LARGE OBJECT | CLOB |
| TIMESTAMP_UNCONSTRAINED | TIMESTAMP(9) |
| TIMESTAMP_TZ_UNCONSTRAINED | TIMESTAMP(9) WITH TIME ZONE |
| YMINTERVAL_UNCONSTRAINED | INTERVAL YEAR(9) TO MONTH |
| DSINTERVAL_UNCONSTRAINED | INTERVAL DAY(9) TO SECOND(9) |
| TIMESTAMP_LTZ_UNCONSTRAINED | TIMESTAMP(9) WITH LOCAL TIME ZONE |
The following table shows generic data types that are specific to the STANDARD package and some other packages. Note that generic data types are not a part of PL.
| Generic data type | Return type |
|---|---|
| <ADT_1> | RECORD (DUMMY CHAR(1)) |
| <RECORD_1> | RECORD (DUMMY CHAR(1)) |
| <TUPLE_1> | RECORD (DUMMY CHAR(1)) |
| <VARRAY_1> | VARRAY(1) OF CHAR(1) |
| <V2_TABLE_1> | TABLE OF CHAR(1) INDEX BY BINARY_INTEGER |
| <TABLE_1> | TABLE OF CHAR(1) |
| <COLLECTION_1> | TABLE OF CHAR(1) |
| <REF_CURSOR_1> | REF CURSOR |
| <TYPED_TABLE> | TABLE OF <ADT_1> |
| <ADT_WITH_OID> | <TYPED_TABLE> |
The PL compiler uses the following generic index table type to instantiate array attributes.
| Generic index table type | Return type |
|---|---|
| SYS $ INT_V2TABLE | An integer index table represented by binary_integer. |
The PL compiler uses the following record types and corresponding generic index table types to record exceptions that are raised in the execution of the batch-binding FORALL statement during table running.
| Record type | Return type |
|---|---|
| SYS$BULK_ERROR_RECORD | RECORD (ERROR_INDEX PLS_INTEGER, ERROR_CODE PLS_INTEGER) |
| SYS$REC_V2TABLE | TABLE OF CHAR(1) |
The following table lists all opaque generic data types.
| Opaque** generic data type** | Return type |
|---|---|
| <ASSOC_ARRAY_1> | TABLE OF CHAR(1) INDEX BY VARCHAR2(1) |
Predefined exceptions
| Predefined exception | Pragma |
|---|---|
| CURSOR_ALREADY_OPEN | EXCEPTION_INIT(CURSOR_ALREADY_OPEN, '-5589') |
| DUP_VAL_ON_INDEX | EXCEPTION_INIT(DUP_VAL_ON_INDEX, '-5024') |
| TIMEOUT_ON_RESOURCE | EXCEPTION_INIT(TIMEOUT_ON_RESOURCE, '-5848') |
| INVALID_CURSOR | EXCEPTION_INIT(INVALID_CURSOR, '-5844') |
| NOT_LOGGED_ON | EXCEPTION_INIT(NOT_LOGGED_ON, '-5846') |
| LOGIN_DENIED | EXCEPTION_INIT(LOGIN_DENIED, '-5845') |
| NO_DATA_FOUND | EXCEPTION_INIT(NO_DATA_FOUND, '-4026') |
| ZERO_DIVIDE | EXCEPTION_INIT(ZERO_DIVIDE, '-4333') |
| INVALID_NUMBER | EXCEPTION_INIT(INVALID_NUMBER, '-5114') |
| TOO_MANY_ROWS | EXCEPTION_INIT(TOO_MANY_ROWS, '-5294') |
| STORAGE_ERROR | EXCEPTION_INIT(STORAGE_ERROR, '-5842') |
| PROGRAM_ERROR | EXCEPTION_INIT(PROGRAM_ERROR, '-5840') |
| VALUE_ERROR | EXCEPTION_INIT(VALUE_ERROR, '-5677') |
| ACCESS_INTO_NULL | EXCEPTION_INIT(ACCESS_INTO_NULL, '-5837') |
| COLLECTION_IS_NULL | EXCEPTION_INIT(COLLECTION_IS_NULL , '-5838') |
| SUBSCRIPT_OUTSIDE_LIMIT | EXCEPTION_INIT(SUBSCRIPT_OUTSIDE_LIMIT,'-5843') |
| SUBSCRIPT_BEYOND_COUNT | EXCEPTION_INIT(SUBSCRIPT_BEYOND_COUNT ,'-5828') |
| ROWTYPE_MISMATCH | EXCEPTION_INIT(ROWTYPE_MISMATCH, '-5841') |
| SYS_INVALID_ROWID | EXCEPTION_INIT(SYS_INVALID_ROWID, '-5802') |
| SELF_IS_NULL | EXCEPTION_INIT(SELF_IS_NULL, '-5847') |
| CASE_NOT_FOUND | EXCEPTION_INIT(CASE_NOT_FOUND, '-5571') |
| NO_DATA_NEEDED | EXCEPTION_INIT(NO_DATA_NEEDED, '-5839') |