B Discoverer EUL Trigger Information

This section provides information about Oracle Business Intelligence Discoverer EUL triggers, and contains the following topics:

What are Oracle Business Intelligence Discoverer EUL triggers?
List of Oracle Business Intelligence Discoverer EUL triggers
How to register a PL/SQL function that can be executed by a Discoverer EUL trigger
Example for using a PL/SQL trigger to extract SQL from a worksheet when it is saved

B.1 What are Oracle Business Intelligence Discoverer EUL triggers?

An Oracle Business Intelligence Discoverer EUL trigger is a database call that executes a specific PL/SQL function when specific actions occur in Discoverer (for example, when a user saves a workbook).

Notes:

Discoverer enables EUL triggers to be executed by default unless the EnableTriggers registry setting is in the registry, and its value is set to 0. For more information about Discoverer registry settings, see Chapter 21, "Discoverer Registry Settings"
An EUL trigger is only executed if a PL/SQL function is registered correctly in Discoverer Administrator with a specific display name (for example, eul_trigger$post_save_document). For more information, see "How to register a PL/SQL function that can be executed by a Discoverer EUL trigger".
An EUL trigger is only executed if the PL/SQL function arguments are defined for each EUL trigger (for more information, see List of Oracle Business Intelligence Discoverer EUL triggers).

B.2 List of Oracle Business Intelligence Discoverer EUL triggers

The table below lists the EUL triggers you can use with Discoverer, when they fire, their arguments, whether they are required or optional and their suggested uses.

Trigger name	When it fires	Arguments (Datatype)	Arguments Required or Optional?	Suggested Uses
eul_trigger$post_login	After a successful login.	Function Return Datatype (Number) No Arguments. Note: The number returned from this function is not used.	N/A	Set up the user environment context. For example, to enforce row level security based on the database or SSO username.
eul_trigger$post_save_document	When a workbook is saved	Function Return Datatype (Number) WorkbookOwner (Varchar) WorkbookName (Varchar) WorksheetName (Varchar) Sequence (Varchar) SQLSegment (Varchar)	Required Required Required Required Required	Automatically extract SQL from a Discoverer worksheet and save it into a pre-defined table in the database.
eul_trigger$pre_delete_document	Before a workbook is deleted from the database.	Function Return Datatype (Number) WorkbookOwner (Varchar) WorkbookName (Varchar)	Required Required	Preserve the name and owner of the deleted workbook.
eul_trigger$refresh_new_column	When a refresh operation finds a new column.	Function Return Datatype (Varchar) DBLink (Varchar) Schema (Varchar) Table (Varchar) Column Name (Varchar) EUL Language (Varchar)	Required Required Required Required Required	Return the proper display name of an Oracle Applications field.
eul_trigger$get_developer_key	When you load a business area from the Gateway or Online Dictionary, and before the object is created within the database	Function Return Datatype (Varchar) Default Developer Key (Varchar) Element Type (Varchar) DB Link (Varchar) Schema (Varchar) Table (Varchar) Constraint / Column (Varchar)	Required Required Required Required Required Required Prepend the default Identifier with a product short name. For example, a folder based on the table EMPLOYEE (default Identifier: EMPLOYEE), maintained by HRI product, should have the Identifier: HRI_EMPLOYEE	Prepend the default Identifier with a product short name. For example, a folder based on the table EMPLOYEE (default Identifier: EMPLOYEE), maintained by HRI product, should have the Identifier: HRI_EMPLOYEE

B.3 How to register a PL/SQL function that can be executed by a Discoverer EUL trigger

You might want to register a PL/SQL function that carries out a particular function (for example, to extract SQL from a worksheet when it is saved). An Oracle Business Intelligence Discoverer EUL trigger can only execute a PL/SQL function if the PL/SQL function exists in the database, and is registered correctly in Discoverer Administrator.

Note: A PL/SQL function must be registered with the same name as the Discoverer EUL trigger. If it is not, the Discoverer EUL trigger will neither find the function nor execute it. Therefore, the name in the Display Name field of the PL/SQL function must be set to the PL/SQL function name (for example, eul_trigger$post_save_document).

To register a PL/SQL function that can be executed by an Oracle Business Intelligence Discoverer EUL trigger:

Create a PL/SQL function in the database and give it a function name.

For more information, see "Example for using a PL/SQL trigger to extract SQL from a worksheet when it is saved"
Start Discoverer Administrator and select Tools | Register PL/SQL Functions to display the "PL/SQL Functions dialog: Functions tab".

You use this dialog to import a PL/SQL function and register it with Discoverer.

For more information, see "About registering custom PL/SQL functions".

B.4 Example for using a PL/SQL trigger to extract SQL from a worksheet when it is saved

To enable automatic extraction of SQL Discoverer has enabled a trigger that gets fired whenever a workbook is saved. This trigger looks for a PL/SQL function (eul_trigger$post_save_document). If the function is not found, then nothing happens. If the PL/SQL function is found then the SQL is saved according to the function. Below is an example function that can be used, but this functionality has been left open to enable customers to fit their own specific needs.

To use a PL/SQL trigger to extract SQL from a worksheet when it is saved:

Invoke SQL*Plus.

Enter the following SQL statement to create a table to store the SQL:

Note: This creates a table my_sql that gets populated by the PL/SQL function.

SQL> create table my_sql (            workbookowner              varchar2 (100),
            workbook                   varchar2 (100), 
            worksheet                  varchar2 (100),
            seg_sequence               number (10),
            sql_segment                varchar2 (2000) );

Enter the following SQL statement to create a PL/SQL function called POST_SAVE_DOCUMENT:

SQL> create or replace function POST_SAVE_DOCUMENT
          (P_WORKBOOKOWNER in varchar,
           P_WORKBOOK        in  varchar,
           P_WORKSHEET       in  varchar,
           P_SEQUENCE      in number,
           P_SQL_SEGMENT   in varchar,
)
return number as
begin
delete from my_sql;
insert into my_sql
                       (       WORKBOOKOWNER,
                               WORKBOOK
                               WORKSHEET
                               SEG_SEQUENCE
                               SQL_SEGMENT
                      
VALUES (          P_WORKBOOKOWNER,
                               P_WORKBOOK,
                               P_WORKSHEET,
                               P_SEQUENCE,
                               P_SQL_SEGMENT
                 );
return 1;
end;

Grant the following privileges to the PUBLIC user to allow access to the table my_sql and the PL/SQL function POST_SAVE_DOCUMENT, by entering the following SQL statements in SQL*Plus:

SQL> grant execute on POST_SAVE_DOCUMENT to PUBLIC;

This SQL statement enables the PUBLIC user to execute the PL/SQL function POST_SAVE_DOCUMENT.

SQL> grant select, insert, update on my_sql to PUBLIC;

This SQL statement enables the PUBLIC user to select, insert and update the database table my_sql.
Start Discoverer Administrator and select Tools | Register PL/SQL Functions to display the "PL/SQL Functions dialog: Functions tab".

You use this dialog to register the new PL/SQL function with Discoverer.

Note: You must make sure that the name in the Display Name field of the new PL/SQL function is set to eul_trigger$post_save_document.
Register the new PL/SQL function.

For more information about how to import a PL/SQL function, see "How to register custom PL/SQL functions automatically".