[Chapter 1] 1.6 Nonprogram Elements

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;