Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

User-Written Programs Looked For by Oracle OLAP

Oracle OLAP looks for the Oracle OLAP programs with the following names and executes them as explained in the topic for each program.


AUTOGO
ONATTACH
ONDETACH
PERMIT_READ
PERMIT_WRITE
TRIGGER_AFTER_UPDATE
TRIGGER_AW
TRIGGER_BEFORE_UPDATE
TRIGGER_DEFINE

AUTOGO

An AUTOGO program is a program that you can create and that Oracle OLAP checks for by name when an AW ATTACH command executes.

When you attach an analytic workspace that contains a program named AUTOGO, unless the AW ATTACH statement includes an NOAUTOGO clause or an AUTOGO clause that specifies a program with a different name, Oracle OLAP executes the ONATTACH program.

Note:

Oracle OLAP checks for this program and other programs when a user attaches an analytic workspace. See "Startup Programs" for more information.

Return Value

BOOLEAN

TRUE when Oracle OLAP has successfully set up and attached the analytic workspace; or FALSE when it has not or when the Autogo program has thrown an exception.

Note:

You are encouraged to use the normal return values rather than relying on exceptions to create a return value of FALSE.

Syntax

To define a program with the name AUTOGO use the syntax shown in DEFINE PROGRAM. Code the actual program as a user-defined function with the following argument.

AUTOGO (password)

Parameters

See AW ATTACH for explanation of password.

Examples

For examples of how attachment programs behave, see Example 9-50, "Startup Programs".


ONATTACH

An ONATTACH program is a program that you can create and that Oracle OLAP checks for by name when an AW ATTACH command executes.

When you attach an analytic workspace that contains a program named ONATTACH, unless the AW ATTACH statement includes an NOONATTACH clause or an ONATTACH clause that specifies a program with a different name, Oracle OLAP executes the ONATTACH program.

Depending on the statements in the onattach program, the user is granted or denied access to specific objects or sets of object values. For multiwriter attachment, you can use ACQUIRE commands to provide access to individual workspace objects. For read-only and read/write attachment, you can use PERMIT commands that grant or restrict access to individual workspace objects. All of the objects referred to in a given onattach program must exist in the same analytic workspace.

Note:

Oracle OLAP checks for this program and other programs when a user attaches an analytic workspace. See "Startup Programs" for more information.

Return Value

BOOLEAN

TRUE when Oracle OLAP has successfully set up and attached the analytic workspace; or FALSE when it has not or when the onattach program has thrown an exception.

Note:

You are encouraged to use the normal return values rather than relying on exceptions to create a return value of FALSE.

Syntax

To define a program with the name ONATTACH use the syntax shown in DEFINE PROGRAM. Code the actual program as a user-defined function with the following argument.

ONATTACH ({READ|WRITE|EXCLUSIVE|MULTI} password)

Parameters

See AW ATTACH for explanations of the attachment modes (that is, READ, WRITE, EXCLUSIVE, and MULTI) and password.

Usage Notes

ONATTACH Programs Created by the OLAP API

When an analytic workspace is created as an OLAP cube using the OLAP API, the OLAP API may also create a program named ONATTACH. You can not modify an ONATTACH program that is automatically created in this way. Additionally, overriding the execution of this ONATTACH is not recommended.

Consequently, when this type of ONATTACH program exists in an analytic workspace, create a different type of startup program to specify behavior that you want performed when that analytic workspace is attached.

Examples

For examples of how attachment programs behave, see Example 9-50, "Startup Programs".


ONDETACH

An ONDETACH program is a program that you can create and that Oracle OLAP checks for by name when an AW DETACH command executes. Depending on the value returned by the program, Oracle OLAP executes the code within the program immediately after detaching the analytic workspace.

Note:

Oracle OLAP checks for other programs when a user attaches an analytic workspace. See "Startup Programs" for more information.

Return Value

BOOLEAN

TRUE when Oracle OLAP has successfully detached the analytic workspace; or FALSE when it has not or when the detach program has thrown an exception.

Note:

You are encouraged to use the normal return values rather than relying on exceptions to create a return value of FALSE.

Syntax

To define a program with the name ONDETACH use the syntax shown in DEFINE PROGRAM.


PERMIT_READ

A PERMIT_READ program is a program that you can create and that Oracle OLAP checks for by name when an AW ATTACH read-only command executes. Depending on the value returned by the program, Oracle OLAP executes the code within the program after attaching the analytic workspace. Depending on the statements in the permit_read program the user is granted or denied access to specific objects or sets of object values. Within permit_read program, you can specify PERMIT commands that grant or restrict access to individual workspace objects. All of the objects referred to in a given permit_read must exist in the same analytic workspace.

Note:

Oracle OLAP checks for this program and other programs when a user attaches an analytic workspace. See "Startup Programs" for more information.

See also:

PERMITERROR option, PERMITREADERROR option, and PERMIT_WRITE program

Return Value

BOOLEAN

TRUE when Oracle OLAP has successfully set up and attached the analytic workspace; or FALSE when it has not or when the permit_read program has thrown an exception

Note:

You are encouraged to use the normal return values rather than relying on exceptions to create a return value of FALSE.

Syntax

To define a program with the name PERMIT_READ use the syntax shown in DEFINE PROGRAM. Code the actual program as a user-defined function with the following argument.

PERMIT_READ (password)

Parameters

See AW ATTACH for an explanation of password. When a user specifies a password when attaching the analytic workspace, then the password is passed as an argument to the program for processing.

Examples

To see the order in which permission programs are executed when an analytic workspace is attached, see Example 9-50, "Startup Programs".


PERMIT_WRITE

A PERMIT_WRITE program is a program that you can create and that Oracle OLAP checks for by name when an AW ATTACH read/write command executes. Depending on the value returned by the program, Oracle OLAP executes the code within the program after attaching the analytic workspace. Depending on the statements in the permit_write program, the user is granted or denied access to specific objects or sets of object values. Within permit_write program, you can specify PERMIT commands that grant or restrict access to individual workspace object. All of the objects referred to in a given permit_write program must exist in the same analytic workspace.

Note:

Oracle OLAP checks for this program and other programs when a user attaches an analytic workspace. See "Startup Programs" for more information.

See also:

PERMITERROR option, PERMITREADERROR option, and PERMIT_READ program

Return Value

BOOLEAN

TRUE when Oracle OLAP has successfully set up and attached the analytic workspace; or FALSE when it has not or when the permit_write program has thrown an exception

Note:

You are encouraged to use the normal return values rather than relying on exceptions to create a return value of FALSE.

Syntax

To define a program with the name PERMIT_WRITE use the syntax shown in DEFINE PROGRAM. Code the actual program as a user-defined function with the following argument.

PERMIT_WRITE (password)

Parameters

See AW ATTACH for an explanation of password. When a user specifies a password when attaching the analytic workspace, then the password is passed as an argument to the program for processing.

Examples

To see the order in which permission programs are executed when an analytic workspace is attached, see Example 9-50, "Startup Programs".


TRIGGER_AFTER_UPDATE

A TRIGGER_AFTER_UPDATE program is a program that you can create in an analytic workspace and that Oracle OLAP checks for by name when an UPDATE command for that analytic workspace executes. When the program exists in the same analytic workspace that you are updating, Oracle OLAP executes the program after executing the UPDATE.

Note:

The USETRIGGERS option must be set to its default value of TRUE for a TRIGGER_AFTER_UPDATE program to execute

See also:

"Trigger Programs".

Syntax

To create a program with the name TRIGGER_AFTER_UPDATE, follow the guidelines presented in "Trigger Programs".

Examples

Example 6-1 TRIGGER_AFTER_UPDATE Program

Assume you have defined the following program in your analytic workspace.

DEFINE TRIGGER_AFTER_UPDATE PROGRAM
PROGRAM
SHOW JOINCHARS ('calltype = ' CALLTYPE)
SHOW JOINCHARS ('triggering event = ' TRIGGER(EVENT))
SHOW JOINCHARS ('triggering subevent = ' TRIGGER(SUBEVENT))
END
 

When you issue an UPDATE statement the program executes and displays the following output.

calltype = TRIGGER
triggering event = AFTER_UPDATE
triggering subevent = AW

TRIGGER_AW

A TRIGGER_AW program is a program that you can create in one analytic workspace and that Oracle OLAP checks for by name when that analytic workspace is current and you create, attach, detach, or delete any other analytic workspace.

Note:

Oracle OLAP checks for this program and other programs when a user attaches an analytic workspace. See "Startup Programs" for more information.

See also:

"Trigger Programs".

Return Value

None.

Syntax

To create a program with the name TRIGGER_AW, follow the guidelines presented in"How to Create a TRIGGER_AW Program".

Usage Notes

How to Create a TRIGGER_AW Program

You create a TRIGGER_AW program by following the following procedure:

  1. Define the program as described in DEFINE PROGRAM.

  2. Name the program TRIGGER_AW.

  3. Code the actual program as described in"Specifying Program Contents".

    Note:

    There is no support for recursive triggers. You must set the USETRIGGERS option to NO before you issue an AW statement within an TRIGGER_AW program

Examples

Example 6-2 A TRIGGER_AW Program

Assume that you have defined a program with the following definition in an analytic workspace named my_aw.

DEFINE TRIGGER_AW PROGRAM
PROGRAM
SHOW CALLTYPE
SHOW TRIGGER(EVENT)
SHOW TRIGGER(SUBEVENT)
SHOW TRIGGER(NAME)
END

When attach the my_aw workspace, the specified values are displayed.

AW ATTACH MY_AW

TRIGGER
AW
ATTACH
MY_AW 

TRIGGER_BEFORE_UPDATE

A TRIGGER_BEFORE_UPDATE program is a program that you can create and that Oracle OLAP checks for by name when an UPDATE command executes. When the program exists in the same analytic workspace that you are updating, Oracle OLAP executes the program and then, depending on the value returned by the program (if any), either does nor does not update the workspace.

Note:

The USETRIGGERS option must be set to its default value of TRUE for a TRIGGER_BEFORE_UPDATE program to execute

See also:

"Trigger Programs".

Return Value

You can write the program as a function that returns a BOOLEAN value. In this case, when the program returns FALSE, Oracle OLAP does not execute the UPDATE statement that triggered the execution of the TRIGGER_BEFORE_UPDATE program; when the program returns TRUE or NA, the UPDATE statement executes.

Syntax

To create a program with the name TRIGGER_UPDATE, follow the guidelines presented in"Trigger Programs".

Examples

Example 6-3 TRIGGER_BEFORE_UPDATE Program

Assume that an analytic workspace named myaw has an TRIGGER_BEFORE_UPDATE program with the following definition.

DEFINE TRIGGER_BEFORE_UPDATE PROGRAM BOOLEAN
PROGRAM
SHOW JOINCHARS ('calltype = ' CALLTYPE)
SHOW JOINCHARS ('triggering event = ' TRIGGER(EVENT))
SHOW JOINCHARS ('triggering subevent = ' TRIGGER(SUBEVENT))
RETURN TRUE
END
 

Assume that you define a TEXT variable named myvar and, then, issue an UPDATE statement. The TRIGGER_BEFORE_UPDSATE program executes.

calltype = TRIGGER
triggering event = BEFORE_UPDATE
triggering subevent = AW

Because the program returned TRUE, the definition for myvar exists after you detach and reattach the workspace.

AW DETACH myaw
AW ATTACH myaw
DESCRIBE

DEFINE TRIGGER_BEFORE_UPDATE PROGRAM BOOLEAN
PROGRAM
SHOW JOINCHARS ('calltype = ' CALLTYPE)
SHOW JOINCHARS ('triggering event = ' TRIGGER(EVENT))
SHOW JOINCHARS ('triggering subevent = ' TRIGGER(SUBEVENT))
RETURN TRUE
END
 
DEFINE MYVAR VARIABLE TEXT

However, if you modified the program so that it returned FALSE, then when you detach and reattach the workspace, not only would the myvar definition not in the workspace, the definition for the TRIGGER_BEFORE_UPDATE program would also not be in the workspace.


TRIGGER_DEFINE

A TRIGGER_DEFINE program is a program that you create and that Oracle OLAP checks for by name when a DEFINE command executes. When the program exists in the same analytic workspace in which you are defining a new object, Oracle OLAP executes the program.

Note:

The USETRIGGERS option must be set to its default value of TRUE for a TRIGGER_DEFINE program to execute

See also:

"Trigger Programs".

Syntax

To create a program with the name TRIGGER_DEFINE, follow the guidelines presented in "Trigger Programs".

Examples

Example 6-4 A TRIGGER_DEFINE Program

Assume that you have written a TRIGGER_DEFINE program with the following description in your analytic workspace.

DEFINE TRIGGER_DEFINE PROGRAM
PROGRAM
SHOW JOINCHARS ('calltype = ' CALLTYPE)
SHOW JOINCHARS ('triggering event = ' TRIGGER(EVENT))
SHOW JOINCHARS ('fully qualified object name ='TRIGGER(NAME))
SHOW JOINCHARS ('type of object = 'OBJ(TYPE TRIGGER(NAME))
DESCRIBE &TRIGGER(NAME)
END

Assume, as shown in the following statements, that you issue a DEFINE VARIABLE statement to define a variable named myvar. As shown by the output following the statement, Oracle OLAP defines the variable and executes the TRIGGER_DEFINE program.

DEFINE myvar VARIABLE TEXT
calltype = TRIGGER
triggering event = DEFINE
fully qualified object name =MYAW!MYVAR
type of object = VARIABLE
 
DEFINE MYVAR VARIABLE TEXT