Oracle | DBMS_HIERARCHY

Oracle DBMS_HIERARCHY 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 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: Packages and Types Reference

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	A`nalytic 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	GET_MV_SQL_FOR_STAR_CACHE	VALIDATE_ANALYTIC_VIEW
CREATE_VIEW_FOR_FACT_ROWS	IS_NUMERIC	VALIDATE_CHECK_SUCCESS
CREATE_VIEW_FOR_STAR_ROWS	UPGRADE_VALIDATE_LOG_TABLE	VALIDATE_HIERARCHY
GET_MV_SQL_FOR_AV_CACHE

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