Oracle | DBMS_MDX

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

      FROM dual;

      

      GET_MDSCHEMA_ACTIONS

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

                         1

GET_MDSCHEMA_CUBES

Undocumented dbms_mdx_odbo.get_mdschema_cubes RETURN BINARY_INTEGER;

SELECT dbms_mdx_odbo.get_mdschema_cubes

      FROM dual;

      

      GET_MDSCHEMA_CUBES

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

                       2

GET_MDSCHEMA_DIMENSIONS

Undocumented dbms_mdx_odbo.get_mdschema_dimensions RETURN BINARY_INTEGER;

SELECT dbms_mdx_odbo.get_mdschema_dimensions

      FROM dual;

      

      GET_MDSCHEMA_DIMENSIONS

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

                            3

GET_MDSCHEMA_FUNCTIONS

Undocumented dbms_mdx_odbo.get_mdschema_functions RETURN BINARY_INTEGER;

SELECT dbms_mdx_odbo.get_mdschema_functions

      FROM dual;

      

      
      GET_MDSCHEMA_FUNCTIONS

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

                           4

GET_MDSCHEMA_HIERARCHIES

Undocumented dbms_mdx_odbo.get_mdschema_hierarchies RETURN BINARY_INTEGER;

SELECT dbms_mdx_odbo.get_mdschema_hierarchies

      FROM dual;

      

      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

      FROM dual;

      

      GET_MDSCHEMA_MEASURES

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

                          7

GET_MDSCHEMA_MEMBERS

Undocumented dbms_mdx_odbo.get_mdschema_members RETURN BINARY_INTEGER;

SELECT dbms_mdx_odbo.get_mdschema_members

      FROM dual;

      

      GET_MDSCHEMA_MEMBERS

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

                         9

GET_MDSCHEMA_PROPERTIES

Undocumented dbms_mdx_odbo.get_mdschema_properties RETURN BINARY_INTEGER;

SELECT dbms_mdx_odbo.get_mdschema_properties

      FROM dual;

      

      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

      FROM dual;

      

      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

      FROM dual;

      

      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

      FROM dual;

      

      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

      FROM dual;

      

      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