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

Related Topics
Built-in Functions
Built-in Packages
DBMS_MDX_UTIL
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx