Oracle | DBMS_HIERARCHY

Oracle DBMS_HIERARCHY Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?

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_22	NUMBER	3
VERSION_NONE	NUMBER	4
VERSION_LATEST	NUMBER	VERSION_22

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

ALL_ANALYTIC_VIEWS	ALL_CONSTRAINTS	DBMS_HCS_LIB
ALL_ANALYTIC_VIEW_COLUMNS	ALL_CONS_COLUMNS	DBMS_HCS_LOG
ALL_ANALYTIC_VIEW_DIMENSIONS	ALL_TAB_COLS	DBMS_LOGREP_UTIL
ALL_ANALYTIC_VIEW_DIM_ATTRS	ALL_TAB_COLUMNS	DBMS_STANDARD
ALL_ANALYTIC_VIEW_FACT_COLS	DBMS_ASSERT	DBMS_UTILITY
ALL_ANALYTIC_VIEW_HIERS	DBMS_AVTUNE	PLITBLM
ALL_ANALYTIC_VIEW_KEYS

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
{ORACLE_HOME}/rdbms/admin/prvtshier.pl`b

Subprograms

CREATE_ANALYTIC_VIEW	GET_MV_SQL_FOR_STAR_CACHE
CREATE_PARENT_CHILD_HIERARCHY	IS_NUMERIC
CREATE_VALIDATE_LOG_TABLE	UPGRADE_VALIDATE_LOG_TABLE
CREATE_VIEW_FOR_FACT_ROWS	VALIDATE_ANALYTIC_VIEW
CREATE_VIEW_FOR_STAR_ROWS	VALIDATE_CHECK_SUCCESS
GET_MV_SQL_FOR_AV_CACHE	VALIDATE_HIERARCHY

CREATE_ANALYTIC_VIEW (new 23ai)

Undocumented dbms_hierarchy.create_analytic_view( table_name IN dbms_id, table_owner IN dbms_id DEFAULT sys_context('USERENV', 'CURRENT_SCHEMA'), analytic_view_name IN dbms_id DEFAULT NULL, analytic_view_owner IN dbms_id DEFAULT sys_context('USERENV', 'CURRENT_SCHEMA'));

TBD

CREATE_PARENT_CHILD_HIERARCHY (new 23ai)

Undocumented dbms_hierarchy.create_parent_child_hierarchy( table_name IN dbms_id, table_owner_name IN dbms_id DEFAULT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'), parent_column_name IN dbms_id, child_column_name IN dbms_id, lvl_attr_column_names IN sys.dbms_hierarchy.id_sequence DEFAULT ID_SEQUENCE(), level_view_name IN dbms_id DEFAULT NULL, dim_name IN dbms_id DEFAULT NULL, hier_name IN dbms_id DEFAULT NULL, cache_name IN dbms_id DEFAULT NULL, dest_owner_name IN dbms_id DEFAULT sys_context('USERENV', 'CURRENT_SCHEMA'));

TBD

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_SCHEMA'), ignore_if_exists IN BOOLEAN DEFAULT FALSE);

exec sys.dbms_hierarchy.create_validate_log_table('UWLOGTAB', 'C##UWCLASS', TRUE);

      

      PL/SQL procedure successfully completed.

CREATE_VIEW_FOR_FACT_ROWS

Undocumented dbms_hierarchy.create_view_for_fact_rows( analytic_view_name IN VARCHAR2, view_name IN VARCHAR2, dim_hier_seq IN dbms_hierarchy.id2sequence 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);

TBD

CREATE_VIEW_FOR_STAR_ROWS

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

TBD

GET_MV_SQL_FOR_AV_CACHE (new 23ai overload)

Undocumented

Overload 1 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_SCHEMA')) RETURN CLOB;

TBD

Overload 2 dbms_hierarchy.get_mv_sql_for_av_cache( analytic_view_name IN VARCHAR2, lvl_seq IN dbms_hierarchy.id3_sequence, meas_seq IN dbms_hierarchy.id_sequence DEFAULT NULL, analytic_view_owner_name IN VARCHAR2 DEFAULT sys_context('USERENV', 'CURRENT_SCHEMA') include_nrows IN BOOLEAN DEFAULT FALSE) RETURN CLOB;

TBD

GET_MV_SQL_FOR_STAR_CACHE

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');

      

      DBMS_HIERARCHY.IS_NUMERIC('20')

      -------------------------------

                                    1

      

      SELECT dbms_hierarchy.is_numeric('20.6');

      

      DBMS_HIERARCHY.IS_NUMERIC('20.6')

      ---------------------------------

                                      1

      

      SELECT dbms_hierarchy.is_numeric('20A6');

      

      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

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_SCHEMA'), log_table_name IN VARCHAR2 DEFAULT NULL, log_table_owner_name IN VARCHAR2 DEFAULT sys_context('USERENV', 'CURRENT_SCHEMA'), skip hiers IN VARCHAR2 DEFAULT 'N', error_threshold IN NUMBER DEFAULT 100) RETURN NUMBER;

SELECT dbms_hierarchy.validate_analytic_view('UWAVIEW', 'C##UWCLASS', 'UWLOGTAB', 'C##UWCLASS');

      *

      ORA-18307: analytic view "C##UWCLASS"."UWAVIEW" does not exist

VALIDATE_CHECK_SUCCESS

Undocumented dbms_hierarchy.validate_check_success( topobj_name IN VARCHAR2, topobj_owner IN VARCHAR2 DEFAULT sys_context('USERENV', 'CURRENT_SCHEMA'), log_number IN NUMBER, log_table_name IN VARCHAR2 DEFAULT NULL, log_table_owner_name IN VARCHAR2 DEFAULT sys_context('USERENV', 'CURRENT_SCHEMA')) 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_SCHEMA'), log_table_name IN VARCHAR2 DEFAULT NULL, log_table_owner_name IN VARCHAR2 DEFAULT sys_context('USERENV', 'CURRENT_SCHEMA'), error_threshold IN NUMBER DEFAULT 100) RETURN NUMBER;

TBD