Oracle DBMS_LOGREP_UTIL
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 Internal support for log file replication.
AUTHID DEFINER
Data Types SYS.RE$NV_LIST
Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_LOGREP_UTIL'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_LOGREP_UTIL';

161 separate objects
Documented No
Exceptions
Error Code Reason
ORA-04052 error occurred when looking up remote object SYS.DBMS_UTILITY@<db_link_name>
ORA-23375 feature is incompatible with database version at <database_name>
ORA-23605 Invalid value "" for parameter <parameter_value>
ORA-26958 LCR identifier version is invalid.
First Available Not known
Security Model Owned by SYS with EXECUTE granted to GGSYS.
Source {ORACLE_HOME}/rdbms/admin/prvthlut.plb
Subprograms
ADD_CLOB_TO_TABLE GET_LOCK QUERY_DBA_APPLY
ARRAY_SUBSTITUTE GET_MAX_BYTES_PER_CHAR QUERY_DBA_CAPTURE
BIC GET_MAX_LENGTH_COMPAT QUERY_DBA_CAPTURE2
BIS GET_NLS_PREFIX QUERY_DBA_QUEUES
BIT GET_NLS_SUBSTR QUERY_DBA_XOUT_ATTACHED_SVR
BITOR GET_OBJECT_LOCK QUERY_DIFF_APPLY_USER
BOOLEAN_TO_VARCHAR2 GET_OBJECT_NAME RAISE_CONFIG_ERROR
BUMP_SCN GET_PDB_SHORT_NAME RAISE_SYSTEM_ERROR (9)
CANONICALIZE GET_PROC_USE_CONTEXT RAISE_SYSTEM_ERROR_3GL
CANONICALIZE_OLD GET_PROC_USE_CONTEXT_INT RAISE_SYSTEM_WARNING (5)
CANONICAL_CONCAT GET_QUEUE_OID RAISE_SYSTEM_WARNING_3GL
CANON_DBLINK GET_REAL_CHECKPOINT_SCNS RAWS
CHECK_2LEVEL_PRIVILEGE GET_REQ_CKPT_SCN RELEASE_LOCK
CHECK_DBLINK GET_RS_LOCKS RELEASE_OBJECT_LOCK
CHECK_LCRID_VERSION GET_RULE_ACTION_CONTEXT RELEASE_RS_LOCKS
CHECK_PROCESS_PRIVILEGES GET_STR_COMPAT RESET
CHECK_SOURCE_ROOT GET_VERSION_NUMBER SESS_HAS_ROLE
COMMA_TO_TABLE GET_VERSION_STRING SET_3X_IDEN
COMPATIBLE_VARCHAR_TO_INT GG_XSTREAM_QTABLE SET_ALLOCATED_MEMORY
CONVERT_INT_TO_EXT_LCR IS_BUILT_IN_TYPE SET_CCA_MAX_PERCENTAGE
DB_VERSION IS_FUNCTION_BASED_INDEX SET_PARAMETER
DDL_ANNOTATE IS_INVOKER_VALID_OGG_USER SET_STREAMS_AUTO_FILTER
DROP_UNUSED_RULE_SETS IS_MAX_PRIV_USER SET_SUPP_LOGGING
DUMP_TRACE (2) IS_PDB_ENABLED SHORTEN_OBJECT_NAME
ENQUOTE_LITERAL IS_ROOT_PDB START_PROCESS
ENQUOTE_NAME IS_SES_USER STOP_PROCESS
ENSURE_DB_COMPATIBLE IS_VALID_ROLE STRCMP_CS
ENSURE_NONNULL IS_VALID_SYSTEM_PRIV STREAMS_TRANSACTION_PURGE
ENSURE_STREAMS LCR_CACHE_PURGE UNCL_TO_NAME
FETCH_CAPTURE_USER LOAD_DDL_TAB UNCL_TO_QUOTED_NAME
FETCH_DBA_XOUT_CAPTURE_USER LOAD_STRING_FROM_TAB UNLOCK_PROCESS
FORCE_XSTREAM LOCAL_NODE UPDATE_DBNAME_MAPPING
GENERIC_CANONICALIZE LOCK_PROCESS USER_HAS_ROLE
GET_CHECKPOINT_SCNS MESSAGE_TRACKING_PURGE WRAP_DQT
GET_CONSISTENT_SCN MESSAGE_TRACKING_RESIZE WRITE_ERROR
GET_CONSTRAINT_NAME PARSE_FOR_KEYWORD WRITE_TRACE
GET_CURRENT_PDB_NAME PRE_11_2_DB WRITE_TRACE_APT
GET_LAST_ENQ_SCN    
 
ADD_CLOB_TO_TABLE
Likely doing something wrong here and while no exception is raised ... nothing seems to be altered. dbms_logrep_util.add_clob_to_table(
text     IN     CLOB;
tab      IN OUT dbms_sql.varchar2s,
tab_len  IN OUT NUMBER,
line_len IN     NUMBER)
RETURN NUMBER;
DECLARE
 cText     CLOB := 'A,B,C';
 sql_table dbms_sql.varchar2s;
 tLen      NUMBER;
 retVal    NUMBER;
BEGIN
  sql_table(1) := 'Line 1';
  sql_table(2) := 'Line 2';
  tLen := 5;
  retVal := dbms_logrep_util.add_clob_to_table(cText, sql_table, tLen, 60);
  dbms_output.put_line(TO_CHAR(retVal));
  dbms_output.put_line(sql_table(1));
  dbms_output.put_line(sql_table(2));
END;
/
 
BIS
Undocumented

Demo code found in $ORACLE_HOME/rdbms/admin/ e1102000.sql
dbms_logrep_util.bis(
flag       IN NUMBER,
bit_offset IN NUMBER)
RETURN NUMBER;
UPDATE sys.streams$_privileged_user
SET privs = dbms_logrep_util.bis(privs, dbms_streams_adm_utl.privs_local_offset),
                                 flags = dbms_logrep_util.bis(0, 1)
WHERE user# IN (SELECT u.user# FROM sys.user$ u WHERE u.name = user_names_xs(i));
 
BITOR
Undocumented dbms_logrep_util.bitor(
flag  IN NUMBER,
value IN NUMBER)
RETURN NUMBER;
SELECT dbms_logrep_util.bitor(42, 1)
FROM dual;
 
BOOLEAN_TO_VARCHAR2
Returns "Y" for TRUE and "N" for FALSE dbms_logrep_util.boolean_to_varchar2(b IN BOOLEAN)
RETURN VARCHAR2;
BEGIN
  dbms_output.put_line(dbms_logrep_util.boolean_to_varchar2(TRUE));
  dbms_output.put_line(dbms_logrep_util.boolean_to_varchar2(FALSE));
END;
/
 
CANONICALIZE
Undocumented

Overload 1
dbms_logrep_util.canonicalize(
object_name IN  VARCHAR2,
canon_name  OUT VARCHAR2,
is_dbname   IN  BOOLEAN);
DECLARE
 retVal VARCHAR2(30);
BEGIN
  dbms_logrep_util.canonicalize('TAB$', retVal, FALSE);
  dbms_output.put_line(retVal);
END;
/
Overload 2 dbms_logrep_util.canonicalize(
object_name IN VARCHAR2,
is_dbname   IN BOOLEAN)
RETURN VARCHAR2;
DECLARE
 retVal VARCHAR2(30);
BEGIN
  retVal := dbms_logrep_util.canonicalize('TAB$', FALSE);
  dbms_output.put_line(retVal);
END;
/
 
CANONICAL_CONCAT
Returns the schema and object names concatenated with a period (.) separator and within double quotes dbms_logrep_util.canonical_concat(
canon_schema IN VARCHAR2,
canon_name   IN VARCHAR2)
RETURN VARCHAR2;
SELECT dbms_logrep_util.canonical_concat('UWCLASS', 'AIRPLANES')
FROM dual;


-- also see catprp.sql
 
CHECK_LCRID_VERSION
Returns and exception if the LCR version identifier is invalid dbms_logrep_util.check_lcrid_version(lcrid_version IN NUMBER) RETURN NUMBER;
SELECT dbms_logrep_util.check_lcrid_version(2) from dual;

DBMS_LOGREP_UTIL.CHECK_LCRID_VERSION(2)
---------------------------------------
                                     2


SELECT dbms_logrep_util.check_lcrid_version(3) FROM dual;
SELECT dbms_logrep_util.check_lcrid_version(3) FROM dual
*
ERROR at line 1:
ORA-26958: LCR identifier version is invalid.
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 581
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 635
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 4072
 
CHECK_SOURCE_ROOT
Perhaps I am misunderstanding this proc but it appears to not care what I enter as source_root_name ... it returns it unchanged as well as the value TRUE dbms_logrep_util.canonical_concat(
source_db_name         IN     VARCHAR2,
source_root_name       IN OUT VARCHAR2,
include_root_condition    OUT BOOLEAN);
DECLARE
 srn VARCHAR2(60) := 'CDB$ROOT';
 irc BOOLEAN;
BEGIN
  dbms_logrep_util.check_source_root('PDBDEV', srn, irc);
  dbms_output.put_line(srn);
  IF irc THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
 
COMMA_TO_TABLE
Loads an array built from dbms_utility.uncl_array from values in a comma delimited list

Overload 1
dbms_logrep_util.comma_to_table(
list          IN  VARCHAR2,
tablen        OUT NUMBER,
tab           OUT dbms_utility.uncl_array,
allow_3X_iden IN  BOOLEAN);
DECLARE
 sql_tab dbms_utility.uncl_array;
 lenOut  NUMBER;
BEGIN
  dbms_logrep_util.comma_to_table('A,B,C', lenOut, sql_tab, TRUE);
  dbms_output.put_line(TO_CHAR(lenOut));
  dbms_output.put_line(sql_tab(1));
  dbms_output.put_line(sql_tab(2));
  dbms_output.put_line(sql_tab(3));
END;
/
Loads an array built from dbms_utility.lname_array from values in a comma delimited list

Overload 2
dbms_logrep_util.comma_to_table(
list          IN  VARCHAR2,
tablen        OUT BINARY_INTEGER,
tab           OUT dbms_utility.lname_array,
allow_3X_iden IN  BOOLEAN);
DECLARE
 sql_tab dbms_utility.lname_array;
 lenOut  NUMBER;
BEGIN
  dbms_logrep_util.comma_to_table('A,B,C', lenOut, sql_tab, TRUE);
  dbms_output.put_line(TO_CHAR(lenOut));
  dbms_output.put_line(sql_tab(1));
  dbms_output.put_line(sql_tab(2));
  dbms_output.put_line(sql_tab(3));
END;
/
 
COMPATIBLE_VARCHAR_TO_INT
Undocumented and perhaps misused by there is on excuse for an ORA-00600 dbms_logrep_util.compatible_varchar_to_int(compat_var IN VARCHAR2)
RETURN BINARY_INTEGER;
SQL> show parameter compatible

NAME               TYPE        VALUE
------------------ ----------- -------
compatible         string      19.0.0
noncdb_compatible  boolean     FALSE



SELECT dbms_logrep_util.compatible_varchar_to_int('19.0.0')
FROM dual;
       *
ERROR at line 1:
ORA-00600: internal error code, arguments: [knllcmpat_var_2_ub4], [19.0.0], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 1596
 
DB_VERSION
Returns the database version from the database pointed to by a db link dbms_logrep_util.db_version(canon_dblink IN VARCHAR2)
RETURN BINARY_INTEGER;
SELECT dbms_logrep_util.db_version('TESTLINK')
FROM dual;
 
DDL_ANNOTATE
Undocumented dbms_logrep_util.ddl_annotate(
ddl_text   IN  VARCHAR2,
annotation OUT VARCHAR2);
DECLARE
 outVal VARCHAR2(256);
BEGIN
  dbms_logrep_util.ddl_annotate('CREATE TABLE t(testcol DATE)', outVal);
  dbms_output.put_line(outVal);
END;
/

[#annotation= 1, version=1, flags=SUC CMT_ANN ]
[aflags,pid,id,rid,fcid,lcid,node,pos,len,ident]
[DDL ,0,1,0,0,0,TAB,13,1,t]
 
ENQUOTE_LITERAL
Enquote a string literal. Add leading and trailing single quotes to a string literal dbms_logrep_util.enquote_literal(str IN VARCHAR2)
RETURN VARCHAR2;
set serveroutput on

DECLARE
 table_name user_tables.table_name%TYPE := 'SERVERS';
BEGIN
  dbms_output.put_line(table_name);
  table_name := dbms_logrep_util.enquote_literal(table_name);
  dbms_output.put_line(table_name);
END;
/
 
ENQUOTE_NAME
This function encloses a name in double quotes dbms_logrep_util.enquote_name(
str        IN VARCHAR2,
capitalize IN BOOLEAN DEFAULT TRUE)
RETURN VARCHAR2;
set serveroutput on

DECLARE
 table_name user_tables.table_name%TYPE := 'SERVERS';
BEGIN
dbms_output.put_line(table_name);
  table_name := dbms_logrep_util.enquote_name(table_name);
  dbms_output.put_line(table_name);
END;
/
 
ENSURE_DB_COMPATIBLE
Returns an exception if the minimum compatible version is not compatible with the current (locally) installed version dbms_logrep_util.ensure_db_compatible(
min_compat   IN NUMBER,
local_compat IN NUMBER);
exec dbms_logrep_util.ensure_db_compatible(12, 18);

PL/SQL procedure successfully completed.

exec dbms_logrep_util.ensure_db_compatible(12, 11);
*
ERROR at line 1:
ORA-23375: feature is incompatible with database version at ORABASE1
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 4031
ORA-06512: at line 1
 
ENSURE_NONNULL
Ensures a variable is not null by  raising an exception if it is dbms_logrep_util.enquote_name(
parameter_value  IN VARCHAR2,
parameter_name   IN VARCHAR2,
proc_use_context IN VARCHAR2);
DECLARE
 pval  VARCHAR2(10) := 'TEST_VALUE';
 pname VARCHAR2(10) := 'TEST_NAME';
BEGIN
  dbms_logrep_util.ensure_nonnull(pval, pname, NULL);
END;
/

DECLARE
 pval  VARCHAR2(10);
 pname VARCHAR2(10) := 'TEST_NAME';
BEGIN
  dbms_logrep_util.ensure_nonnull(pval, pname, NULL);
END;
/
begin
*
ERROR at line 1:
ORA-23605: invalid value "" for parameter TEST_NAME
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 569
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 623
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 2773
ORA-06512: at line 2
 
ENSURE_STREAMS
Undocumented dbms_logrep_util.ensure_streams;
exec dbms_logrep_util.ensure_streams;
 
GET_CONSISTENT_SCN
Undocumented dbms_logrep_util.get_consistent_scn(consistent_scn OUT NUMBER)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba

DECLARE
  retVal BOOLEAN;
BEGIN
  IF dbms_logrep_util.get_consistent_scn(retVal) THEN
    dbms_output.put_line('T: ' || TO_CHAR(retVal);
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
 
GET_CURRENT_PDB_NAME
Returns the name of the current PDB dbms_logrep_util.get_current_pdb_name RETURN VARCHAR2;
conn sys@pdbdev as sysdba

SELECT dbms_logrep_util.get_current_pdb_name
FROM dual;
 
GET_LAST_ENQ_SCN
Undocumented dbms_logrep_util..get_last_enq_scn(capture_name IN VARCHAR2)
RETURN NUMBER;
See the source code in catcap.sql for the view _SXGG_DBA_CAPTURE
 
GET_MAX_BYTES_PER_CHAR
Returns the maximum number of bytes per character in the database's character set dbms_logrep_util.get_max_bytes_per_char RETURN NUMBER;
SELECT dbms_logrep_util.get_max_bytes_per_char
FROM dual;

GET_MAX_BYTES_PER_CHAR
----------------------
                     4
 
GET_MAX_LENGTH_COMPAT
Undocumented dbms_logrep_util.get_max_length_compat RETURN NUMBER;
SELECT dbms_logrep_util.get_max_length_compat
FROM dual;

GET_MAX_LENGTH_COMPAT
---------------------
                  128
 
GET_NLS_PREFIX
Substrings the first n characters of any string. Works with character sets ... also works with any other string tested.

It is always fascinating when developers at Oracle reinvent the wheel.
dbms_logrep_util.get_nls_prefix(
s   IN VARCHAR2,
len IN NUMBER)
RETURN VARCHAR2;
SELECT dbms_logrep_util.get_nls_prefix('AL32UTF8', 4)
FROM dual;

DBMS_LOGREP_UTIL.GET_NLS_PREFIX('AL32UTF8',4)
----------------------------------------------
AL32


SELECT dbms_logrep_util.get_nls_prefix('Antidisestablishmentarianism', 4)
FROM dual;

DBMS_LOGREP_UTIL.GET_NLS_PREFIX('ANTIDISESTABLISHMENTARIANISM',4)
------------------------------------------------------------------
Anti
 
GET_NLS_SUBSTR
Another absolutely brilliant reinvention of the wheel dbms_logrep_util.get_nls_substr(
s      IN      VARCHAR2,
offset IN  OUT NUMBER,
len    IN      NUMBER);
RETURN VARCHAR2;
SELECT substr('AL32UTF8', 3, 4)
FROM dual;

SUBS
----
32UT


SELECT 3+4 FROM dual;

 3+4
----
   7


DECLARE
 retVal VARCHAR2(30);
 oSet   NUMBER := 3;
BEGIN
  retVal := dbms_logrep_util.get_nls_substr('AL32UTF8', oSet, 4);
  dbms_output.put_line(retVal);
  dbms_output.put_line(TO_CHAR(oSet));
END;
/
32UT
7
 
GET_OBJECT_NAME
Undocumented dbms_logrep_util.get_object_name(
object_name         IN  VARCHAR2,
canon_owner         OUT VARCHAR2,
canon_name          OUT VARCHAR2,
canon_default_owner IN  VARCHAR2);
conn sys@pdbdev as sysdba

DECLARE
k co VARCHAR2(30);
 cn VARCHAR2(30);
BEGIN
  dbms_logrep_util.get_object_name('SYS.DBMS_MVIEW', co, cn, 'SYS');
  dbms_output.put_line('Owner: ' || co);
  dbms_output.put_line('OName: ' || cn);
END;
/
 
GET_PDB_SHORT_NAME
Returns the database name stripped of domain information dbms_logrep_util.get_pdb_shortname(canon_dbname IN VARCHAR2) RETURN VARCHAR2;
conn sys@pdbdev as sysdba

SELECT dbms_logrep_util.get_pdb_short_name('ORABASE.MLIB.COM')
FROM dual;
 
GET_REQ_CKPT_SCN
Undocumented dbms_logrep_util..get_req_ckpt_scn(
logmnr_sid  IN NUMBER,
applied_scn IN NUMBER)
RETURN NUMBER;
See the source code in catcap.sql for the view _SXGG_DBA_CAPTURE
 
GET_STR_COMPAT
Undocumented dbms_logrep_util..get_str_compat RETURN BINARY_INTEGER;
conn sys@pdbdev as sysdba

SELECT dbms_logrep_util.get_str_compat
FROM dual;

-- also see the source code in catstrc.sql for the view DBA_STREAMS_UNSUPPORTED
 
GET_VERSION_NUMBER
Overload 1 dbms_logrep_util.get_version_number(version IN VARCHAR2)
RETURN BINARY_INTEGER;
SELECT dbms_logrep_util.get_version_number('21.1.0.0')
FROM dual;

DBMS_LOGREP_UTIL.GET_VERSION_NUMBER('21.1.0.0')
-----------------------------------------------
21010000
Overload 2 dbms_logrep_util.get_version_number(version IN dbms_utility.db_version_array)
RETURN BINARY_INTEGER;
TBD
 
IS_BUILT_IN_TYPE
Undocumented dbms_logrep_util.is_built_in_type(type_name IN VARCHAR2) RETURN BOOLEAN;
conn sys@pdbdev as sysdba

BEGIN
  IF dbms_logrep_util.is_built_in_type('ADR_HOME_T') THEN
    dbms_output.put_line('Found');
  ELSE
    dbms_output.put_line('Not Found');
  END IF;
END;
/
 
IS_FUNCTION_BASED_INDEX
Returns TRUE if the identified index is function based dbms_logrep_util.is_function_based_index(
canon_sname IN VARCHAR2,
canon_oname IN VARCHAR2)
RETURN BOOLEAN;
SELECT owner, index_name
FROM dba_indexes
WHERE index_type = 'FUNCTION-BASED NORMAL'
AND rownum < 6;

OWNER                     INDEX_NAME
------------------------- ------------------------------
SYS                       I_PDBSYNC3
SYS                       I_WRI$_OPTSTAT_TAB_OBJ#_ST
SYS                       I_WRI$_OPTSTAT_IND_OBJ#_ST
SYS                       I_WRI$_OPTSTAT_IND_ST
SYS                       I_WRI$_OPTSTAT_AUX_ST


BEGIN
  IF dbms_logrep_util.is_function_based_index('SYS', 'I_PDBSYNC3') THEN
    dbms_output.put_line('I_PDBSYNC3 is an FBI');
  ELSE
    dbms_output.put_line('I_PDBSYNC3 is not an FBI');
  END IF;
END;
/
I_PDBSYNC3 is an FBI

SELECT owner, index_name
FROM dba_indexes
WHERE index_type LIKE 'FUNCTION-BASED DOMAIN';

OWNER                     INDEX_NAME
------------------------- ------------------------------
XDB                       XDBHI_IDX


BEGIN
  IF dbms_logrep_util.is_function_based_index('XDB', 'XDBHI_IDX') THEN
    dbms_output.put_line('XDBHI_IDX is an FBI');
  ELSE
    dbms_output.put_line('XDBHI_IDX is not an FBI');
  END IF;
END;
/
XDBHI_IDX is an FBI
 
IS_INVOKER_VALID_OGG_USER
Returns TRUE if the user is a valid GoldenGate user dbms_logrep_util.is_invoker_valid_ogg_user(canon_user_name IN VARCHAR2) RETURN BOOLEAN;
BEGIN
  IF dbms_logrep_util.is_invoker_valid_ogg_user(USER) THEN
    dbms_output.put_line('A Valid GG User');
  ELSE
    dbms_output.put_line('Not A Valid GG User');
  END IF;
END;
/
 
IS_MAX_PRIV_USER
Returns TRUE if a user has maximum privileges ... whatever that means dbms_logrep_util.is_max_priv_user(canon_user_name IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
  IF dbms_logrep_util.is_max_priv_user('SYS') THEN
    dbms_output.put_line('SYS is a max priv user');
  ELSE
    dbms_output.put_line('SYS is not a max priv user');
  END IF;
  IF dbms_logrep_util.is_max_priv_user('XDB') THEN
    dbms_output.put_line('XDB is a max priv user');
  ELSE
    dbms_output.put_line('XDB is not a max priv user');
  END IF;
END;
/
SYS is a max priv user
XDB is not a max priv user
 
IS_PDB_ENABLED
Returns TRUE if a database is a Container database dbms_logrep_util.is_pdb_enabled RETURN BOOLEAN;
conn sys@pdbdev as sysdba

BEGIN
  IF dbms_logrep_util.is_pdb_enabled THEN
    dbms_output.put_line('Enabled');
  ELSE
    dbms_output.put_line('Not Enabled');
  END IF;
END;
/
 
IS_ROOT_PDB
Returns TRUE if the current container is CDB$ROOT dbms_logrep_util.is_root_pdb RETURN BOOLEAN;
conn sys@pdbdev as sysdba

BEGIN
  IF dbms_logrep_util.is_root_pdb THEN
    dbms_output.put_line('Root Container');
  ELSE
    dbms_output.put_line('Some Other Container');
  END IF;
END;
/
 
IS_SES_USER
Returns 1 if the canon_user_name matches the current user logon dbms_logrep_util.is_ses_user(canon_user_name IN VARCHAR2) RETURN BINARY_INTEGER;
SQL> sho user
USER is "SYS"

SELECT dbms_logrep_util.is_ses_user('SYSTEM')
FROM dual;

DBMS_LOGREP_UTIL.IS_SES_USER('SYSTEM')
-------------------------------------
                                    0
 
IS_VALID_ROLE
Returns TRUE if the role named is valid dbms_logrep_util.is_valid_role(canon_role_in IN VARCHAR2) RETURN BOOLEAN;
BEGIN
  IF dbms_logrep_util.is_valid_role('RESOURCE') THEN
    dbms_output.put_line('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;
/
TRUE
 
IS_VALID_SYSTEM_PRIV
Returns TRUE if the system privilege named is valid dbms_logrep_util.is_valid_system_priv(canon_privilege_in IN VARCHAR2) RETURN BOOLEAN;
BEGIN
  IF dbms_logrep_util.is_valid_system_priv('UNDER ANY TABLE') THEN
    dbms_output.put_line('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;
/
True
 
LCR_CACHE_PURGE
Undocumented dbms_logrep_util.lcr_cache_purge;
exec dbms_logrep_util.lcr_cache_purge;
 
LOCAL_NODE
In a stand-alone database returns the database name from v$database dbms_logrep_util.local_node RETURN VARCHAR2;
SQL> SELECT name FROM v$database;

NAME
----------
ORABASE21


SELECT dbms_logrep_util.local_node
FROM dual;

LOCAL_NODE
-----------
ORABASE21
 
MESSAGE_TRACKING_PURGE
Undocumented dbms_logrep_util.message_tracking_purge;
exec dbms_logrep_util.message_tracking_purge;
 
RAWS
Undocumented dbms_logrep_util.raws(bit_offset IN NUMBER) RETURN RAW;
SELECT dbms_logrep_util.raws(42)
FROM dual;

DBMS_LOGREP_UTIL.RAWS(42)
--------------------------
80
 
RESET
Undocumented dbms_logrep_util.reset;
exec dbms_logrep_util.reset;
 
SESS_HAS_ROLE
Returns 1 if the current session has been assigned the named role dbms_logrep_util.sess_has_role(canon_role_name IN VARCHAR2) RETURN BINARY_INTEGER;
SELECT dbms_logrep_util.sess_has_role('RESOURCE')
FROM dual;

DBMS_LOGREP_UTIL.SESS_HAS_ROLE('RESOURCE')
------------------------------------------
                                         0
 
SET_3X_IDEN
Undocumented dbms_logrep_util.set_3x_iden(enable IN BOOLEAN);
exec dbms_logrep_util.set_3x_iden(TRUE);
 
SET_SUPP_LOGGING
Couldn't get this to fail with any value from 0 to 99 ... thought I knew what it would do ... but never found any system changes. dbms_logrep_util.set_supp_logging(
canon_schema_name IN VARCHAR2,
level             IN BINARY_INTEGER,
append            IN BOOLEAN);
exec dbms_logrep_util.set_supp_logging('SYS', 0, TRUE);

exec dbms_logrep_util.set_supp_logging('SYS', 99, TRUE);
 
SHORTEN_OBJECT_NAME
Another reinvention of the SUBSTRING function. And as can be seen from the 2nd demo at right doesn't even check to see if the resulting string is a valid object name dbms_logrep_util.shorten_object_name(
object_name IN VARCHAR2,
limit       IN NUMBER)
RETURN VARCHAR2;
SELECT dbms_logrep_util.shorten_object_name('OBNOXIOUSLYNAMEDTABLE', 9)
FROM dual;

DBMS_LOGREP_UTIL.SHORTEN_OBJECT_NAME('OBNOXIOUSLYNAMEDTABLE',9)
---------------------------------------------------------------
OBNOXIOUS


SELECT dbms_logrep_util.shorten_object_name('OBNO XIOUSLYNAMEDTABLE', 9)
FROM dual;

DBMS_LOGREP_UTIL.SHORTEN_OBJECT_NAME('OBNOXIOUSLYNAMEDTABLE',9)
---------------------------------------------------------------
OBNO XIOU
 
STRCMP_CS
String Comparison but clearly from the two examples at right I remain far from impressed.

Perhaps the "CS" hints at some deeper mystery
dbms_logrep_util.strcmp_cs(
str1 IN VARCHAR2,
str2 IN VARCHAR2)
RETURN BINARY_INTEGER;
SELECT dbms_logrep_util.strcmp_cs('Morgan', 'Mogen')
FROM dual;

DBMS_LOGREP_UTIL.STRCMP_CS('MORGAN','MOGEN')
--------------------------------------------
                                           1


SELECT dbms_logrep_util.strcmp_cs('Morgan', 'Quantum Fluctuations')
FROM dual;

DBMS_LOGREP_UTIL.STRCMP_CS('MORGAN','QUANTUMFLUCTUATIONS')
----------------------------------------------------------
                                                         1
 
STREAMS_TRANSACTION_PURGE
Undocumented dbms_logrep_util.streams_transaction_purge;
exec dbms_logrep_util.streams_transaction_purge;
 
UNCL_TO_NAME
Converts a uncl_array to a name_array dbms_logrep_util.uncl_to_name(
uncl IN  dbms_utility.uncl_array,
name OUT dbms_utility.name_array);
DECLARE
 uArray dbms_utility.uncl_array;
 nArray dbms_utility.name_array;
BEGIN
  uArray(1) := 'Dan';
  uArray(2) := 'Morgan';
  dbms_logrep_util.uncl_to_name(uArray, nArray);
  dbms_output.put_line(nArray(1));
  dbms_output.put_line(nArray(2));
END;
/
Dan
Morgan
 
UNCL_TO_QUOTED_NAME
Converts a uncl_array to quoted_name_array dbms_logrep_util.uncl_to_quoted_name(
uncl IN  dbms_utility.uncl_array,
name OUT dbms_utility.quoted_name_array);
DECLARE
 uArray dbms_utility.uncl_array;
 qArray dbms_utility.quoted_name_array;
BEGIN
  uArray(1) := 'Dan';
  uArray(2) := 'Morgan';
  dbms_logrep_util.uncl_to_quoted_name(uArray, qArray);
  dbms_output.put_line(qArray(1));
  dbms_output.put_line(qArray(2));
END;
/
Dan
Morgan
 
USER_HAS_ROLE
Returns 1 if the named user has the named role dbms_logrep_util.user_has_role(
canon_user_name IN VARCHAR2,
canon_role_name IN VARCHAR2)
RETURN BINARY_INTEGER;
SQL> SELECT grantee
FROM dba_role_privs
WHERE granted_role = 'RESOURCE'
ORDER BY 1;

GRANTEE
------------------------------
CTXSYS
GGSYS
LBACSYS
LOGSTDBY_ADMINISTRATOR
MDDATA
MDSYS
OJVMSYS
OUTLN
SYS
XDB

SELECT dbms_logrep_util.user_has_role('SYS', 'RESOURCE')
FROM dual;

DBMS_LOGREP_UTIL.USER_HAS_ROLE('SYS','RESOURCE')
------------------------------------------------
                                               1
 
WRITE_ERROR
  dbms_logrep_util.write_error(
header      IN VARCHAR2,
write_alert IN BOOLEAN);
exec dbms_logrep_util.write_error('WT_TEST0', TRUE);

PL/SQL procedure successfully completed.

-- from trace file
Trace file C:\U01\ORABASE19\diag\rdbms\orabasexix\orabasexix\trace\orabasexix_ora_7724.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Build label: RDBMS_19.3.0.0.0DBRU_WINDOWS.X64_190528
Windows NT Version V6.2
ORACLE_HOME = C:\u01\orahome19\WINDOWS.X64_193000_db_home
Node name : PERRITO5
CPU : 4 - type 86642 physical cores
Process Affinity : 0x0000000000000000
Memory (Avail/Total): Ph:10205M/16239M, Ph+PgF:8682M/18671M
Instance name: orabasexix
Redo thread mounted by this instance: 1
Oracle process number: 0
Windows thread id: 7724, image:


*** 2020-09-28T20:52:12.541587-05:00 (CDB$ROOT(1))
Required IPC RDMAV_FORK_SAFE environment not set
Required IPC RDMAV_HUGEPAGES_SAFE environment not set
WT_TEST0: exception, sql code = 0, error message: ORA-0000: normal, successful completion
 
WRITE_TRACE
Write a message to a trace file
Overload 1
dbms_logrep_util.write_trace(
message     IN VARCHAR2,
event_level IN BINARY_INTEGER,
time_info   IN BOOLEAN,
wrap        IN BOOLEAN)
exec dbms_logrep_util.write_trace('WT_TEST0', 2, TRUE, FALSE);

PL/SQL procedure successfully completed.

-- nothing was found in the alert log or a trace file
Overload 2 dbms_logrep_util.write_trace(
message     IN CLOB,
event_level IN BINARY_INTEGER,
time_info   IN BOOLEAN,
wrap        IN BOOLEAN)
exec dbms_logrep_util.write_trace('WT_TEST0', 3, TRUE, TRUE);

PL/SQL procedure successfully completed.

-- nothing was found in the alert log or a trace file

Related Topics
Built-in Functions
Built-in Packages
DBMS_LOGREP_DEFPROC_UTL
DBMS_LOGREP_EXP
DBMS_LOGREP_IMP
DBMS_LOGREP_IMP_INTERNAL
DBMS_LOGREP_UTIL_INVOK
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx