Oracle DBMS_HIERARCHY
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose Analytic view validation
AUTHID CURRENT_USER
Constants
Name Data Type Value
Upgrade Log Table
VERSION_12_2_0_1 NUMBER 1
VERSION_12_2_0_2 NUMBER 2
VERSION_NONE NUMBER 3
VERSION_LATEST NUMBER VERSION_12_2_0_2
Data Types TYPE ID3 IS RECORD (
comp1 VARCHAR2(128),
comp2 VARCHAR2(128),
comp3 VARCHAR2(128));

TYPE ID2 IS RECORD (
comp1 VARCHAR2(128),
comp2 VARCHAR2(128));

TYPE ID_SEQUENCE IS VARRAY(32767) OF VARCHAR2(128);

TYPE ID2_SEQUENCE IS VARRAY(32767) OF ID2;

TYPE ID3_SEQUENCE IS VARRAY(32767) OF ID3;
Dependencies
DBMS_ASSERT DBMS_HCS_LIB  
Documented Yes
Exceptions
Error Code Reason
ORA-00942 TABLE_DOES_NOT_EXIST
ORA-00955 NAME_ALREADY_USED
ORA-18250 INVALID_SQL_ARG
ORA-18263 MISMATCH_OBJ_LOGNUM
ORA-18275 MISMATCH_COL_LENGTH
ORA-18276 LOG_TABLE_UPGRADE
ORA-18307 Analytic view <schema_name.object_name> does not exist
ORA-44003 INVALID_SQL_NAME
First Available 12.2
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source $ORACLE_HOME/rdbms/admin/dbmshier.sql
Subprograms
 
CREATE_VALIDATE_LOG_TABLE
Creates a log table for use in validating hierarchies dbms_hierarchy.create_validate_log_table(
table_name       IN VARCHAR2,
owner_name       IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'),
ignore_if_exists IN BOOLEAN  DEFAULT FALSE);
exec dbms_hierarchy.create_validate_log_table('UWLOGTAB', 'C##UWCLASS', TRUE);

PL/SQL procedure successfully completed.
 
CREATE_VIEW_FOR_FACT_ROWS (new 21c)
Undocumented dbms_hierarchy.create_view_for_fact_rows(
analytic_view_name       IN VARCHAR2,
view_name                IN VARCHAR2,
dim_hier_seq             IN dbms_hierarchy.ID2_SEQUENCE DEFAULT NULL,
analytic_view_owner_name IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
view_owner_name          IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
dim_qual_sep             IN VARCHAR2 DEFAULT '_',
all_join_keys            IN BOOLEAN DEFAULT TRUE,
include_meas             IN BOOLEAN DEFAULT FALSE,
include_hier_attr        IN BOOLEAN DEFAULT FALSE);
PRAGMA supplemental_log_data(create_view_for_fact_rows, UNSUPPORTED_WITH_COMMIT);
TBD
 
CREATE_VIEW_FOR_STAR_ROWS (new 21c)
Undocumented dbms_hierarchy.create_view_for_star_rows(
analytic_view_name       IN VARCHAR2,
dimension_alias          IN VARCHAR2,
view_name                IN VARCHAR2,
analytic_view_owner_name IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
view_owner_name          IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
hier_qual_sep            IN VARCHAR2 DEFAULT '_',
include_hier_attr        IN BOOLEAN  DEFAULT FALSE);
PRAGMA supplemental_log_data(create_view_for_star_rows, AUTO_WITH_COMMIT);
TBD
 
GET_MV_SQL_FOR_AV_CACHE
Undocumented dbms_hierarchy.get_mv_sql_for_av_cache(
analytic_view_name       IN VARCHAR2,
cache_idx                IN NUMBER, -- 0 based cache index
analytic_view_owner_name IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'))
RETURN CLOB;
TBD
 
GET_MV_SQL_FOR_STAR_CACHE (new 21c)
Undocumented dbms_hierarchy.get_mv_sql_for_star_cache(
attr_dim_name       IN VARCHAR2,
attr_dim_owner_name IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'))
RETURN CLOB;
TBD
 
IS_NUMERIC
Returns 1 if the string can be converted to an integer or decimal value, otherwise 0 dbms_hierarchy.is_numeric(strnum IN VARCHAR2) RETURN NUMBER;
SELECT dbms_hierarchy.is_numeric('20')
FROM dual;

DBMS_HIERARCHY.IS_NUMERIC('20')
-------------------------------
                              1


SELECT dbms_hierarchy.is_numeric('20.6')
FROM dual;

DBMS_HIERARCHY.IS_NUMERIC('20.6')
---------------------------------
                                1


SELECT dbms_hierarchy.is_numeric('20A6')
FROM dual;

DBMS_HIERARCHY.IS_NUMERIC('20A6')
---------------------------------
                                0
 
UPGRADE_VALIDATE_LOG_TABLE
Undocumented dbms_hierarchy.upgrade_validate_log_table(
table_name IN VARCHAR2,
owner_name IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'));
exec dbms_hierarchy.upgrade_validate_log_table('UWLOGTAB', 'C##UWCLASS');

PL/SQL procedure successfully completed.
 
VALIDATE_ANALYTIC_VIEW (2 new 21c parameters)
Validates an analytic view writing output to the named log table dbms_hierarchy.validate_analytic_view(
analytic_view_name       IN VARCHAR2,
analytic_view_owner_name IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'),
log_table_name           IN VARCHAR2 DEFAULT NULL,
log_table_owner_name     IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'),
skip hiers               IN VARCHAR2,
error_threshold          IN NUMBER)
RETURN NUMBER;
SELECT dbms_hierarchy.validate_analytic_view('UWAVIEW', 'C##UWCLASS', 'UWLOGTAB', 'C##UWCLASS')
FROM dual;
*
ERROR at line 1:
ORA-18307: analytic view "C##UWCLASS"."UWAVIEW" does not exist
ORA-06512: at "SYS.DBMS_HIERARCHY", line 385
ORA-06512: at "SYS.DBMS_HIERARCHY", line 442
ORA-06512: at line 1
 
VALIDATE_CHECK_SUCCESS
Undocumented dbms_hierarchy.validate_check_success(
topobj_name          IN VARCHAR2,
topobj_owner         IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'),
log_number           IN NUMBER,
log_table_name       IN VARCHAR2 DEFAULT NULL,
log_table_owner_name IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'))
RETURN VARCHAR2;
TBD
 
VALIDATE_HIERARCHY
Validate a hierarchy writing output to the named log table dbms_hierarchy.validate_hierarchy(
hier_name            IN VARCHAR2,
hier_owner_name      IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'),
log_table_name       IN VARCHAR2,
log_table_owner_name IN VARCHAR2 DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_USER'))
RETURN NUMBER;
TBD

Related Topics
Built-in Functions
Built-in Packages
Analytic Views
What's New In 19c
What's New In 20c-21c

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