Oracle DBMS_LIBCACHE$DEF Demos

Oracle DBMS_LIBCACHE$DEF Version 11.2.0.3

General Information

Purpose

Data types and messages for dbms_libcache which compiles cursors in the local library cache

Constants

Name	Data Type	Value
Source Access Methods
DB_LINK_METHOD	BINARY_INTEGER	1
GLOBAL_METHOD	BINARY_INTEGER	2
ACCESS_METHOD	BINARY_INTEGER	DB_LINK_METHOD
DBMS_LIBCACHE Return Codes
SUCCESS	BINARY_INTEGER	0
ERROR	BINARY_INTEGER	1
WARNING	BINARY_INTEGER	2
Internal Oracle command types -- refer octdef.h
c_ins	BINARY_INTEGER	2
c_sel	BINARY_INTEGER	3
c_upd	BINARY_INTEGER	6
c_del	BINARY_INTEGER	7
c_plsql	BINARY_INTEGER	47
External Oracle data types -- refer dtydef.h
c_nul (not bound)	BINARY_INTEGER	0
c_str (string, may be space padded)	BINARY_INTEGER	1
c_num (numeric)	BINARY_INTEGER	2
c_int (integer)	BINARY_INTEGER	3
c_flt (floating point)	BINARY_INTEGER	4
c_txt (null terminated text)	BINARY_INTEGER	5
c_vnu (num, length in 1st byte)	BINARY_INTEGER	6
c_pdn (packed decimal)	BINARY_INTEGER	7
c_lng (long)	BINARY_INTEGER	8
c_vcs (variable string)	BINARY_INTEGER	9
c_ti5 (table)	BINARY_INTEGER	10
c_rid (row id)	BINARY_INTEGER	11
c_dat (date)	BINARY_INTEGER	12
c_bin (RAW)	BINARY_INTEGER	23
c_lbi (binary long raw)	BINARY_INTEGER	24
c_chr (ANSI Fixed)	BINARY_INTEGER	96
Information cursor processed
lc_cursor_done_msg	VARCHAR2(256)	'Completed cursor :'
Information processing complete
lc_compile_done_msg	VARCHAR2(256)	'Total SQL statements compiled = '

Data Types and Subtypes

-- types for dbms_libcache package
TYPE dynamic_cursor IS REF CURSOR;

cursor_id INTEGER;

cursor_addr RAW(8);

db_link VARCHAR2(128);

sql_stmt VARCHAR2(13767);

-- PL/SQL table containing the sql statement
tab_sql_statements dbms_sql.varchar2s ;

-- Control record describing each cursor on the source instance
TYPE SQL_record IS RECORD (
inst_id           INTEGER,
addr              cursor_addr%TYPE,
hash_value        INTEGER,
command_type      INTEGER,
optimizer_mode    VARCHAR2(10),
parsing_user_id   INTEGER,
parsing_schema_id INTEGER);

-- Table of SQL records extracted from the source instance
TYPE SQL_tab IS TABLE OF SQL_record INDEX BY BINARY_INTEGER;

-- Control record containing the meta data record for a bind variable
TYPE SQL_bind_record IS RECORD (
position     INTEGER,
datatype     INTEGER,
bind_name    VARCHAR2(30),
max_length   INTEGER,
array_length INTEGER);

-- Table of SQL meta data descriptions extracted from the source instance
TYPE SQL_bind_tab IS TABLE OF SQL_bind_record INDEX BY BINARY_INTEGER;

-- ERROR STACK for dbms_libcache
ERR_MSG VARCHAR2(100); -- application error message
ERR_CODE NUMBER;       -- application error code

-- record of values for each Oracle datatype, excluding tables
TYPE rec_data_values IS RECORD(
v_null VARCHAR2(1) := null,
v_str VARCHAR2(13767) := 'SAMPLE',
v_num NUMERIC := 0.0,
v_int INTEGER := 0,
v_flt FLOAT := 0.0,
v_txt VARCHAR2(256) := 'SAMPLE',
v_vnu NUMBER := 0,
v_pdn DECIMAL := 0.0,
v_lng LONG := 'SAMPLE',
v_vcs VARCHAR2(13767) := 'SAMPLE',
v_rid ROWID, -- set at initialization
v_dat DATE := sysdate,
v_bin RAW(13767),
v_lbi LONG RAW,
v_chr CHAR(2000) := 'SAMPLE' );

Dependencies

DBMS_LIBCACHE

DBMS_SQL

Exceptions

Error Code	Reason
-00942	-- Parsing user does not have access to the objects lc_outside_security exception; lc_outside_security_msg constant varchar2(256) := 'Parsing user cannot access the objects.'; pragma exception_init(lc_outside_security, -942);
-20001	-- instance name for the source instance must be entered lc_err_null_instance_name exception; lc_err_null_instance_code constant numeric := -20001; lc_err_null_instance_msg constant varchar2(256) := 'Instance name cannot be null.'; pragma exception_init(lc_err_null_instance_name, -20001);
-20002	-- instance name entered does not match an active instance, excluding the current lc_err_invalid_instance_name exception; lc_err_invalid_instance_code constant numeric := -20002; lc_err_invalid_instance_msg constant varchar2(256) := 'Instance name is invalid:'; pragma exception_init(lc_err_invalid_instance_name, -20002);
-20003	-- multiple instances exist with the instance name entered lc_err_multiple_instance_names exception; lc_err_multiple_instance_code constant numeric := -20003 ; lc_err_multiple_instance_msg constant varchar2(256) := 'Multiple instances with name:'; pragma exception_init(lc_err_multiple_instance_names, -20003);
-20004	-- no instance id. found with the given instance name (eg. it is now down) lc_err_no_instance_found exception; lc_err_no_instance_found_code constant numeric := -20004 ; lc_err_no_instance_found_msg constant varchar2(256) := 'Instance not found:'; pragma exception_init(lc_err_no_instance_found, -20004);
-20005	-- user name entered does not match known user lc_err_invalid_username exception; lc_err_invalid_username_code constant numeric := -20005 ; lc_err_invalid_username_msg constant varchar2(256) := 'User name is invalid:'; pragma exception_init(lc_err_invalid_username, -20005);
-20006	-- execution threshold should be greater than zero lc_err_threshold_exec exception; lc_err_threshold_exec_code constant numeric := -20006 ; pragma exception_init(lc_err_threshold_exec, -20006);
-20007	-- shared memory threshold should be greater than zero lc_err_threshold_sharable_mem exception; lc_err_threshold_shar_mem_code constant numeric := -20007 ; pragma exception_init(lc_err_threshold_sharable_mem, -20007);
-20008	-- no SQL text address descriptors returned lc_err_no_matching_SQL exception; lc_err_no_matching_SQL_code constant numeric := -20008 ; lc_err_no_matching_SQL_msg constant varchar2(256) := 'No SQL statements found matching the input criteria.'; pragma exception_init(lc_err_no_matching_SQL, -20008);
-20009	-- no SQL text returned for the address descriptor lc_err_no_SQL_text exception; lc_err_no_SQL_text_code constant numeric := -20009 ; lc_err_no_SQL_text_msg constant varchar2(256) := 'No SQL text found.'; pragma exception_init(lc_err_no_SQL_text, -20009);
-20100	-- Fatal error in compile from remote lc_err_compile_cursors exception; lc_err_compile_cursors_code constant numeric := -20100 ; lc_err_compile_cursors_msg constant varchar2(256) := 'Exiting compile from remote'; pragma exception_init(lc_err_compile_cursors, -20100);
-20111	-- Non-fatal error in compile cursor job lc_warn_compile_cursors exception; lc_warn_compile_cursors_code constant numeric := -20111 ; lc_warn_compile_cursors_msg constant varchar2(256) := 'Warning at cursor :'; pragma exception_init(lc_warn_compile_cursors, -20111);
-20112	-- Fatal error in compile cursor job lc_error_count_exceeded exception; lc_error_count_exceeded_code constant numeric := -20112 ; lc_error_count_exceeded_msg constant varchar2(256) := 'Error threshold exceeded.'; pragma exception_init(lc_error_count_exceeded, -20112);
-20114	-- Warn of unsupported data types during binding lc_warn_unsupported_type exception; lc_warn_unsupported_type_code constant numeric := -20114 ; lc_warn_unsupported_type_msg constant varchar2(256) := 'Warning - bind datatype is not supported.'; pragma exception_init(lc_warn_unsupported_type, -20114);
-20115	-- Raise an error if database link is null, db_access_method only lc_err_null_db_link exception; lc_err_null_db_link_code constant numeric := -20115 ; lc_err_null_db_link_msg constant varchar2(256) := 'Database link is null.'; pragma exception_init(lc_err_null_db_link, -20115);
-20116	-- Raise an error if the database link is invalid, db_access_method only lc_err_invalid_db_link exception; lc_err_invalid_db_link_code constant numeric := -20116 ; lc_err_invalid_db_link_msg constant varchar2(256) := 'Database link is invalid, see error stack.'; pragma exception_init(lc_err_invalid_db_link, -20116);
-20118	-- Invalid schema name (parsing user is dropped) lc_err_invalid_schema exception; lc_err_invalid_schema_code constant numeric := -20118 ; lc_err_invalid_schema_msg constant varchar2(256) := 'Parsing schema id no longer exists:'; pragma exception_init(lc_err_invalid_schema, -20118);

First Available

2000

Security Model

Owned by SYS with EXECUTE granted to PUBLIC

Source

{ORACLE_HOME}/rdbms/admin/catlibc.sql