Oracle | DBMS_AW Demos, Syntax, and Example Code

Oracle DBMS_AW Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?

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_IN	OLAP_ACTIVE
ADVISE_CUBE	EIF_BLOB_OUT	OLAP_ON
ADVISE_DIMENSIONALITY	EIF_DELETE	OLAP_RUNNING
ADVISE_PARTITIONING_DIMENSION	EIF_IN	OLAP_TYPE
ADVISE_PARTITIONING_LEVEL	EIF_OUT	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

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;

      /

      *

      ORA-01403: no data found

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

      

      PL/SQL procedure successfully completed.

GETLOG

Undocumented dbms_aw.getlog RETURN CLOB;

SELECT dbms_aw.getlog;

      

      GETLOG

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

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;

      /

      T

      

      PL/SQL procedure successfully completed.

INFILE

Undocumented dbms_aw.infile(ifilename IN STRING);

TBD

INITDRIVER

Undocumented dbms_aw.initdriver;

exec dbms_aw.initdriver;

      

      PL/SQL procedure successfully completed.

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;

      /

      FALSE

      

      PL/SQL procedure successfully completed.

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;

      /

      FALSE

      

      PL/SQL procedure successfully completed.

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;

      /

      TRUE

      

      PL/SQL procedure successfully completed.

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;

      /

      TRUE

      

      PL/SQL procedure successfully completed.

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

      

      PL/SQL procedure successfully completed.

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

      

      PL/SQL procedure successfully completed.

SPARSITY_ADVICE_TABLE

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

TBD

STARTUP

Undocumented dbms_aw.startup;

exec dbms_aw.startup;

      

      PL/SQL procedure successfully completed.

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');

      *

      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.

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');

           *

      ORA-34373: schema name contains unsupported characters