Oracle | DBMS_LIBCACHE$DEF

Oracle DBMS_LIBCACHE$DEF Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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

AUTHID N/A

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 = '
lc_outside_security_msg	VARCHAR2(256)	'Parsing user cannot access the objects.'

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_SQL

Documented No

Exceptions

Error Code	Reason
ORA-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);
ORA-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);
ORA-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);
ORA-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);
ORA-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);
ORA-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);
ORA-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);
ORA-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);
ORA-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);
ORA-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);
ORA-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);
ORA-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);
ORA-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);
ORA-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);
ORA-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);
ORA-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);
ORA-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 - running this script, in SQL*Plus results in an exit from the session. This package is not deployed by OUI or DBCA.

Manual Creation Attempt

It appears Oracle broke this any didn't notice as v$sql2 no longer exists: Of course, on the other hand, I am hacking. SQL> @?/rdbms/admin/catlibc.sql SP2-0808: Package created with compilation warnings Synonym created. Grant succeeded. CREATE OR REPLACE VIEW v$sql2 AS * ERROR at line 1: ORA-00999: invalid view name GRANT select ON v$sql2 TO PUBLIC; * ERROR at line 1: ORA-00942: table or view does not exist Synonym created. DROP USER parser CASCADE; * ERROR at line 1: ORA-01918: user 'PARSER' does not exist ... Creating the parsing user and database link. Below are the list of online tablespaces in this database. Decide which tablespace you wish to use for the PARSER user. TABLESPACE_NAME ------------------------------ SYSAUX UNDOTBS1 TEMP USERS Please enter the parsing users password and tablespaces. create user parser identified by &parser_password * ERROR at line 1: ORA-00922: missing or invalid option to parser * ERROR at line 7: ORA-01917: user or role 'PARSER' does not exist ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SP2-0640: Not connected SP2-0640: Not connected Please enter the parsing users TNS connect string. SP2-0640: Not connected C:\Users\oracle>