Skip Headers
Oracle® Business Intelligence Discoverer Administration Guide
10g Release 2 (10.1.2.1)
B13916-04
  Go To Documentation Library
Library
Go To Product List
Product
Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

B Oracle Business Intelligence 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?

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

Notes:

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 flefield..
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

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

You might want to register a PL/SQL function that carries out a particular function (e.g. 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 exactly the same name as the Discoverer EUL trigger. If it is not, the Discoverer EUL trigger will not 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 (e.g. eul_trigger$post_save_document).

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

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

    For more information, see "Example of how to use a PL/SQL trigger to extract SQL from a worksheet when it is saved."

  2. 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".

Example of how to use 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 isn't 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:

  1. Invoke SQL*Plus.

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

  3. 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;

  4. 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.

  5. 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.

  6. 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".