Oracle | DBMS_HIERARCHY

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	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