Oracle | DBMS_AW Demos, Syntax, and Example Code

Oracle DBMS_AW Version 21c

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 Defines the interfaces of the OLAP option package. It is also identified as the interface to the Express server routines.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value

NO_HIER	BINARY_INTEGER	0
MEASURE	BINARY_INTEGER	1
HIER_PARENTCHILD	BINARY_INTEGER	2
HIER_LEVELS	BINARY_INTEGER	3
HIER_SNOWFLAKE	BINARY_INTEGER	4

PARTBY_DEFAULT	BINARY_INTEGER	0
PARTBY_NONE	BINARY_INTEGER	1
PARTBY_FORCE	BINARY_INTEGER	2147483647

ADVICE_DEFAULT	BINARY_INTEGER	0
ADVICE_FAST	BINARY_INTEGER	1
ADVICE_FULL	BINARY_INTEGER	2
ADVICE_NOSAMPLE	BINARY_INTEGER	3

EIFIMP_DATA	BINARY_INTEGER	1
EIFIMP_DEFINES	BINARY_INTEGER	2
EIFIMP_DFNS	BINARY_INTEGER	EIFIMP_DEFINES
EIFIMP_DATADEFINES	BINARY_INTEGER	3
EIFIMP_DATADFNS	BINARY_INTEGER	EIFIMP_DATADEFINES

Data Types CREATE OR REPLACE TYPE sys.dbms_aw$_columnlist_t AS TABLE OF VARCHAR2(100) / CREATE OR REPLACE TYPE sys.dbms_aw$_dimension_source_t AS OBJECT ( dimname VARCHAR2(100), columnname VARCHAR2(100), sourcevalue VARCHAR2(32767), dimtype NUMBER(3,0), hiercols dbms_aw$_columnlist_t, partby NUMBER(10,0)) / CREATE OR REPLACE TYPE dbms_aw$_dimension_sources_t AS TABLE OF dbms_aw$_dimension_source_t / TYPE eif_t IS TABLE OF BLOB NOT NULL; TYPE eif_objlist_t IS TABLE OF VARCHAR2(100); TYPE loblineiter_t IS RECORD ( mylob CLOB, loc NUMBER, clength NUMBER, cmax NUMBER, linemax NUMBER); /

Dependencies

ALL_AWS	DBMS_AW$_COLUMNLIST_T	DBMS_LOB
ALL_AW_PROP	DBMS_AW$_DIMENSION_SOURCES_T	DBMS_OUTPUT
ALL_PART_TABLES	DBMS_AW$_DIMENSION_SOURCE_T	DBMS_STANDARD
ALL_TABLES	DBMS_AW_EXP	DBMS_STATS
APS_VALIDATE	DBMS_AW_INTERNAL	DBMS_UTILITY
AW$	DBMS_AW_LIB	PLITBLM
DBA_AW_PROP	DBMS_CUBE	USER_AW_PROP
DBA_TABLESPACES	DBMS_CUBE_EXP	V$OPTION
DBMS_ASSERT	DBMS_CUBE_LOG	V$PARAMETER

Documented No

Exceptions

Error Code	Reason
ORA-20001	en_tbs_error
ORA-20003	aw_changed_error
ORA-20004	awname_null_error
ORA-20005	has_schema_error
ORA-20006	bad_snowflake_error
ORA-34373	Schema name contains unsupported characters
awname_is_null	Package header declared named exception

First Available 10.2

Security Model Owned by SYS with EXECUTE granted to PUBLIC

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

Subprograms

ADD_DIMENSION_SOURCE	EIF_BLOB_OUT	OLAP_ACTIVE
ADVISE_CUBE	EIF_DELETE	OLAP_ON
ADVISE_DIMENSIONALITY	EIF_IN	OLAP_RUNNING
ADVISE_PARTITIONING_DIMENSION	EIF_OUT	OLAP_TYPE
ADVISE_PARTITIONING_LEVEL	EVAL_NUMBER	PARSE_AW_NAME
ADVISE_REL	EVAL_TEXT	PARSE_SCHEMA_NAME
ADVISE_SPARSITY	EXECUTE	PRINTLOG
AW_ATTACH	GATHER_STATS	PROP_CLOB
AW_COPY	GETLOG	PROP_LEN
AW_CREATE	GET_OBJ_PROTECT	PROP_VAL
AW_DELETE	INFILE	RUN
AW_DETACH	INITDRIVER	SHUTDOWN
AW_IS_SYSTEM	INTERP	SPARSITY_ADVICE_TABLE
AW_RENAME	INTERPCLOB	STARTUP
AW_TABLESPACE	INTERP_SILENT	TOGGLEDBCREATE
AW_UPDATE	IN_AW_CLEANUP	VALIDATE_AW_NAME
CONVERT	MOVE_AWMETA	VALIDATE_SCHEMA_NAME
EIF_BLOB_IN

ADD_DIMENSION_SOURCE

Undocumented dbms_aw.add_dimension_source(dimname IN VARCHAR2, colname IN VARCHAR2, sources IN OUT dbms_aw$_dimension_sources_t, srcval IN VARCHAR2 DEFAULT NULL, dimtype IN NUMBER DEFAULT NO_HIER, hiercols IN dbms_aw$_columnlist_t DEFAULT NULL, partby IN NUMBER DEFAULT PARTBY_DEFAULT);

TBD

ADVISE_CUBE

Undocumented dbms_aw.advise_cube( aggmap IN VARCHAR2, pct IN BINARY_INTEGER DEFAULT 20, compressed IN BOOLEAN DEFAULT FALSE);

TBD

ADVISE_DIMENSIONALITY

Undocumented

Overload 1 dbms_aw.advise_dimensionality( cubename IN VARCHAR2, sparsedfn OUT VARCHAR2, sparsename IN VARCHAR2 DEFAULT NULL, partnum IN NUMBER DEFAULT 1, advtable IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;

TBD

Overload 2 dbms_aw.advise_dimensionality( output OUT NOCOPY CLOB, cubename IN VARCHAR2, sparsename IN VARCHAR2 DEFAULT NULL, dtype IN VARCHAR2 DEFAULT 'NUMBER', advtable IN VARCHAR2 DEFAULT NULL);

TBD

ADVISE_PARTITIONING_DIMENSION

Undocumented dbms_aw.advise_partitioning_dimension( cubename IN VARCHAR2, dimsources IN dbms_aw$_dimension_sources_t, advtable IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;

TBD

ADVISE_PARTITIONING_LEVEL

Undocumented dbms_aw.advise_partitioning_level( cubename IN VARCHAR2, dimsources IN dbms_aw$_dimension_sources_t, advtable IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;

TBD

ADVISE_REL

Undocumented dbms_aw.advise_rel( relname IN VARCHAR2, valueset IN VARCHAR2, pct IN BINARY_INTEGER DEFAULT 20, compressed IN BOOLEAN DEFAULT FALSE);

TBD

ADVISE_SPARSITY

Undocumented dbms_aw.advise_sparsity( fact IN VARCHAR2, cubename IN VARCHAR2, dimsources IN dbms_aw$_dimension_sources_t, advmode IN BINARY_INTEGER DEFAULT ADVICE_DEFAULT, partby IN BINARY_INTEGER DEFAULT PARTBY_DEFAULT, advtable IN VARCHAR2 DEFAULT NULL);

TBD

AW_ATTACH

Undocumented

Overload 1 dbms_aw.aw_attach( name IN VARCHAR2, forwrite IN BOOLEAN DEFAULT FALSE, createaw IN BOOLEAN DEFAULT FALSE, attargs IN VARCHAR2 DEFAULT NULL, tbspace IN VARCHAR2 DEFAULT NULL);

TBD

Overload 2 dbms_aw.aw_attach( schema IN VARCHAR2, name IN VARCHAR2, forwrite IN BOOLEAN DEFAULT FALSE, createaw IN BOOLEAN DEFAULT FALSE, attargs IN VARCHAR2 DEFAULT NULL, tbspace IN VARCHAR2 DEFAULT NULL);

TBD

AW_COPY

Undocumented

Overload 1 dbms_aw.aw_copy( oldname IN VARCHAR2, newname IN VARCHAR2, newtablespace IN VARCHAR2 DEFAULT NULL, partnum IN NUMBER DEFAULT 8);

TBD

Overload 2 dbms_aw.aw_copy( oldschema IN VARCHAR2, oldname IN VARCHAR2, newname IN VARCHAR2, newtablespace IN VARCHAR2 DEFAULT NULL, partnum IN NUMBER DEFAULT 8);

TBD

AW_CREATE

Undocumented

Overload 1 dbms_aw.aw_create( name IN VARCHAR2, tbspace IN VARCHAR2 DEFAULT NULL, partnum IN NUMBER DEFAULT 8);

TBD

Overload 2 dbms_aw.aw_create( schema IN VARCHAR2, name IN VARCHAR2, tbspace IN VARCHAR2 DEFAULT NULL);

TBD

AW_DELETE

Undocumented

Overload 1 dbms_aw.aw_delete(name IN VARCHAR2);

TBD

Overload 2 dbms_aw.aw_delete(schema IN VARCHAR2, name IN VARCHAR2);

TBD

AW_DETACH

Undocumented

Overload 1 dbms_aw.aw_detach(name IN VARCHAR2);

TBD

Overload 2 dbms_aw.aw_detach(schema IN VARCHAR2, name IN VARCHAR2);

TBD

AW_IS_SYSTEM

Undocumented: Clearly some poorly written code. The function should have returned FALSE rather than demonstrating the coder's inability to trap for a NO_DATA condition with an EXCEPTION clause. dbms_aw.aw_is_system( schema IN VARCHAR2, name IN VARCHAR2) RETURN BOOLEAN;

BEGIN

        IF dbms_aw.aw_is_system('ABC', 'DEF') THEN

          dbms_output.put_line('T');

        ELSE

          dbms_output.put_line('F');

        END IF;

      END;

      /

      BEGIN

      *

      ERROR at line 1:

      ORA-01403: no data found

      ORA-06512: at "SYS.DBMS_AW", line 1390

      ORA-06512: at line 2

AW_RENAME

Undocumented dbms_aw.aw_rename(inname IN VARCHAR2, outname IN VARCHAR2);

TBD

AW_TABLESPACE

Undocumented

Overload 1 dbms_aw.aw_tablespace(schema IN VARCHAR2, name IN VARCHAR2) RETURN VARCHAR2;

TBD

Overload 2 dbms_aw.aw_tablespace(name IN VARCHAR2) RETURN VARCHAR2;

TBD

AW_UPDATE

Undocumented
Overload 1 dbms_aw.aw_update(name IN VARCHAR2 DEFAULT NULL);

exec dbms_aw.aw_update;

Overload 2 dbms_aw.aw_update(schema IN VARCHAR2, name IN VARCHAR2);

TBD

CONVERT

Undocumented
Overload 1 dbms_aw.convert(awname IN VARCHAR2);

TBD

Overload 2 dbms_aw.convert( oldname IN VARCHAR2, newname IN VARCHAR2, newtablespace IN VARCHAR2 DEFAULT NULL);

TBD

EIF_BLOB_IN

Undocumented

Overload 1 dbms_aw.eif_blob_in( name IN VARCHAR2, implob IN BLOB, datadfns IN BINARY_INTEGER DEFAULT EIFIMP_DATA, objlist IN eif_objlist_t DEFAULT NULL api IN BOOLEAN DEFAULT TRUE);

TBD

Overload 2 dbms_aw.eif_blob_in( schema IN VARCHAR2, name IN VARCHAR2, implob IN BLOB, datadfns IN BINARY_INTEGER DEFAULT EIFIMP_DATA, objlist IN eif_objlist_t DEFAULT NULL, api IN BOOLEAN DEFAULT TRUE);

TBD

EIF_BLOB_OUT

Undocumented
Overload 1 dbms_aw.eif_blob_out( name IN VARCHAR2, objlist IN eif_objlist_t DEFAULT NULL, api IN BOOLEAN DEFAULT TRUE) RETURN BLOB;

TBD

Overload 2 dbms_aw.eif_blob_out( schema IN VARCHAR2, name IN VARCHAR2, objlist IN eif_objlist_t DEFAULT NULL, api IN BOOLEAN DEFAULT TRUE) RETURN BLOB;

TBD

EIF_DELETE

Undocumented dbms_aw.eif_delete(eif IN OUT eif_t);

TBD

EIF_IN

Undocumented
Overload 1 dbms_aw.eif_in( name IN VARCHAR2, impeif IN eif_t, datadfns IN BINARY_INTEGER DEFAULT EIFIMP_DATA, objlist IN eif_objlist_t DEFAULT NULL);

TBD

Overload 2 dbms_aw.eif_in( schema IN VARCHAR2, name IN VARCHAR2, impeif IN eif_t, datadfns IN BINARY_INTEGER DEFAULT EIFIMP_DATA, objlist IN eif_objlist_t DEFAULT NULL);

TBD

EIF_OUT

Undocumented
Overload 1 dbms_aw.eif_out( name IN VARCHAR2, expeif OUT eif_t, objlist IN eif_objlist_t DEFAULT NULL);

TBD

Overload 2 dbms_aw.eif_out( schema IN VARCHAR2, name IN VARCHAR2, expeif OUT eif_t, objlist IN eif_objlist_t DEFAULT NULL);

TBD

EVAL_NUMBER

Undocumented dbms_aw.eval_number(cmd IN STRING) RETURN NUMBER;

TBD

EVAL_TEXT

Undocumented dbms_aw.eval_text(cmd IN STRING) RETURN VARCHAR2;

TBD

EXECUTE

Executes an OLAP DML command and uses dbms_output to print the results dbms_aw.execute(cmd IN STRING);

TBD

GATHER_STATS

Undocumented dbms_aw.gather_stats;

exec dbms_aw.gather_stats;

GETLOG

Undocumented dbms_aw.getlog RETURN CLOB;

SELECT dbms_aw.getlog

      FROM dual;

GET_OBJ_PROTECT

Undocumented dbms_aw.get_obj_protect RETURN BOOLEAN;

DECLARE

       b BOOLEAN;

      BEGIN

        IF dbms_aw.get_obj_protect THEN

          dbms_output.put_line('T');

        ELSE

          dbms_output.put_line('F');

        END IF;

      END;

      /

INFILE

Undocumented dbms_aw.infile(ifilename IN STRING);

TBD

INITDRIVER

Undocumented dbms_aw.initdriver;

exec dbms_aw.initdriver;

INTERP

Interprets an OLAP DML command and returns the output dbms_aw.interp(cmd IN string) RETURN CLOB;

TBD

INTERPCLOB

Interprets an OLAP DML command and returns the output dbms_aw.interpclob(cmd_clob IN CLOB) RETURN CLOB;

TBD

INTERP_SILENT

Interprets an OLAP DML command dbms_aw.interp_silent(cmd IN STRING);

TBD

IN_AW_CLEANUP

Undocumented dbms_aw.in_aw_cleanup RETURN BOOLEAN;

set serveroutput on

      

      BEGIN

        IF dbms_aw.in_aw_cleanup THEN

          dbms_output.put_line('TRUE');

        ELSE

          dbms_output.put_line('FALSE');

        END IF;

      END;

      /

MOVE_AWMETA

Undocumented dbms_aw.move_awmeta(dest_tbs IN VARCHAR2);

TBD

OLAP_ACTIVE

Returns TRUE if OLAP services are active: Otherwise FALSE. dbms_aw.olap_active RETURN BOOLEAN;

set serveroutput on

      

      BEGIN

        IF dbms_aw.olap_active THEN

          dbms_output.put_line('TRUE');

        ELSE

          dbms_output.put_line('FALSE');

        END IF;

      END;

      /

OLAP_ON

Returns TRUE if OLAP services are enabled: Otherwise FALSE. dbms_aw.olap_on RETURN BOOLEAN;

set serveroutput on

      

      BEGIN

        IF dbms_aw.olap_on THEN

          dbms_output.put_line('TRUE');

        ELSE

          dbms_output.put_line('FALSE');

        END IF;

      END;

      /

OLAP_RUNNING

Returns TRUE if OLAP services are running: Otherwise FALSE. dbms_aw.olap_running RETURN BOOLEAN;

set serveroutput on

      

      BEGIN

        IF dbms_aw.olap_running THEN

          dbms_output.put_line('TRUE');

        ELSE

          dbms_output.put_line('FALSE');

        END IF;

      END;

      /

OLAP_TYPE

Undocumented dbms_aw.olap_type(otype IN NUMBER) RETURN VARCHAR2;

TBD

PARSE_AW_NAME

Undocumented dbms_aw.parse_aw_name( inschema IN VARCHAR2, inawname IN VARCHAR2, schema OUT VARCHAR2, awname OUT VARCHAR2, defschema OUT BOOLEAN);

TBD

PARSE_SCHEMA_NAME

Undocumented dbms_aw.parse_schema_name( inschema IN VARCHAR2, schema OUT VARCHAR2, defschema OUT BOOLEAN);

DECLARE

       retSch VARCHAR2(30);

       retDef BOOLEAN;

      BEGIN

        dbms_aw.parse_schema_name('C##SH', retSch, retDef);

        dbms_output.put_line(retSch);

      END;

      /

      C##SH

PRINTLOG

Undocumented dbms_aw.printlog(log_clob IN CLOB);

TBD

PROP_CLOB

Undocumented dbms_aw.prop_clob(rid IN ROWID) RETURN CLOB;

TBD

PROP_LEN

Undocumented dbms_aw.prop_len(rid IN ROWID) RETURN NUMBER;

TBD

PROP_VAL

Undocumented dbms_aw.prop_val(rid IN ROWID) RETURN VARCHAR2;

TBD

RUN

Handles output for the user
Overload 1 dbms_aw.run(cmd IN STRING, silent IN BOOLEAN DEFAULT FALSE);

TBD

Handles output for the user
Overload 2 dbms_aw.run(cmd IN CLOB, silent IN BOOLEAN DEFAULT FALSE);

TBD

Passes data
Overload 3 dbms_aw.run(cmd IN STRING, output OUT STRING);

TBD

Passes data
Overload 4 dbms_aw.run(cmd IN STRING, output IN OUT NOCOPY CLOB);

TBD

Passes data
Overload 5 dbms_aw.run(cmd IN CLOB, output OUT STRING);

TBD

Passes data
Overload 6 dbms_aw.run(cmd IN CLOB, output IN OUT NOCOPY CLOB);

TBD

SHUTDOWN

Undocumented dbms_aw.shutdown(force IN BOOLEAN DEFAULT FALSE);

exec dbms_aw.shutdown(TRUE);

SPARSITY_ADVICE_TABLE

Undocumented dbms_aw.sparsity_advice_table(tblname IN VARCHAR2 DEFAULT NULL);

TBD

STARTUP

Undocumented dbms_aw.startup;

exec dbms_aw.startup;

TOGGLEDBCREATE

Undocumented dbms_aw.toggledbcreate;

exec dbms_aw.toggledbcreate;

VALIDATE_AW_NAME

Validates whether an AW name would be valid if used ... not that it is already used dbms_aw.validate_aw_name(awname IN VARCHAR2);

exec dbms_aw.validate_aw_name('ZZYZX');

      

      PL/SQL procedure successfully completed.

      

      exec dbms_aw.validate_aw_name('ZZY ZX');

      BEGIN dbms_aw.validate_aw_name('ZZY ZX'); END;

      *

      ERROR at line 1:

      ORA-34376: ZZY ZX is not a valid analytic workspace name. Analytic workspace names must be 124 characters or less, can contain only letters, digits,

      underscores, and dollar signs, and cannot begin with a digit or dollar sign.

      ORA-06512: at "SYS.DBMS_AW", line 26

      ORA-06512: at "SYS.DBMS_AW", line 887

      ORA-06512: at line 1

VALIDATE_SCHEMA_NAME

Raises an exception if the string provided is not a valid schema name dbms_aw.validate_schema_name(schema IN VARCHAR2);

exec dbms_aw.validate_schema_name('A_C');

      

      PL/SQL procedure successfully completed.

      

      exec dbms_aw.validate_schema_name('A!C');

      BEGIN dbms_aw.validate_schema_name('A!C'); END;

      *

      ERROR at line 1:

      ORA-34373: schema name contains unsupported characters

      ORA-06512: at "SYS.DBMS_AW", line 923

      ORA-06512: at line 1