#

| Chapter 1 Oracle PL/SQL Built-ins Pocket Reference |

## 1.6 Nonprogram Elements

In addition to the definitions for the programs (shown earlier in the Section 1.3, "Built-in Packages" section), a package specification may contain nonprogram elements defined for that package. These elements may include constants, exceptions, record types, and index-by tables.

This section shows the nonprogram elements defined for each of the built-in packages.

### 1.6.1 DBMS_ALERT

>     maxwait CONSTANT INTEGER := 86400000;

### 1.6.2 DBMS_APPLICATION_INFO

>     set_session_longops_nohint
>       CONSTANT BINARY_INTEGER := -1;

### 1.6.3 DBMS_AQ

>     before            CONSTANT BINARY_INTEGER
>     browse            CONSTANT BINARY_INTEGER
>     expired           CONSTANT BINARY_INTEGER
>     first_message     CONSTANT BINARY_INTEGER
>     forever           CONSTANT BINARY_INTEGER
>     immediate         CONSTANT BINARY_INTEGER
>     locked            CONSTANT BINARY_INTEGER
>     never             CONSTANT BINARY_INTEGER
>     next_message      CONSTANT BINARY_INTEGER
>     next_transaction  CONSTANT BINARY_INTEGER
>     no_delay          CONSTANT BINARY_INTEGER
>     no_wait           CONSTANT BINARY_INTEGER
>     on_commit         CONSTANT BINARY_INTEGER
>     processed         CONSTANT BINARY_INTEGER
>     ready             CONSTANT BINARY_INTEGER
>     remove            CONSTANT BINARY_INTEGER
>     top               CONSTANT BINARY_INTEGER
>     waiting           CONSTANT BINARY_INTEGER

>     TYPE aq$_recipient_list_t IS TABLE OF sys.aq$_agent
>       INDEX BY BINARY_INTEGER;

>     TYPE message_properties_t IS RECORD
>        (priority        BINARY_INTEGER DEFAULT 1
>        ,delay           BINARY_INTEGER
>                              DEFAULT DBMS_AQ.no_delay
>        ,expiration      BINARY_INTEGER
>                              DEFAULT DBMS_AQ.never
>        ,correlation     VARCHAR2(128) DEFAULT NULL
>        ,attempts        BINARY_INTEGER
>        ,recipient_list  DBMS_AQ.aq$_recipient_list_t
>        ,exception_queue VARCHAR2(51) DEFAULT NULL
>        ,enqueue_time    DATE
>        ,state           BINARY_INTEGER);

>     TYPE enqueue_options_t IS RECORD
>        (visibility         BINARY_INTEGER
>                                DEFAULT DBMS_AQ.on_commit
>        ,relative_msgid     RAW(16) DEFAULT NULL
>        ,sequence_deviation BINARY_INTEGER DEFAULT NULL);

>     TYPE dequeue_options_t IS RECORD
>        (consumer_name  VARCHAR2(30) DEFAULT NULL
>        ,dequeue_mode   BINARY_INTEGER
>                             DEFAULT DBMS_AQ.remove
>        ,navigation     BINARY_INTEGER
>                             DEFAULT DBMS_AQ.next_message
>        ,visibility     BINARY_INTEGER
>                             DEFAULT DBMS_AQ.on_commit
>        ,wait           BINARY_INTEGER
>                             DEFAULT DBMS_AQ.forever
>        ,msgid          RAW(16) DEFAULT NULL
>        ,correlation    VARCHAR2(128) DEFAULT NULL);

### 1.6.4 DBMS_AQADM

>     exception_queue CONSTANT BINARY_INTEGER := 1;
>     infinite        CONSTANT BINARY_INTEGER := -1;
>     none            CONSTANT BINARY_INTEGER := 0;
>     normal_queue    CONSTANT BINARY_INTEGER := 0;
>     transactional   CONSTANT BINARY_INTEGER := 1;

>     TYPE aq$_subscriber_list_t IS TABLE OF sys.aq$_agent
>       INDEX BY BINARY_INTEGER;

### 1.6.5 DBMS_DESCRIBE

>     TYPE varchar2_table IS TABLE OF VARCHAR2(30)
>       INDEX BY BINARY_INTEGER;

>     TYPE number_table IS TABLE OF NUMBER
>       INDEX BY BINARY_INTEGER;

### 1.6.6 DBMS_LOB

>     invalid_argval EXCEPTION;
>     PRAGMA EXCEPTION_INIT(invalid_argval, -21560);

>     access_error EXCEPTION;
>     PRAGMA EXCEPTION_INIT(access_error, -22925);

>     noexist_directory EXCEPTION;
>     PRAGMA EXCEPTION_INIT(noexist_directory, -22285);

>     nopriv_directory EXCEPTION;
>     PRAGMA EXCEPTION_INIT(nopriv_directory, -22286);

>     invalid_directory EXCEPTION;
>     PRAGMA EXCEPTION_INIT(invalid_directory, -22287);

>     operation_failed EXCEPTION;
>     PRAGMA EXCEPTION_INIT(operation_failed, -22288);

>     unopened_file EXCEPTION;
>     PRAGMA EXCEPTION_INIT(unopened_file, -22289);

>     open_toomany EXCEPTION;
>     PRAGMA EXCEPTION_INIT(open_toomany, -22290);

>     file_readonly CONSTANT BINARY_INTEGER := 0;
>     lobmaxsize    CONSTANT INTEGER := 4294967295;
>     invalid_argval_num     NUMBER := 21560;
>     access_error_num       NUMBER := 22925;
>     noexist_directory_num  NUMBER := 22285;
>     nopriv_directory_num   NUMBER := 22286;
>     invalid_directory_num  NUMBER := 22287;
>     operation_failed_num   NUMBER := 22288;
>     unopened_file_num      NUMBER := 22289;
>     open_toomany_num       NUMBER := 22290;

### 1.6.7 DBMS_LOCK

>     nl_mode  CONSTANT INTEGER := 1;
>     ss_mode  CONSTANT INTEGER := 2;
>     sx_mode  CONSTANT INTEGER := 3;
>     s_mode   CONSTANT INTEGER := 4;
>     ssx_mode CONSTANT INTEGER := 5;
>     x_mode   CONSTANT INTEGER := 6;
>     maxwait  CONSTANT INTEGER := 32767;

### 1.6.8 DBMS_OUTPUT

>     TYPE chararr IS TABLE OF VARCHAR2(255)
>       INDEX BY BINARY_INTEGER;

### 1.6.9 DBMS_PIPE

>     maxwait CONSTANT INTEGER := 86400000;

### 1.6.10 DBMS_ROWID

>     rowid_invalid EXCEPTION;
>     PRAGMA EXCEPTION_INIT(rowid_invalid, -1410);

>     rowid_bad_block EXCEPTION;
>     PRAGMA EXCEPTION_INIT(rowid_bad_block, -28516);

>     rowid_convert_external CONSTANT INTEGER := 1;
>     rowid_convert_internal CONSTANT INTEGER := 0;
>     rowid_is_invalid       CONSTANT INTEGER := 1;
>     rowid_is_valid         CONSTANT INTEGER := 0;
>     rowid_object_undefined CONSTANT INTEGER := 0;
>     rowid_type_extended    CONSTANT INTEGER := 1;
>     rowid_type_restricted  CONSTANT INTEGER := 0;

### 1.6.11 DBMS_SQL

>     inconsistent_type EXCEPTION;
>     PRAGMA EXCEPTION_INIT(inconsistent_type, -6562);

>     v6     CONSTANT INTEGER := 0;
>     native CONSTANT INTEGER := 1;
>     v7     CONSTANT INTEGER := 2;

>     TYPE varchar2s IS TABLE OF VARCHAR2(256)
>       INDEX BY BINARY_INTEGER;

>     TYPE desc_rec IS RECORD
>        (col_type BINARY_INTEGER := 0
>        ,col_max_len BINARY_INTEGER := 0
>        ,col_name varchar2(32) := ''
>        ,col_name_len BINARY_INTEGER := 0
>        ,col_schema_name VARCHAR2(32) := ''
>        ,col_schema_name_len BINARY_INTEGER := 0
>        ,col_precision BINARY_INTEGER := 0
>        ,col_scale BINARY_INTEGER := 0
>        ,col_charsetid BINARY_INTEGER := 0
>        ,col_charsetform BINARY_INTEGER := 0
>        ,col_null_ok BOOLEAN := TRUE);

>     TYPE desc_tab IS TABLE OF desc_rec
>       INDEX BY BINARY_INTEGER;

>     TYPE number_table IS TABLE OF NUMBER
>       INDEX BY BINARY_INTEGER;

>     TYPE varchar2_table IS TABLE OF VARCHAR2(2000)
>       INDEX BY BINARY_INTEGER;

>     TYPE date_table IS TABLE OF DATE
>       INDEX BY BINARY_INTEGER;

>     TYPE blob_table IS TABLE OF BLOB
>       INDEX BY BINARY_INTEGER;

>     TYPE clob_table IS TABLE OF CLOB
>       INDEX BY BINARY_INTEGER;

>     TYPE bfile_table IS TABLE OF BFILE
>       INDEX BY BINARY_INTEGER;

### 1.6.12 DBMS_TRANSACTION

>     discrete_transaction_failed EXCEPTION;
>         -8175);

>     consistent_read_failure EXCEPTION;
>     PRAGMA EXCEPTION_INIT(consistent_read_failure, -8176);

### 1.6.13 DBMS_UTILITY

>     TYPE uncl_array IS TABLE OF VARCHAR2(227)
>       INDEX BY BINARY_INTEGER;

>     TYPE name_array IS TABLE OF VARCHAR2(30)
>       INDEX BY BINARY_INTEGER;

>     TYPE dblink_array IS TABLE OF VARCHAR2(128)
>       INDEX BY BINARY_INTEGER;

>     TYPE index_table_type IS TABLE OF BINARY_INTEGER
>       INDEX BY BINARY_INTEGER;

>     TYPE number_array IS TABLE OF NUMBER
>       INDEX BY BINARY_INTEGER;

### 1.6.14 UTL_FILE

>     internal_error EXCEPTION;
>     invalid_filehandle EXCEPTION;
>     invalid_mode EXCEPTION;
>     invalid_operation EXCEPTION;
>     invalid_path EXCEPTION;
>     read_error EXCEPTION;
>     write_error EXCEPTION;

>     TYPE file_type IS RECORD (id BINARY_INTEGER);

### 1.6.15 STANDARD

Following are the nonprogram elements for the STANDARD package.

#### 1.6.15.1 Exceptions

>     dup_val_on_index EXCEPTION;
>     PRAGMA EXCEPTION_INIT(dup_val_on_index, '-0001');

>     timeout_on_resource EXCEPTION;
>     PRAGMA EXCEPTION_INIT(timeout_on_resource, '-0051');

>     invalid_cursor EXCEPTION;
>     PRAGMA EXCEPTION_INIT(invalid_cursor, '-1001');

>     not_logged_on EXCEPTION;
>     PRAGMA EXCEPTION_INIT(not_logged_on, '-1012');

>     login_denied EXCEPTION;
>     PRAGMA EXCEPTION_INIT(login_denied, '-1017');

>     too_many_rows EXCEPTION;
>     PRAGMA EXCEPTION_INIT(too_many_rows, '-1422');

>     zero_divide EXCEPTION;
>     PRAGMA EXCEPTION_INIT(zero_divide, '-1476');

>     invalid_number EXCEPTION;
>     PRAGMA EXCEPTION_INIT(invalid_number, '-1722');

>     storage_error EXCEPTION;
>     PRAGMA EXCEPTION_INIT(storage_error, '-6500');

>     program_error EXCEPTION;
>     PRAGMA EXCEPTION_INIT(program_error, '-6501');

>     value_error EXCEPTION;
>     PRAGMA EXCEPTION_INIT(value_error, '-6502');

>     rowtype_mismatch EXCEPTION;
>     PRAGMA EXCEPTION_INIT(rowtype_mismatch, '-6504');

>     cursor_already_open EXCEPTION;
>     PRAGMA EXCEPTION_INIT(cursor_already_open, '-6511');

>     access_into_null EXCEPTION;
>     PRAGMA EXCEPTION_INIT(access_into_null, '-6530');

>     collection_is_null EXCEPTION;
>     PRAGMA EXCEPTION_INIT(collection_is_null , '-6531');

>     subscript_outside_limit EXCEPTION;
>     PRAGMA EXCEPTION_INIT(subscript_outside_limit,'-6532');

>     subscript_beyond_count EXCEPTION;
>     PRAGMA EXCEPTION_INIT(subscript_beyond_count ,'-6533');

>     no_data_found EXCEPTION;
>     PRAGMA EXCEPTION_INIT(no_data_found, 100);

#### 1.6.15.2 Numeric datatypes

>     TYPE NUMBER IS NUMBER_BASE;
>     SUBTYPE BINARY_INTEGER IS INTEGER RANGE '-
>         2147483647'..2147483647;
>     SUBTYPE CURSOR_HANDLE IS BINARY_INTEGER RANGE 0..255;
>     SUBTYPE DEC IS DECIMAL;
>     SUBTYPE DECIMAL IS NUMBER(38,0);
>     SUBTYPE "DOUBLE PRECISION" IS FLOAT;
>     SUBTYPE FLOAT IS NUMBER; -- NUMBER(126)
>     SUBTYPE INT IS INTEGER;
>     SUBTYPE INTEGER IS NUMBER(38,0);
>     SUBTYPE NATURAL IS BINARY_INTEGER RANGE 0..2147483647;
>     SUBTYPE NATURALN IS NATURAL NOT NULL;
>     SUBTYPE NUMERIC IS DECIMAL;
>     SUBTYPE PLS_INTEGER IS BINARY_INTEGER;
>     SUBTYPE POSITIVE IS BINARY_INTEGER RANGE
>         1..2147483647;
>     SUBTYPE POSITIVEN IS POSITIVE NOT NULL;
>     SUBTYPE REAL IS FLOAT; -- FLOAT(63)
>     SUBTYPE SIGNTYPE IS BINARY_INTEGER RANGE '-1'..1;  --
>         for SIGN functions
>     SUBTYPE SMALLINT IS NUMBER(38,0);

#### 1.6.15.3 Character datatypes

>     TYPE VARCHAR2 IS NEW CHAR_BASE;
>     SUBTYPE CHAR IS VARCHAR2;
>     SUBTYPE CHARACTER IS CHAR;
>     SUBTYPE "CHAR VARYING" IS VARCHAR;
>     SUBTYPE "CHARACTER VARYING" IS VARCHAR;
>     SUBTYPE LONG IS VARCHAR2(32760);
>     SUBTYPE "LONG RAW" IS RAW(32760);
>     SUBTYPE RAW IS VARCHAR2;
>     SUBTYPE ROWID IS VARCHAR2(256);
>     SUBTYPE STRING IS VARCHAR2;
>     SUBTYPE VARCHAR IS VARCHAR2;
>     SUBTYPE "NCHAR" IS CHAR CHARACTER SET NCHAR_CS;
>     SUBTYPE "NCHAR VARYING" IS VARCHAR CHARACTER SET
>         NCHAR_CS;
>     SUBTYPE "NCLOB" IS CLOB CHARACTER SET NCHAR_CS;
>     SUBTYPE "NVARCHAR2" IS VARCHAR2 CHARACTER SET NCHAR_CS;
>     SUBTYPE "NATIONAL CHAR VARYING" IS
>         VARCHAR CHARACTER SET NCHAR_CS;
>     SUBTYPE "NATIONAL CHAR" IS CHAR CHARACTER SET NCHAR_CS;
>     SUBTYPE "NATIONAL CHARACTER LARGE OBJECT" IS
>         CLOB CHARACTER SET NCHAR_CS;
>     SUBTYPE "NATIONAL CHARACTER VARYING" IS
>         VARCHAR CHARACTER SET NCHAR_CS;
>     SUBTYPE "NATIONAL CHARACTER" IS
>         CHAR CHARACTER SET NCHAR_CS;
>     SUBTYPE "NCHAR LARGE OBJECT" IS
>         CLOB CHARACTER SET NCHAR_CS;

#### 1.6.15.4 LOB datatypes

>     TYPE BFILE IS BFILE_BASE;
>     TYPE BLOB IS BLOB_BASE;
>     TYPE CLOB IS CLOB_BASE;
>     SUBTYPE "BINARY LARGE OBJECT" IS BLOB;
>     SUBTYPE "CHAR LARGE OBJECT" IS CLOB;
>     SUBTYPE "CHARACTER LARGE OBJECT" IS CLOB;

#### 1.6.15.5 Other datatypes

>     TYPE BOOLEAN IS (FALSE, TRUE);
>     TYPE DATE IS DATE_BASE;
>     TYPE MLSLABEL IS NEW CHAR_BASE;

1.5 RESTRICT REFERENCES Pragmas |  |

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.