Table of Contents
- Title and Copyright Information
- Preface
- Changes in This Release for Oracle OLAP DML Reference
-
1
OLAP DML Basic Concepts
- 1.1 What is the OLAP DML?
- 1.2 Basic Syntactic Units of the OLAP DML
- 1.3 How to Execute OLAP DML Statements
- 1.4 Introduction to Analytic Workspaces
- 1.5 Introduction to Analytic Workspace Data Objects
- 1.6 OLAP DML Statements Apply to All of the Values of a Data Object
- 1.7 How to Specify the Set of Data that OLAP DML Operations Work Against
- 1.8 Populating Multidimensional Hierarchical Data Objects
-
2
Data Types, Operators, and Expressions
- 2.1 OLAP DML Data Types
- 2.2 OLAP DML Operators
-
2.3
OLAP DML Expressions
- 2.3.1 About OLAP DML Expressions
-
2.3.2
Using Workspace Objects in Expressions
- 2.3.2.1 How OLAP DML Data Objects Behave in Expressions
- 2.3.2.2 Syntax for Specifying an Object in an Expression
- 2.3.2.3 Specifying Values of Dimensions and Composites in Expressions
- 2.3.2.4 Using Variables and Relations in Expressions
- 2.3.2.5 Limiting a Dimension to a Single Value Without Changing Status
- 2.3.3 Working with Empty Cells in Expressions
- 2.3.4 Numeric Expressions
- 2.3.5 Text Expressions
- 2.3.6 Datetime and Interval Expressions
- 2.3.7 Date-only Expressions
- 2.3.8 Boolean Expressions
- 2.3.9 Conditional Expressions
- 2.3.10 Substitution Expressions
- 3 Formulas, Models, Aggregations, and Allocations
-
4
OLAP DML Properties
- 4.1 About OLAP DML Properties
- 4.2 System Properties: Alphabetical Listing
- 4.3 System Properties by Category
- 4.4 $AGGMAP
- 4.5 $AGGREGATE_FORCECALC
- 4.6 $AGGREGATE_FORCEORDER
- 4.7 $AGGREGATE_FROM
- 4.8 $AGGREGATE_FROMVAR
- 4.9 $ALLOCMAP
- 4.10 $COUNTVAR
- 4.11 $DEFAULT_LANGUAGE
- 4.12 $GID_DEPTH
- 4.13 $GID_LIST
- 4.14 $GID_TYPE
- 4.15 $LOOP_AGGMAP
- 4.16 $LOOP_DENSE
- 4.17 $LOOP_TYPE
- 4.18 $LOOP_VAR
- 4.19 $NATRIGGER
- 4.20 $STORETRIGGERVAL
- 4.21 $VARCACHE
-
5
OLAP DML Options
- 5.1 About Options
- 5.2 Options: Alphabetical Listing
- 5.3 Options by Category
- 5.4 ALLOCERRLOGFORMAT
- 5.5 ALLOCERRLOGHEADER
- 5.6 AWWAITTIME
- 5.7 BADLINE
- 5.8 BMARGIN
- 5.9 CALENDARWEEK
- 5.10 COLWIDTH
- 5.11 COMMAS
- 5.12 COMPILEMESSAGE
- 5.13 COMPILEWARN
- 5.14 DATEFORMAT
- 5.15 DATEORDER
- 5.16 DAYABBRLEN
- 5.17 DAYNAMES
- 5.18 DECIMALCHAR
- 5.19 DECIMALOVERFLOW
- 5.20 DECIMALS
- 5.21 DEFAULTAWSEGSIZE
- 5.22 DIVIDEBYZERO
- 5.23 DSECONDS
- 5.24 ECHOPROMPT
- 5.25 EIFBYTES
- 5.26 EIFEXTENSIONPATH
- 5.27 EIFNAMES
- 5.28 EIFSHORTNAMES
- 5.29 EIFTYPES
- 5.30 EIFUPDBYTES
- 5.31 EIFVERSION
- 5.32 ERRNAMES
- 5.33 ERRORNAME
- 5.34 ERRORTEXT
- 5.35 ESCAPEBASE
- 5.36 EXPTRACE
- 5.37 INF_STOP_ON_ERROR
- 5.38 LCOLWIDTH
- 5.39 LIKECASE
- 5.40 LIKEESCAPE
- 5.41 LIKENL
- 5.42 LIMIT.SORTREL
- 5.43 LIMITSTRICT
- 5.44 LINENUM
- 5.45 LINESLEFT
- 5.46 LOCK_LANGUAGE_DIMS
- 5.47 LSIZE
- 5.48 MAXFETCH
- 5.49 MODDAMP
- 5.50 MODERROR
- 5.51 MODGAMMA
- 5.52 MODINPUTORDER
- 5.53 MODMAXITERS
- 5.54 MODOVERFLOW
- 5.55 MODSIMULTYPE
- 5.56 MODTOLERANCE
- 5.57 MODTRACE
- 5.58 MONTHABBRLEN
- 5.59 MONTHNAMES
- 5.60 MULTIPATHHIER
- 5.61 NASKIP
- 5.62 NASKIP2
- 5.63 NASPELL
- 5.64 NLS_CALENDAR
- 5.65 NLS_CURRENCY
- 5.66 NLS_DATE_FORMAT
- 5.67 NLS_DATE_LANGUAGE
- 5.68 NLS_DUAL_CURRENCY
- 5.69 NLS_ISO_CURRENCY
- 5.70 NLS_LANG
- 5.71 NLS_LANGUAGE
- 5.72 NLS_NUMERIC_CHARACTERS
- 5.73 NLS_SORT
- 5.74 NLS_TERRITORY
- 5.75 NOSPELL
- 5.76 OKFORLIMIT
- 5.77 OKNULLSTATUS
- 5.78 OUTFILEUNIT
- 5.79 PAGENUM
- 5.80 PAGEPRG
- 5.81 PAGESIZE
- 5.82 PAGING
- 5.83 PARENS
- 5.84 PERMITERROR
- 5.85 PERMITREADERROR
- 5.86 PRGTRACE
- 5.87 RANDOM.SEED.1 and RANDOM.SEED.2
- 5.88 RECURSIVE
- 5.89 ROLE
- 5.90 ROOTOFNEGATIVE
- 5.91 SECONDS
- 5.92 SESSCACHE
- 5.93 SESSION_NLS_LANGUAGE
- 5.94 SPARSEINDEX
- 5.95 SQLBLOCKMAX
- 5.96 SQLCODE
- 5.97 SQLERRM
- 5.98 SQLMESSAGES
- 5.99 STATIC_SESSION_LANGUAGE
- 5.100 THIS_AW
- 5.101 THOUSANDSCHAR
- 5.102 TMARGIN
- 5.103 TRACEFILEUNIT
- 5.104 TRIGGERMAXDEPTH
- 5.105 TRIGGERSTOREOK
- 5.106 USERID
- 5.107 USETRIGGERS
- 5.108 VARCACHE
- 5.109 WEEKDAYSNEWYEAR
- 5.110 WRAPERRORS
- 5.111 YESSPELL
- 5.112 YRABSTART
- 5.113 ZEROROW
- 5.114 ZSPELL
-
6
OLAP DML Programs
- 6.1 Programs Provided With the OLAP DML
- 6.2 Creating OLAP DML Programs
-
6.3
Specifying Program Contents
- 6.3.1 Creating User-Defined Functions
- 6.3.2 Passing Arguments
- 6.3.3 Program Flow-of-Control
- 6.3.4 Preserving the Environment Settings
- 6.3.5 Handling Errors
- 6.4 Compiling Programs
- 6.5 Testing and Debugging Programs
- 6.6 Executing Programs
-
6.7
Common Types of OLAP DML Programs
- 6.7.1 Startup Programs
- 6.7.2 Data Import and Export Programs
- 6.7.3 Trigger Programs
- 6.7.4 Aggregation, Allocation, and Modeling Programs
- 6.7.5 Forecasting Programs
- 6.7.6 Programs to Export and Import Workspace Objects
- 6.8 User-Written Programs Looked For by Oracle OLAP
-
7
OLAP DML Functions: A - K
- 7.1 About OLAP DML Functions
- 7.2 Functions: Alphabetical Listing
- 7.3 Functions by Category
- 7.4 ABS
- 7.5 ADD_MONTHS
- 7.6 AGGCOUNT
- 7.7 AGGMAPINFO
- 7.8 AGGREGATE function
- 7.9 AGGREGATION
- 7.10 AGGROPS
- 7.11 ALLOCOPS
- 7.12 ANTILOG
- 7.13 ANTILOG10
- 7.14 ANY
- 7.15 ARCCOS
- 7.16 ARCSIN
- 7.17 ARCTAN
- 7.18 ARCTAN2
- 7.19 ARG
- 7.20 ARGCOUNT
- 7.21 ARGFR
- 7.22 ARGS
- 7.23 ASCII
- 7.24 ASCIISTR
- 7.25 AVERAGE
- 7.26 AW function
- 7.27 BACK
- 7.28 BASEDIM
- 7.29 BASEVAL
- 7.30 BEGINDATE
- 7.31 BIN_TO_NUM
- 7.32 BITAND
- 7.33 BLANKSTRIP
- 7.34 CALLTYPE
- 7.35 CATEGORIZE
- 7.36 CEIL
- 7.37 CHANGEBYTES
- 7.38 CHANGECHARS
- 7.39 CHANGEDRELATIONS
- 7.40 CHANGEDVALUES
- 7.41 CHARLIST
- 7.42 CHARTOROWID
- 7.43 CHGDIMS
- 7.44 CHR
- 7.45 COALESCE
- 7.46 COLVAL
- 7.47 CONTEXT function
- 7.48 CONVERT
- 7.49 CORRELATION
- 7.50 COS
- 7.51 COSH
- 7.52 COUNT
- 7.53 CUMSUM
- 7.54 CURRENT_DATE
- 7.55 CURRENT_TIMESTAMP
- 7.56 DAYOF
- 7.57 DBTIMEZONE
- 7.58 DDOF
- 7.59 DECODE
- 7.60 DEPRDECL
- 7.61 DEPRDECLSW
- 7.62 DEPRSL
- 7.63 DEPRSOYD
- 7.64 ENDDATE
- 7.65 ENDOF
- 7.66 EVERY
- 7.67 EXISTS
- 7.68 EXP
- 7.69 EVERSION
- 7.70 EXTBYTES
- 7.71 EXTCHARS
- 7.72 EXTCOLS
- 7.73 EXTLINES
- 7.74 EXTRACT
- 7.75 FCOPEN
- 7.76 FCQUERY
- 7.77 FILEERROR
- 7.78 FILEGET
- 7.79 FILENEXT
- 7.80 FILEOPEN
- 7.81 FILEQUERY
- 7.82 FILTERLINES
- 7.83 FINDBYTES
- 7.84 FINDCHARS
- 7.85 FINDLINES
- 7.86 FINTSCHED
- 7.87 FLOOR
- 7.88 FPMTSCHED
- 7.89 FROM_TZ
- 7.90 GET
- 7.91 GREATEST
- 7.92 GROUPINGID function
- 7.93 GROWRATE
- 7.94 HEXTORAW
- 7.95 HIERCHECK
- 7.96 HIERHEIGHT
- 7.97 HIERSHAPE
- 7.98 INFO
- 7.99 INITCAP
- 7.100 INLIST
- 7.101 INSBYTES
- 7.102 INSCHARS
- 7.103 INSCOLS
- 7.104 INSLINES
- 7.105 INSTAT
- 7.106 INSTR functions
- 7.107 INTPART
- 7.108 IRR
- 7.109 ISDATE
- 7.110 ISINFINITE
- 7.111 ISEMPTY
- 7.112 ISNAN
- 7.113 ISSESSION
- 7.114 ISVALUE
- 7.115 JOINBYTES
- 7.116 JOINCHARS
- 7.117 JOINCOLS
- 7.118 JOINLINES
- 7.119 KEY
-
8
OLAP DML Functions: L - Z
- 8.1 LAG
- 8.2 LAGABSPCT
- 8.3 LAGDIF
- 8.4 LAGPCT
- 8.5 LARGEST
- 8.6 LAST_DAY
- 8.7 LEAD
- 8.8 LEAST
- 8.9 LENGTH functions
- 8.10 LIMIT function
- 8.11 LIMITMAPINFO
- 8.12 LNNVL
- 8.13 LOCALTIMESTAMP
- 8.14 LOG function
- 8.15 LOG10
- 8.16 LOWCASE
- 8.17 LOWER
- 8.18 LPAD
- 8.19 LTRIM
- 8.20 MAKEDATE
- 8.21 MAX
- 8.22 MAXBYTES
- 8.23 MAXCHARS
- 8.24 MEDIAN
- 8.25 MIN
- 8.26 MMOF
- 8.27 MODE
- 8.28 MODULO
- 8.29 MONTHS_BETWEEN
- 8.30 MOVINGAVERAGE
- 8.31 MOVINGMAX
- 8.32 MOVINGMIN
- 8.33 MOVINGTOTAL
- 8.34 NA2
- 8.35 NAFILL
- 8.36 NAFLAG
- 8.37 NEW_TIME
- 8.38 NEXT_DAY
- 8.39 NLS_CHARSET_ID
- 8.40 NLS_CHARSET_NAME
- 8.41 NLSSORT
- 8.42 NONE
- 8.43 NORMAL
- 8.44 NPV
- 8.45 NULLIF
- 8.46 NUMBYTES
- 8.47 NUMCHARS
- 8.48 NUMLINES
- 8.49 NUMTODSINTERVAL
- 8.50 NUMTOYMINTERVAL
- 8.51 NVL
- 8.52 NVL2
- 8.53 OBJ
- 8.54 OBJLIST
- 8.55 OBJORG
- 8.56 OBSCURE
- 8.57 ORA_HASH
- 8.58 PARTITION
- 8.59 PARTITIONCHECK
- 8.60 PERCENTAGE
- 8.61 QUAL
- 8.62 RANDOM
- 8.63 RANK
- 8.64 RAWTOHEX
- 8.65 RECNO
- 8.66 REGEXP_COUNT
- 8.67 REGEXP_INSTR
- 8.68 REGEXP_REPLACE
- 8.69 REGEXP_SUBSTR
- 8.70 REM
- 8.71 REMAINDER
- 8.72 REMBYTES
- 8.73 REMCHARS
- 8.74 REMCOLS
- 8.75 REMLINES
- 8.76 REPLACE
- 8.77 REPLBYTES
- 8.78 REPLCHARS
- 8.79 REPLCOLS
- 8.80 REPLLINES
- 8.81 RESERVED
- 8.82 ROUND
- 8.83 ROW function
- 8.84 ROWIDTOCHAR
- 8.85 ROWIDTONCHAR
- 8.86 RPAD
- 8.87 RTRIM
- 8.88 RUNTOTAL
- 8.89 SESSIONTIMEZONE
- 8.90 SIGN
- 8.91 SIN
- 8.92 SINH
- 8.93 SMALLEST
- 8.94 SMOOTH
- 8.95 SORT function
- 8.96 SORTLINES
- 8.97 SOUNDEX
- 8.98 SQLFETCH
- 8.99 SQRT
- 8.100 STARTOF
- 8.101 STATALL
- 8.102 STATCURR
- 8.103 STATDEPTH
- 8.104 STATEQUAL
- 8.105 STATFIRST
- 8.106 STATLAST
- 8.107 STATLEN
- 8.108 STATLIST
- 8.109 STATMAX
- 8.110 STATMIN
- 8.111 STATRANK
- 8.112 STATVAL
- 8.113 STDDEV
- 8.114 SUBSTR functions
- 8.115 SUBTOTAL
- 8.116 SYS_CONTEXT
- 8.117 SYSDATE
- 8.118 SYSINFO
- 8.119 SYSTEM
- 8.120 SYSTIMESTAMP
- 8.121 TALLY
- 8.122 TAN
- 8.123 TANH
- 8.124 TCONVERT
- 8.125 TEXTFILL
- 8.126 TO_BINARY_DOUBLE
- 8.127 TO_BINARY_FLOAT
- 8.128 TO_CHAR
- 8.129 TO_DATE
- 8.130 TO_DSINTERVAL
- 8.131 TO_NCHAR
- 8.132 TO_NUMBER
- 8.133 TO_TIMESTAMP
- 8.134 TO_TIMESTAMP_TZ
- 8.135 TO_YMINTERVAL
- 8.136 TOD
- 8.137 TODAY
- 8.138 TOTAL
- 8.139 TRANSLATE
- 8.140 TRIGGER function
- 8.141 TRIM
- 8.142 TRUNCATE
- 8.143 TZ_OFFSET
- 8.144 UNIQUELINES
- 8.145 UNRAVEL
- 8.146 UPPER
- 8.147 UPPER
- 8.148 VALSPERPAGE
- 8.149 VALUES
- 8.150 VINTSCHED
- 8.151 VPMTSCHED
- 8.152 VSIZE
- 8.153 WEEKOF
- 8.154 WIDTH_BUCKET
- 8.155 WKSDATA
- 8.156 WRITABLE
- 8.157 YYOF
-
9
OLAP DML Commands: A-G
- 9.1 About OLAP DML Commands
- 9.2 Commands: Alphabetical Listing
- 9.3 Commands by Category
- 9.4 ACQUIRE
- 9.5 ACROSS
- 9.6 ADD_CUBE_MODEL
- 9.7 ADD_DIMENSION_MEMBER
- 9.8 ADD_MODEL_DIMENSION
- 9.9 AGGMAP
- 9.10 AGGMAP ADD or REMOVE model
- 9.11 AGGMAP SET
- 9.12 AGGREGATE command
- 9.13 ALLCOMPILE
- 9.14 ALLOCATE
- 9.15 ALLOCMAP
- 9.16 ALLSTAT
- 9.17 ARGUMENT
- 9.18 AW command
- 9.19 AWDESCRIBE
- 9.20 BLANK
- 9.21 BREAK
- 9.22 CALL
- 9.23 CDA
- 9.24 CHGDFN
- 9.25 CLEAR
- 9.26 COMMIT
- 9.27 COMPILE
- 9.28 CONSIDER
- 9.29 CONTEXT command
- 9.30 CONTINUE
- 9.31 COPYDFN
- 9.32 CREATE_LOGICAL_MODEL
- 9.33 DATE_FORMAT
- 9.34 DBGOUTFILE
- 9.35 DEFINE
- 9.36 DELETE
- 9.37 DESCRIBE
- 9.38 DO ... DOEND
- 9.39 EDIT
- 9.40 EQ
- 9.41 EXPORT
- 9.42 FCCLOSE
- 9.43 FCEXEC
- 9.44 FCSET
- 9.45 FETCH
- 9.46 FILECLOSE
- 9.47 FILECOPY
- 9.48 FILEDELETE
- 9.49 FILEMOVE
- 9.50 FILEPAGE
- 9.51 FILEPUT
- 9.52 FILEREAD
- 9.53 FILESET
- 9.54 FILEVIEW
- 9.55 FOR
- 9.56 FORECAST
- 9.57 FORECAST.REPORT
- 9.58 FULLDSC
- 9.59 GOTO
- 9.60 GROUPINGID command
-
10
OLAP DML Commands: H-Z
- 10.1 HEADING
- 10.2 HIDE
- 10.3 HIERDEPTH
- 10.4 HIERHEIGHT command
- 10.5 IF...THEN...ELSE command
- 10.6 IMPORT
- 10.7 INFILE
- 10.8 LD
- 10.9 LIMIT command
- 10.10 LIMIT BASEDIMS
- 10.11 LISTBY
- 10.12 LISTFILES
- 10.13 LISTNAMES
- 10.14 LOAD
- 10.15 LOG command
- 10.16 MAINTAIN
- 10.17 MODEL
- 10.18 MODEL.COMPRPT
- 10.19 MODEL.DEPRT
- 10.20 MODEL.XEQRPT
- 10.21 MONITOR
- 10.22 MOVE
- 10.23 OUTFILE
- 10.24 PAGE
- 10.25 PARSE
- 10.26 PERMIT
- 10.27 PERMITRESET
- 10.28 POP
- 10.29 POPLEVEL
- 10.30 PROGRAM
- 10.31 PROPERTY
- 10.32 PUSH
- 10.33 PUSHLEVEL
- 10.34 RECAP
- 10.35 REDO
- 10.36 REEDIT
- 10.37 REGRESS
- 10.38 REGRESS.REPORT
- 10.39 RELATION command
- 10.40 RELEASE
- 10.41 REMOVE_CUBE_MODEL
- 10.42 REMOVE_DIMENSION_MEMBER
- 10.43 REMOVE_MODEL_DIMENSION
- 10.44 RENAME
- 10.45 REPORT
- 10.46 RESYNC
- 10.47 RETURN
- 10.48 REVERT
- 10.49 ROW command
- 10.50 SET
- 10.51 SET1
- 10.52 SET_INCLUDED_MODEL
- 10.53 SET_PROPERTY
- 10.54 SHOW
- 10.55 SIGNAL
- 10.56 SLEEP
- 10.57 SORT command
- 10.58 SQL
- 10.59 STATUS
- 10.60 STDHDR
- 10.61 SWITCH command
- 10.62 TEMPSTAT
- 10.63 TRACE
- 10.64 TRACKPRG
- 10.65 TRAP
- 10.66 TRIGGER command
- 10.67 TRIGGERASSIGN
- 10.68 UNHIDE
- 10.69 UPDATE
- 10.70 UPDATE_ATTRIBUTE_VALUE
- 10.71 UPDATE_DIMENSION_MEMBER
- 10.72 VARIABLE
- 10.73 VNF
- 10.74 WHILE
- 10.75 ZEROTOTAL
- A OLAP_TABLE SQL Functions
-
B
DBMS_AW PL/SQL Package
- B.1 Managing Analytic Workspaces
- B.2 Embedding OLAP DML in SQL Statements
- B.3 Using the Sparsity Advisor
- B.4 Using the Aggregate Advisor
- B.5 Summary of DBMS_AW Subprograms
- B.6 ADD_DIMENSION_SOURCE Procedure
- B.7 ADVISE_CUBE Procedure
- B.8 ADVISE_DIMENSIONALITY Function
- B.9 ADVISE_DIMENSIONALITY Procedure
- B.10 ADVISE_PARTITIONING_DIMENSION Function
- B.11 ADVISE_PARTITIONING_LEVEL Function
- B.12 ADVISE_REL Procedure
- B.13 ADVISE_SPARSITY Procedure
- B.14 AW_ATTACH Procedure
- B.15 AW_COPY Procedure
- B.16 AW_CREATE Procedure
- B.17 AW_DELETE Procedure
- B.18 AW_DETACH Procedure
- B.19 AW_RENAME Procedure
- B.20 AW_TABLESPACE Function
- B.21 AW_UPDATE Procedure
- B.22 CONVERT Procedure
- B.23 EVAL_NUMBER Function
- B.24 EVAL_TEXT Function
- B.25 EXECUTE Procedure
- B.26 GETLOG Function
- B.27 INFILE Procedure
- B.28 INTERP Function
- B.29 INTERPCLOB Function
- B.30 INTERP_SILENT Procedure
- B.31 OLAP_ON Function
- B.32 OLAP_RUNNING Function
- B.33 PRINTLOG Procedure
- B.34 RUN Procedure
- B.35 SHUTDOWN Procedure
- B.36 SPARSITY_ADVICE_TABLE Procedure
- B.37 STARTUP Procedure
- C OLAP_API_SESSION_INIT PL/SQL Package
- D Changes in Previous Releases
- Index