Oracle | DBMS_MDX

Oracle DBMS_MDX_ODBO Version 23c

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 Multi-Dimensional SQL ODBO package for MDX support. PL/SQL definitions to support OLE DB for MDX.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
General
MDX_DATE	VARCHAR2(8)	'MDX_DATE'
MDX_NUMBER	VARCHAR2(10)	'MDX_NUMBER'
MDSCHEMA_ACTIONS	BINARY_INTEGER	1
MDSCHEMA_CUBES	BINARY_INTEGER	2
MDSCHEMA_DIMENSIONS	BINARY_INTEGER	3
MDSCHEMA_FUNCTIONS	BINARY_INTEGER	4
MDSCHEMA_HIERARCHIES	BINARY_INTEGER	5
MDSCHEMA_LEVELS	BINARY_INTEGER	6
MDSCHEMA_MEASURES	BINARY_INTEGER	7
MDSCHEMA_PROPERTIES	BINARY_INTEGER	8
MDSCHEMA_MEMBERS	BINARY_INTEGER	9
MDSCHEMA_SETS	BINARY_INTEGER	10
MDSCHEMA_ROWSET_MAX	BINARY_INTEGER	11

Data Types TYPE odbo_boolean_sequence IS VARRAY(32767) OF NUMBER; TYPE odbo_short_sequence IS VARRAY(32767) OF NUMBER; TYPE odbo_number_sequence IS VARRAY(32767) OF NUMBER; TYPE odbo_string_sequence IS VARRAY(32767) OF VARCHAR2(10922);

Dependencies

ALL_ANALYTIC_VIEW_BASE_MEAS	DBA_ATTRIBUTE_DIM_ATTRS	MDX_ODBO_DIMENSIONS
ALL_ANALYTIC_VIEW_CALC_MEAS	DBA_ATTRIBUTE_DIM_KEYS	MDX_ODBO_FUNCTIONS
ALL_ANALYTIC_VIEW_DIMENSIONS	DBA_ATTRIBUTE_DIM_TABLES	MDX_ODBO_HIERARCHIES
ALL_ANALYTIC_VIEW_HIERS	DBA_HIER_COLUMNS	MDX_ODBO_LEVELS
ALL_ANALYTIC_VIEW_LEVELS	DBMS_HCS_LIB	MDX_ODBO_MEASURES
ALL_ATTRIBUTE_DIM_TABLES	DBMS_MDX_ODBO_FUNCTION_T	MDX_ODBO_PROPERTIES
ALL_TABLES	DBMS_MDX_ODBO_KEYWORD_T	PLITBLM
ALL_TAB_COLUMNS	DBMS_MDX_ODBO_PROPVAL_T

Documented No

Exceptions

Error Code	Reason
ORA-18250	The input parameter to the MDX PL/SQL function is invalid
ORA-18252	There are no active MDX queries
ORA-18259	INVALID_ROWSET_TYPE
ORA-18260	INVALID_ROWSET_ARRAYS
ORA-18264	INVALID_QRY_PROPS

First Available 12.2

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsodbo.sql

Subprograms

CLOSE	GET_MDSCHEMA_MEASURES	MDX_GET_DIMENSION_CARDINALITY
CLOSE_SCHEMA_ROWSET	GET_MDSCHEMA_MEMBERS	MDX_GET_HIERARCHY_CARDINALITY
CONVERT_FORMAT_STRING	GET_MDSCHEMA_PROPERTIES	MDX_GET_LEVEL_CARDINALITY
EXECUTE	GET_MDSCHEMA_ROWSET_MAX	MDX_GET_MEASURE_CARDINALITY
GET_AXIS_DATA	GET_MDSCHEMA_SETS	MDX_HIERARCHY_INST_SELECTION
GET_CELL_DATA	GET_MDX_DATE_TYPE	MDX_HIERARCHY_STRUCTURE
GET_DSO_PROPERTIES	GET_MDX_FUNCTION_NAMES	MDX_LEVEL_UNIQUE_SETTINGS
GET_KEYWORDS	GET_MDX_KEYWORD_NAMES	MDX_MEASURE_AGGREGATOR
GET_MDSCHEMA_ACTIONS	GET_MDX_NUMBER_TYPE	MDX_ORIGIN
GET_MDSCHEMA_CUBES	GET_MDX_PROPERTY_VALUES	MDX_PROPERTY_CONTENT_TYPE
GET_MDSCHEMA_DIMENSIONS	GET_SCHEMA_ROWSET	MDX_PROPERTY_ORIGIN
GET_MDSCHEMA_FUNCTIONS	MDX_COMPONENT_ID	MDX_PROPERTY_TYPE
GET_MDSCHEMA_HIERARCHIES	MDX_DATATYPE	MDX_STRIP_COMPONENT
GET_MDSCHEMA_LEVELS	MDX_DIMENSION_TYPE

Undocumented dbms_mdx_odbo.close(query_id IN NUMBER);

exec dbms_mdx_odbo.close(1);

      BEGIN dbms_mdx_odbo.close(1); END;

      *

      ERROR at line 1:

      ORA-18252: There are no active MDX queries.

      ORA-06512: at "SYS.DBMS_MDX_ODBO", line 81

      ORA-06512: at "SYS.DBMS_MDX_ODBO", line 1498

      ORA-06512: at line 1

CLOSE_SCHEMA_ROWSET

Undocumented dbms_mdx_odbo.close_schema_rowset( rowset_type IN NUMBER, rowset IN OUT sys_refcursor);

TBD

CONVERT_FORMAT_STRING

Undocumented dbms_mdx_odbo.convert_format_string( orcl_fmt_str IN VARCHAR2, datatype IN VARCHAR2) RETURN VARCHAR2;

TBD

EXECUTE

Undocumented dbms_mdx_odbo.execute( mdx_str IN VARCHAR2, query_properties IN odbo_string_sequence, column_axis OUT SYS_REFCURSOR, row_axis OUT SYS_REFCURSOR, page_axis OUT SYS_REFCURSOR, chapter_axis OUT SYS_REFCURSOR, section_axis OUT SYS_REFCURSOR, slicer OUT SYS_REFCURSOR, mdx_info OUT CLOB, query_id OUT NUMBER);

TBD

GET_AXIS_DATA

Undocumented dbms_mdx_odbo.get_axis_data( query_id IN NUMBER, axis_index IN NUMBER, axis_data OUT sys_refcursor);

TBD

GET_CELL_DATA

Undocumented dbms_mdx_odbo.get_cell_data( query_id IN NUMBER, cell_range IN odbo_number_sequence, cell_data OUT sys_refcursor);

TBD

GET_DSO_PROPERTIES

Undocumented dbms_mdx_odbo.get_dso_properties(mdpropvals OUT odbo_short_sequence);

DECLARE

       outVal dbms_mdx_odbo.odbo_short_sequence;

      BEGIN

        dbms_mdx_odbo.get_dso_properties(outVal);

        FOR i IN 1 .. outVal.COUNT LOOP

          dbms_output.put_line(outVal(i));

        END LOOP;

      END;

      /

      0

      0

      4

      0

      7

      30

      1

      15

      0

      7

      376

      0

      1

      262143

      2

      15

      3

      1

      2

      0

      7

      

      PL/SQL procedure successfully completed.

GET_KEYWORDS

Returns a comma delimited list of keywords dbms_mdx_odbo.get_keywords(keywords OUT VARCHAR2);

DECLARE

       outVal VARCHAR2(4000);

      BEGIN

        dbms_mdx_odbo.get_keywords(outVal);

        dbms_output.put_line(outVal);

      END;

      /

      ADDCALCULATEDMEMBERS,AFTER,AGGREGATE,ALL,ANCESTOR,ANCESTORS,AND,AS,ASC,

      ASCENDANTS,AVG,AXIS,BACK_COLOR,BASC,BDESC,BEFORE,BEFORE_AND_AFTER,BOTTOMCOUNT,

      BOTTOMPERCENT,BOTTOMSUM,CASE,CATALOG_NAME,CELL,CELL_ORDINAL,CHAPTERS,CHILDREN,

      CHILDREN_CARDINALITY, CLOSINGPERIOD, COALESCEEMPTY,COLUMNS,CONSTRAINED,CORRELATION,

      COUNT,COUSIN,COVARIANCE, COVARIANCEN,CROSSJOIN,CUBE_NAME,CURRENTMEMBER,CUSTOM_ROLLUP,

      CUSTOM_ROLLUP_PROPERTIES,DEFAULTMEMBER, DESC,DESCENDANTS, DESCRIPTION,DIMENSION,

      DIMENSION_UNIQUE_NAME,DISTINCT,DRILLDOWNLEVEL, DRILLDOWNLEVELBOTTOM,

      DRILLDOWNLEVELTOP,DRILLDOWNMEMBER,DRILLDOWNMEMBERBOTTOM,DRILLDOWNMEMBERTOP,

      DRILLUPLEVEL, DRILLUPMEMBER,ELSE,EMPTY,END,EXCEPT,EXCLUDEEMPTY,EXISTS,EXTRACT,FALSE,

      FILTER,FIRSTCHILD,FIRSTSIBLING,FONT_FLAGS,FONT_NAME,FONT_SIZE,FORE_COLOR,

      FORMATTED_VALUE,FORMAT_STRING,FROM,GENERATE,HEAD,HIERARCHIZE,HIERARCHY,

      HIERARCHY_UNIQUE_NAME,ID,IIF,INCLUDEEMPTY,INCLUDE_CALC_MEMBERS,INTERSECT,IS,ISEMPTY,

      IS_DATAMEMBER,IS_PLACEHOLDERMEMBER,ITEM,KEY,LAG, LANGUAGE,LASTCHILD,LASTPERIODS,

      LASTSIBLING,LEAD,LEAVES,LEVEL,LEVELS,LEVEL_NUMBER, LEVEL_UNIQUE_NAME,LINREGINTERCEPT,

      LINREGPOINT,LINREGR2,LINREGSLOPE,LINREGVARIANCE,MAX,MEDIAN,MEMBER,MEMBERS,MEMBER_CAPTION,
 
      MEMBER_GUID,MEMBER_KEY,MEMBER_NAME,MEMBER_ORDINAL,MEMBER_TYPE,MEMBER_UNIQUE_NAME,

      MEMBER_VALUE,MIN,MTD,NAME,NEXTMEMBER,NON,NONEMPTYCROSSJOIN,NOT,NULL,ON,OPENINGPERIOD,OR,

      ORDER,ORDINAL, PAGES,PARALLELPERIOD,PARENT,PARENT_COUNT,PARENT_LEVEL,PARENT_UNIQUE_NAME,

      PERIODSTODATE, POST,PREVMEMBER,PROPERTIES,QTD,RANK,RECURSIVE,ROWS,SCHEMA_NAME,SECTIONS,

      SELECT,SELF,SELF_AND_AFTER,SELF_AND_BEFORE,SELF_BEFORE_AFTER,SET,SIBLINGS,SKIPPED_LEVELS,

      STDDEV,STDDEVP,STDEV,STDEVP,STRTOMEMBER,SUM,TAIL,THEN,TOGGLEDRILLSTATE,TOPCOUNT,

      TOPPERCENT,TOPSUM,TRUE,UNARY_OPERATOR,UNION,UNIQUENAME,UNIQUE_NAME,VALUE,VAR,VARIANCE,

      VARIANCEP,VARP,WHEN,WHERE,WITH,WTD,XOR,YTD

GET_MDSCHEMA_ACTIONS

Undocumented dbms_mdx_odbo.get)mdschema_actions RETURN BINARY_INTEGER;

SELECT dbms_mdx_odbo.get_mdschema_actions;

      

      GET_MDSCHEMA_ACTIONS

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

                         1

GET_MDSCHEMA_CUBES

Undocumented dbms_mdx_odbo.get_mdschema_cubes RETURN BINARY_INTEGER;

SELECT dbms_mdx_odbo.get_mdschema_cubes;

      

      GET_MDSCHEMA_CUBES

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

                       2

GET_MDSCHEMA_DIMENSIONS

Undocumented dbms_mdx_odbo.get_mdschema_dimensions RETURN BINARY_INTEGER;

SELECT dbms_mdx_odbo.get_mdschema_dimensions;

      

      GET_MDSCHEMA_DIMENSIONS

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

                            3

GET_MDSCHEMA_FUNCTIONS

Undocumented dbms_mdx_odbo.get_mdschema_functions RETURN BINARY_INTEGER;

SELECT dbms_mdx_odbo.get_mdschema_functions;

      

      GET_MDSCHEMA_FUNCTIONS

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

                           4

GET_MDSCHEMA_HIERARCHIES

Undocumented dbms_mdx_odbo.get_mdschema_hierarchies RETURN BINARY_INTEGER;

SELECT dbms_mdx_odbo.get_mdschema_hierarchies;

      

      GET_MDSCHEMA_HIERARCHIES

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

                             5

GET_MDSCHEMA_LEVELS

Undocumented dbms_mdx_odbo.get_mdschema_levels RETURN BINARY_INTEGER;

SELECT dbms_mdx_odbo.get_mdschema_levels

      FROM dual;

      

      GET_MDSCHEMA_LEVELS

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

                        6

GET_MDSCHEMA_MEASURES

Undocumented dbms_mdx_odbo.get_mdschema_measures RETURN BINARY_INTEGER;

SELECT dbms_mdx_odbo.get_mdschema_measures;

      

      GET_MDSCHEMA_MEASURES

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

                          7

GET_MDSCHEMA_MEMBERS

Undocumented dbms_mdx_odbo.get_mdschema_members RETURN BINARY_INTEGER;

SELECT dbms_mdx_odbo.get_mdschema_members;

      

      GET_MDSCHEMA_MEMBERS

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

                         9

GET_MDSCHEMA_PROPERTIES

Undocumented dbms_mdx_odbo.get_mdschema_properties RETURN BINARY_INTEGER;

SELECT dbms_mdx_odbo.get_mdschema_properties;

      

      GET_MDSCHEMA_PROPERTIES

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

                            8

GET_MDSCHEMA_ROWSET_MAX

Undocumented dbms_mdx_odbo.get_mdschema_rowset_max RETURN BINARY_INTEGER;

SELECT dbms_mdx_odbo.get_mdschema_rowset_max;

      

      GET_MDSCHEMA_ROWSET_MAX

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

                           11

GET_MDSCHEMA_SETS

Undocumented dbms_mdx_odbo.get_mdschema_sets RETURN BINARY_INTEGER;

SELECT dbms_mdx_odbo.get_mdschema_sets;

      

      GET_MDSCHEMA_SETS

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

                    10

GET_MDX_DATE_TYPE

Undocumented dbms_mdx_odbo.get_mdx_date_type RETURN VARCHAR2;

SELECT dbms_mdx_odbo.get_mdx_date_type;

      

      GET_MDX_DATE_TYPE

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

      MDX_DATE

GET_MDX_FUNCTION_NAMES

Undocumented dbms_mdx_odbo.get_mdx_function_names RETURN sys.dbms_mdx_odbo_function_t PIPELINED;

SELECT * FROM TABLE(dbms_mdx_odbo.get_mdx_function_names);

      

      Returns 102 rows with the following column names

      FUNCTION_NAME, CAPTION, DESCRIPTION, PARAM_LIST, INTERFACE_NAME, OBJECT

GET_MDX_KEYWORD_NAMES

Returns a list of keywords dbms_mdx_odbo.get_mdx_keyword_names RETURN sys.dbms_mdx_odbo_keyword_t PIPELINED;

SELECT * FROM TABLE(dbms_mdx_odbo.get_mdx_keyword_names);

      

      KEYWORD_NAME

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

      AFTER

      ALL

      AND

      AS

      ASC

      AXIS

      ...

      WHEN

      WHERE

      WITH

      WTD

      XOR

      YTD

      

      Returns 183 rows

GET_MDX_NUMBER_TYPE

Undocumented dbms_mdx_odbo.get_mdx_number_type RETURN VARCHAR2;

SELECT dbms_mdx_odbo.get_mdx_number_type;

      

      GET_MDX_NUMBER_TYPE

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

      MDX_NUMBER

GET_MDX_PROPERTY_VALUES

Undocumented dbms_mdx_odbo.get_mdx_property_values RETURN sys.dbms_mdx_odbo_propval_t PIPELINED;

SELECT * FROM TABLE(dbms_mdx_odbo.get_mdx_property_values);

      

      PROPERTY_VALUE

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

                   0

                   0

                   4

                   0

                   7

                  30

                   1

                  15

                   0

                   7

                 376

                   0

                   1

              262143

                   2

                  15

                   3

                   1

                   2

                   0

                   7

      

      21 rows selected.

GET_SCHEMA_ROWSET

Undocumented dbms_mdx_odbo.get_schema_rowset( rowset_type IN NUMBER, restrictions IN odbo_string_sequence, empty IN odbo_boolean_sequence, rowset OUT sys_refcursor, query_properties IN odbo_string_sequence);

TBD

MDX_COMPONENT_ID

Undocumented dbms_mdx_odbo.mdx_component_id( component1 IN VARCHAR2, component2 IN VARCHAR2 DEFAULT NULL, component3 IN VARCHAR2 DEFAULT NULL, component4 IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;

TBD

MDX_DATATYPE

Undocumented dbms_mdx_odbo.mdx_datatype (orclDt IN VARCHAR2) RETURN NUMBER DETERMINISTIC;

TBD

MDX_DIMENSION_TYPE

Returns the integer identifier for a dimension type

If this wasn't an unsupported package I would open an SR dbms_mdx_odbo.mdx_dimension_type(dimtype IN VARCHAR2) RETURN INTEGER DETERMINISTIC;

BEGIN

        dbms_output.put_line(dbms_mdx_odbo.mdx_dimension_type('STANDARD'));

      END;

      /

      BEGIN

      *

      ERROR at line 1:

      ORA-06592: CASE not found while executing CASE statement

      ORA-06512: at "SYS.DBMS_MDX_ODBO", line 503

      ORA-06512: at line 2

      

      SELECT dbms_mdx_odbo.mdx_dimension_type('TIME')

      FROM dual;

      SELECT dbms_mdx_odbo.mdx_dimension_type('TIME')

      *

      ERROR at line 1:

      ORA-06592: CASE not found while executing CASE statement

      ORA-06512: at "SYS.DBMS_MDX_ODBO", line 503

MDX_GET_DIMENSION_CARDINALITY

Returns Dimension Cardinality dbms_mdx_odbo.mdx_get_dimension_cardinality( cubeowner IN VARCHAR2, cubename IN VARCHAR2, dimalias IN VARCHAR2) RETURN INTEGER DETERMINISTIC;

TBD

MDX_GET_HIERARCHY_CARDINALITY

Returns the Hierarchy Cardinality dbms_mdx_odbo.mdx_get_hierarchy_cardinality( cubeowner IN VARCHAR2, cubename IN VARCHAR2, dimalias IN VARCHAR2, hierarchyalias IN VARCHAR2) RETURN INTEGER DETERMINISTIC;

TBD

MDX_GET_LEVEL_CARDINALITY

Returns Level Cardinality dbms_mdx_odbo.mdx_get_level_cardinality( cubeowner IN VARCHAR2, cubename IN VARCHAR2, dimalias IN VARCHAR2, hierarchyalias IN VARCHAR2, levelname IN VARCHAR2) RETURN INTEGER DETERMINISTIC;

TBD

MDX_GET_MEASURE_CARDINALITY

Returns Measure Cardinality dbms_mdx_odbo.mdx_get_measure_cardinality( cubeowner IN VARCHAR2, cubename IN VARCHAR2) RETURN INTEGER DETERMINISTIC;

TBD

MDX_HIERARCHY_INST_SELECTION

Returns Hierarchy Instance Selections dbms_mdx_odbo.mdx_hierarchy_inst_selection(selectionType IN VARCHAR2) RETURN INTEGER DETERMINISTIC;

TBD

MDX_HIERARCHY_STRUCTURE

Returns Hierarchy Structures dbms_mdx_odbo.mdx_hierarchy_structure(structuretype IN VARCHAR2) RETURN INTEGER DETERMINISTIC;

TBD

MDX_LEVEL_UNIQUE_SETTINGS

Returns Level Unique Settings dbms_mdx_odbo.mdx_level_unique_settings(type IN VARCHAR2) RETURN INTEGER DETERMINISTIC;

TBD

MDX_MEASURE_AGGREGATOR

Returns the Measure Aggregation Value dbms_mdx_odbo.mdx_measure_aggregator(aggtype IN VARCHAR2) RETURN INTEGER DETERMINISTIC;

TBD

MDX_ORIGIN

Returns the Hierarchy and Level Origins dbms_mdx_odbo.mdx_origin(origintype IN VARCHAR2) RETURN INTEGER DETERMINISTIC;

TBD

MDX_PROPERTY_CONTENT_TYPE

Returns the Property Content Type dbms_mdx_odbo.mdx_property_content_type(propertytype IN VARCHAR2) RETURN INTEGER DETERMINISTIC;

TBD

MDX_PROPERTY_ORIGIN

Returns the Property Origins dbms_mdx_odbo.mdx_property_origin(propertytype IN VARCHAR2) RETURN INTEGER DETERMINISTIC;

TBD

MDX_PROPERTY_TYPE

Returns Property Types dbms_mdx_odbo.mdx_property_type(propertytype IN VARCHAR2) RETURN INTEGER DETERMINISTIC;

TBD

MDX_STRIP_COMPONENT

Undocumented dbms_mdx_odbo.mdx_strip_component(id IN VARCHAR2) RETURN VARCHAR2;

TBD