Oracle DBMS_MDX_ODBO
Version 19.2.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
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
     
Documented No
Exceptions
Error Code Reason
ORA-18259 INVALID_ROWSET_TYPE
ORA-18260 INVALID_ROWSET_ARRAYS
ORA-18264 INVALID_QRY_PROPS
First Available 12.2.0.1
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);
TBD
 
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);
TBD
 
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;
/
AFTER,ALL,AND,AS,ASC,AXIS,BACK_COLOR,BASC,BDESC,BEFORE,BEFORE_AND_AFTER,CASE,
CATALOG_NAME,CELL,CELL_ORDINAL,CHAPTERS,CHILDREN_CARDINALITY,COLUMNS,CONSTRAINED,
CUBE_NAME,CUSTOM_ROLLUP,CUSTOM_ROLLUP_PROPERTIES,DESC,DESCRIPTION,DIMENSION,
DIMENSION_UNIQUE_NAME,ELSE,EMPTY,END,EXCLUDEEMPTY,FALSE,FONT_FLAGS,FONT_NAME,
FONT_SIZE,FORE_COLOR,FORMATTED_VALUE,FORMAT_STRING,FROM,HIERARCHY,
HIERARCHY_UNIQUE_NAME,ID,IIF,INCLUDEEMPTY,INCLUDE_CALC_MEMBERS,IS,IS_DATAMEMBER,
IS_PLACEHOLDERMEMBER,KEY,LANGUAGE,LEAVES,LEVEL_NUMBER,LEVEL_UNIQUE_NAME,MEMBER,
MEMBER_CAPTION,MEMBER_GUID,MEMBER_KEY,MEMBER_NAME,MEMBER_ORDINAL,MEMBER_TYPE,
MEMBER_UNIQUE_NAME,MEMBER_VALUE,NAME,NON,NOT,NULL,ON,OR,PAGES,PARENT_COUNT,
PARENT_LEVEL,PARENT_UNIQUE_NAME,POST,PROPERTIES,RECURSIVE,ROWS,SCHEMA_NAME,
SECTIONS,SELECT,SELF,SELF_AND_AFTER,SELF_AND_BEFORE,SELF_BEFORE_AFTER,SET,
SKIPPED_LEVELS,STRTOMEMBER,THEN,TRUE,UNARY_OPERATOR,UNIQUE_NAME,WHEN,WHERE,WITH,XOR
 
GET_MDSCHEMA_ACTIONS
Undocumented dbms_mdx_odbo.getmdschema_actions RETURN BINARY_INTEGER;
SELECT dbms_mdx_odbo.getmdschema_actions
FROM dual;
 
GET_MDSCHEMA_CUBES
Undocumented dbms_mdx_odbo.get_mdschema_cubes RETURN BINARY_INTEGER;
SELECT dbms_mdx_odbo.get_mdschema_cubes
FROM dual;
 
GET_MDSCHEMA_DIMENSIONS
Undocumented dbms_mdx_odbo.get_mdschema_dimensions RETURN BINARY_INTEGER;
SELECT dbms_mdx_odbo.get_mdschema_dimensions
FROM dual;
 
GET_MDSCHEMA_FUNCTIONS
Undocumented dbms_mdx_odbo.get_mdschema_functions RETURN BINARY_INTEGER;
SELECT dbms_mdx_odbo.get_mdschema_functions
FROM dual;
 
GET_MDSCHEMA_HIERARCHIES
Undocumented dbms_mdx_odbo.get_mdschema_hierarchies RETURN BINARY_INTEGER;
SELECT dbms_mdx_odbo.get_mdschema_hierarchies
FROM dual;
 
GET_MDSCHEMA_LEVELS
Undocumented dbms_mdx_odbo.get_mdschema_levels RETURN BINARY_INTEGER;
SELECT dbms_mdx_odbo.get_mdschema_levels
FROM dual;
 
GET_MDSCHEMA_MEASURES
Undocumented dbms_mdx_odbo.get_mdschema_measures RETURN BINARY_INTEGER;
SELECT dbms_mdx_odbo.get_mdschema_measures
FROM dual;
 
GET_MDSCHEMA_MEMBERS
Undocumented dbms_mdx_odbo.get_mdschema_members RETURN BINARY_INTEGER;
SELECT dbms_mdx_odbo.get_mdschema_members
FROM dual;
 
GET_MDSCHEMA_PROPERTIES
Undocumented dbms_mdx_odbo.get_mdschema_properties RETURN BINARY_INTEGER;
SELECT dbms_mdx_odbo.get_mdschema_properties
FROM dual;
 
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_SETS
Undocumented dbms_mdx_odbo.get_mdschema_sets RETURN BINARY_INTEGER;
SELECT dbms_mdx_odbo.get_mdschema_sets
FROM dual;
 
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_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
BACK_COLOR
BASC
BDESC
BEFORE
BEFORE_AND_AFTER
CASE
CATALOG_NAME
CELL
CELL_ORDINAL
CHAPTERS
CHILDREN_CARDINALITY
COLUMNS
CONSTRAINED
CUBE_NAME
CUSTOM_ROLLUP
CUSTOM_ROLLUP_PROPERTIES
DESC
DESCRIPTION
DIMENSION
DIMENSION_UNIQUE_NAME
ELSE
EMPTY
END
EXCLUDEEMPTY
FALSE
FONT_FLAGS
FONT_NAME
FONT_SIZE
FORE_COLOR
FORMATTED_VALUE
FORMAT_STRING
FROM
HIERARCHY
HIERARCHY_UNIQUE_NAME
ID
IIF
INCLUDEEMPTY
INCLUDE_CALC_MEMBERS
IS
IS_DATAMEMBER
IS_PLACEHOLDERMEMBER
KEY
LANGUAGE
LEAVES
LEVEL_NUMBER
LEVEL_UNIQUE_NAME
MEMBER
MEMBER_CAPTION
MEMBER_GUID
MEMBER_KEY
MEMBER_NAME
MEMBER_ORDINAL
MEMBER_TYPE
MEMBER_UNIQUE_NAME
MEMBER_VALUE
NAME
NON
NOT
NULL
ON
OR
PAGES
PARENT_COUNT
PARENT_LEVEL
PARENT_UNIQUE_NAME
POST
PROPERTIES
RECURSIVE
ROWS
SCHEMA_NAME
SECTIONS
SELECT
SELF
SELF_AND_AFTER
SELF_AND_BEFORE
SELF_BEFORE_AFTER
SET
SKIPPED_LEVELS
STRTOMEMBER
THEN
TRUE
UNARY_OPERATOR
UNIQUE_NAME
WHEN
WHERE
WITH
XOR

93 rows selected.
 
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_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);
 
 
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
What's New In 18cR3
What's New In 19cR2

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