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 will be available on generic Linux platforms in January and soon on AIX and Windows

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