Oracle DBMS_LIBCACHE$DEF
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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 None
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 no privileges granted
Source {ORACLE_HOME}/rdbms/admin/catlibc.sql
 
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>conn / as sysdba
'conn' is not recognized as an internal or external command,
operable program or batch file.

Related Topics
DBMS_LIBCACHE
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved