The topics in this section discuss the use of PL/SQL in Oracle Reports Builder.
The PL/SQL Editor enables you to create and edit PL/SQL program units.
When you make changes to a program unit, dependent program units lose their compiled status, which is indicated by an asterisk (*) after their name under the Program Units node in the Object Navigator. You can navigate to those program units directly in the PL/SQL Editor using the Name list to recompile them.
If you delete a PL/SQL package, function, or procedure, you must also delete all references to it in your report. Otherwise, you will get an error when you compile, generate, or run the report.
PL/SQL package, function, and procedure names must be unique within the report and may not duplicate the names of any columns, groups, queries, or printable objects.
The Stored PL/SQL Editor enables you to create and edit stored PL/SQL program units in a database (listed under the Database Objects node in the Object Navigator).
The Syntax Palette is a programming tool that enables you to display and copy the constructs of PL/SQL language elements and built-in packages into the PL/SQL Editor and Stored PL/SQL Editor.
Program units are packages, functions, or procedures that you can reference from any PL/SQL within the current report.
Note:
Program units cannot be referenced from other documents. If you want to create a package, function, or procedure that can be referenced from multiple documents, create an external PL/SQL library (see Section 4.13.5.1, "Creating an external PL/SQL library").For a detailed example of using PL/SQL in a report, see Chapter 40, "Building a Report that Includes PL/SQL".
If you delete a PL/SQL package, function, or procedure, you must also delete all references to it in your report. Otherwise, you will get an error when you compile, generate, or run the report.
PL/SQL package, function, and procedure names must be unique within the report and may not duplicate the names of any columns, groups, queries, or printable objects.
Example: Referencing a PL/SQL function in formulas
Suppose that you have a report with the following groups and columns:
Groups Columns Summary
-----------------------------------------
RGN REGION
RGNSUMSAL SUM(DEPTSUMSAL)
COSTOFLIVING
DEPT DNAME
DEPTNO
DEPTSUMSAL SUM(EMP.SAL)
JOB JOB
HEADCOUNT COUNT(EMP.EMPNO)
EMP ENAME
EMPNO
SAL
COMM
Given these groups and columns, you might create multiple formulas that apply the cost of living factor (COSTOFLIVING) to salaries. To avoid duplication of effort, you could create the following PL/SQL function and reference it from the formulas:
function CompSal(salary number) return number is begin return (salary*CostofLiving); end;
Following are some examples of how you might reference the PL/SQL function in formulas:
CompSal(:RGNSUMSAL) or CompSal(:SAL) + COMM
Stored program units (also known as stored subprograms, or stored procedures) can be compiled separately and stored permanently in an Oracle database, ready to be executed. Once compiled and stored in the data dictionary, they are schema objects, which can be referenced by any number of applications connected to that database.
Stored program units offer higher productivity, better performance, memory savings, application integrity, and tighter security. For example, by designing applications around a library of stored procedures and functions, you can avoid redundant coding and increase your productivity.
Stored program units are stored in parsed, compiled form. So, when called, they are loaded and passed to the PL/SQL engine immediately. Also, they take advantage of shared memory. So, only one copy of a program unit need be loaded into memory for execution by multiple users.
Because stored program units run in ORACLE, they can perform database operations more quickly than PL/SQL that is local to your report. Therefore, in general, use stored program units for PL/SQL that performs database operations. Use local program units for PL/SQL that does not involve database operations. However, if you are on a heavily loaded network with very slow response time, using stored program units may not be faster for database operations. Similarly, if your server is significantly faster than your local machine, then using local program units may not be faster for non-database operations.
External PL/SQL libraries are collections of PL/SQL procedures, functions, and packages that are independent of a report definition. By attaching an external library to a report, you can reference its contents any number of times. For example, you could reference a procedure in an attached library from both a Before Report trigger and a format trigger. This eliminates the need to re-enter the same PL/SQL for each application.
When you associate an external PL/SQL library with a report or another external library, it is called an attached library.
Attached libraries are external PL/SQL libraries that you have associated with a report or another external library. When an external library is attached, you can reference its packages, functions, and procedures from within your report. For example, if you attached an external library name MYLIB to your report and it contained a function named ADDXY, then you could reference ADDXY from any PL/SQL in the report.
External PL/SQL libraries are independent of a report definition.
Local PL/SQL executes more quickly than a reference to a procedure or function in an external PL/SQL library. As a result, you should only use external PL/SQL libraries when the benefits of sharing the code across many applications outweigh the performance overhead.
If Oracle Reports Builder cannot find a library that you specify in the Attached Libraries list, a warning will be raised when you accept the dialog box, save the report, or open the report. If you try to run the report or compile the PL/SQL in it, an error will be raised.
The Attached Libraries list is saved. The next time you open the report or library the list will have the same contents it did when you last saved the report.
If an external library references another library, you must attach both libraries to the report even if the first library already has the second one attached.
Formulas are PL/SQL functions that populate formula or placeholder columns. You can access the PL/SQL for formulas from the Object Navigator, the PL/SQL Editor, or the Property Inspector (that is, the PL/SQL Formula property).
A column with Datatype property set to Number can only have a formula that returns a value of datatype NUMBER. A column with Datatype property set to Date can only have a formula that returns a value of datatype DATE. A column with Datatype property set to Character can only have a formula that returns a value of datatype CHARACTER, VARCHAR, or VARCHAR2.
You can read and assign values to a column in a formula, if the column is a placeholder or parameter column; you cannot change the value of database columns (values retrieved from the database). For example, you can use the value of a column called COMP in a condition (for example, IF :COMP = 10) and you can directly set its value in an assignment statement (for example,:COMP:= 15).
A formula can only make reference to columns that are in the same or a higher group in the group hierarchy. For example, a formula for a report-level column can only reference other report-level columns.
Formulas are calculated such that any column referenced in the formula will be calculated first. To do so, Oracle Reports Builder builds a dependency list, to guarantee proper ordering of calculations. Note that circular dependencies, in which a column references another column which in turn references the first column, either directly or indirectly, are not allowed.
When using SRW.DO_SQL, we recommend that you do not read database values that are updated or inserted in the same report. There is no guarantee of the exact time Oracle Reports Builder will fetch records from the database for formatting the output. Oracle Reports Builder does internal "data look-ahead" to optimize performance. Thus, a particular record might already have been accessed before an update is issued to the same record. Oracle Reports Builder builds internal dependency lists which guarantee that events, such as invocation of user exits, calculation of summaries, and so on, happen in the correct order. However, Oracle Reports Builder cannot guarantee these events will be synchronized with its internal data access or with the formatting of data.
The following example populates the column with the value of the salary plus the commission.
function salcomm return NUMBER is begin return(:sal + :comm); end;
The following code adds the commission to the salary if the value for the commission is not null.
function calcomm return NUMBER is
temp number;
begin
if :comm IS NOT NULL then
temp := :sal + :comm;
else
temp := :sal;
end if;
return (temp);
end;
Section 2.3.2, "About formula columns"
A group filter determines which records to include in a group. You can use the packaged filters, First and Last, to display the first n or last n records for the group, or you can create your own filters using PL/SQL. You can access group filters from the Object Navigator, the Property Inspector (the PL/SQL Filter property), or the PL/SQL Editor.
The function must return a boolean value (TRUE or FALSE). Depending on whether the function returns TRUE or FALSE, the current record is included or excluded from the report.
Difference between group filters and Maximum Rows to Fetch property
The Maximum Rows to Fetch property restricts the actual number of records fetched by the query. A group filter determines which records to include or exclude, after all the records have been fetched by the query. Since Maximum Rows to Fetch actually restricts the amount of data retrieved, it is faster than a group filter in most cases. If you set the Filter Type property to Last or Conditional, Oracle Reports Builder must retrieve all of the records in the group before applying the filter. Also, you should be aware that when using Maximum Rows to Fetch for queries, it can affect summaries in other groups that depend on this query. For example, if you set the Maximum Rows to Fetch property to 8, any summaries based on that query will only use the 8 records retrieved.
Group filters cannot be added to groups if the Filter Type property is set to First or Last.
Group filters cannot be added to cross-product groups.
The function that you enter for a group filter can only depend upon the following columns:
a database column owned by the group's query or a query above it in the data model hierarchy
computed columns (formulas or summaries) that depend on unrelated queries (that is, computed columns that do not depend upon columns in the group, the group's ancestors, or the group's descendants)
In a group filter, you can read the values of Oracle Reports Builder columns and parameters of the correct frequency, but you cannot directly set their values. For example, you can use the value of a parameter called COUNT1 in a condition (for example, IF :COUNT1 = 10), but you cannot directly set its value in an assignment statement (for example, :COUNT1:= 10). Note also that the use of PL/SQL global variables to indirectly set the values of columns or parameters is not supported. If you do this, you may get unpredictable results. You also cannot reference any page-dependent columns (that is, Reset At of Page) or columns that rely on page-dependent columns in a group filter.
function filter_comm return boolean is
begin
if :comm IS NOT NULL then
if :comm < 100 then
return (FALSE);
else
return (TRUE);
end if;
else
return (FALSE); -- for rows with NULL commissions
end if;
end;
A REF CURSOR query uses PL/SQL to fetch data. Each REF CURSOR query is associated with a PL/SQL function that returns a cursor value from a cursor variable. The function must ensure that the REF CURSOR is opened and associated with a SELECT statement that has a SELECT list that matches the type of the REF CURSOR.
Note:
The use ofREF CURSOR queries in Oracle Reports 11g Release 2 (11.1.2) requires that your database version is 10.1.0.5 (for 10.1) or 10.2.0.2 (for 10.2) or higher.
Interoperability between a 11g Release 2 (11.1.2) database or Oracle Forms Services client and a 10.1/10.2 database requires a minimum patchset level of 10.1.0.5 (for 10.1) or 10.2.0.2 (for 10.2).
An attempt to reference a 10.1 or 10.2 PL/SQL unit or view under the following circumstances will fail with a PLS-801[55916] error unless the 10.1 or 10.2 environment has been patched to the right level:
A PL/SQL unit, anonymous block, trigger, call statement, or SQL statement on a 11g Release 2 (11.1.2) database invokes a PL/SQL unit on a 10.1 or 10.2 database across a database link.
A PL/SQL unit, anonymous block, trigger, or call statement on a 11g Release 2 (11.1.2) database references a view on a 10.1 or 10.2 database across a database link and the view directly or indirectly references a PL/SQL function or an object type.
An Oracle Forms Services 11g Release 2 (11.1.2) client invokes a PL/SQL unit in a 10.1 or 10.2 database using RPC.
Oracle Reports supports both static and dynamic REF CURSORs. For example:
Static REF CURSOR:
open l_rc for SELECT * FROM emp WHERE ename = 'KING';
Dynamic REF CURSOR:
l_query := SELECT empno, ename, sal, hiredate FROM emp WHERE 1-1'; open l_rc for l_query;
Note:
TheSELECT statement must be explicitly set for dynamic REF CURSORs.For a detailed example, see Chapter 41, "Building a Paper Report with REF CURSORs".
Oracle Reports supports only strongly typed REF CURSORs. For example:
type c1 is REF CURSOR RETURN emp%ROWTYPE;
When you make a REF CURSOR query the child in a data link, the link can only be a group to group link. It cannot be a column to column link.
If you use a stored program unit to implement REF CURSORs, you receive the added benefits that go along with storing your program units in the Oracle database.
You base a query on a REF CURSOR when you want to:
more easily administer SQL.
avoid the use of lexical parameters in your reports.
share data sources with other applications, such as Form Builder.
increase control and security.
encapsulate logic within a subprogram.
Furthermore, if you use a stored program unit to implement REF CURSORs, you receive the added benefits that go along with storing your program units in the Oracle database.
For more information about REF CURSORs and stored subprograms, refer to the PL/SQL User's Guide and Reference.
Example 1: Package with REF CURSOR example
/* This package spec defines a REF CURSOR ** type that could be referenced from a ** REF CURSOR query function. ** If creating this spec as a stored ** procedure in a tool such as SQL*Plus, ** you would need to use the CREATE ** PACKAGE command. */ PACKAGE cv IS type comp_rec is RECORD (deptno number, ename varchar(10), compensation number); type comp_cv is REF CURSOR return comp_rec; END;
Example 2: Package with REF CURSOR and function
/* This package spec and body define a ref
** cursor type as well as a function that
** uses the REF CURSOR to return data.
** The function could be referenced from
** the REF CURSOR query, which would
** greatly simplify the PL/SQL in the
** query itself. If creating this spec
** and body as a stored procedure in a
** tool such as SQL*Plus, you would need
** to use the CREATE PACKAGE and CREATE
** PACKAGE BODY commands.
*/
PACKAGE cv IS
type comp_rec is RECORD
(deptno number,
ename varchar(10),
compensation number);
type comp_cv is REF CURSOR return comp_rec;
function emprefc(deptno1 number) return comp_cv;
END;
PACKAGE BODY cv IS
function emprefc(deptno1 number) return comp_cv is
temp_cv cv.comp_cv;
begin
if deptno1 > 20 then
open temp_cv for select deptno, ename,
1.25*(sal+nvl(comm,0)) compensation
from emp where deptno = deptno1;
else
open temp_cv for select deptno, ename,
1.15*(sal+nvl(comm,0)) compensation
from emp where deptno = deptno1;
end if;
return temp_cv;
end;
END;
/* This REF CURSOR query function would be coded
** in the query itself. It uses the cv.comp_cv
** REF CURSOR from the cv package to return
** data for the query.
*/
function DS_3RefCurDS return cv.comp_cv is
temp_cv cv.comp_cv;
begin
if :deptno > 20 then
open temp_cv for select deptno, ename,
1.25*(sal+nvl(comm,0)) compensation
from emp where deptno = :deptno;
else
open temp_cv for select deptno, ename,
1.15*(sal+nvl(comm,0)) compensation
from emp where deptno = :deptno;
end if;
return temp_cv;
end;
Example 4: REF CURSOR query calling function
/* This REF CURSOR query function would be coded ** in the query itself. It uses the cv.comp_cv ** REF CURSOR and the cv.emprefc function from ** the cv package to return data for the query. ** Because it uses the function from the cv ** package, the logic for the query resides ** mainly within the package. Query ** administration/maintenance can be ** done at the package level (for example, ** modifying SELECT clauses could be done ** by updating the package). You could also ** easily move the package to the database. ** Note this example assumes you have defined ** a user parameter named deptno. */ function DS_3RefCurDS return cv.comp_cv is temp_cv cv.comp_cv; begin temp_cv := cv.emprefc(:deptno); return temp_cv; end;
If you want to use Data Manipulation Language (DML) or Data Definition Language (DDL) in your PL/SQL, you can use the SRW.DO_SQL built-in procedure. Note that SRW.DO_SQL should only be used for DML and DDL; you should not use it to fetch data. For more information on DML and DDL, see the Oracle Server SQL Language Reference manual.
Because of Oracle Reports' processing model, it is recommended that you only use DDL in the Before Parameter Form and After Parameter Form triggers. DML can be entered any place that accepts PL/SQL.
Any DML or DDL that will reported on with this report's processing should be done in (or before) the After Parameter Form trigger. Consistency cannot be guaranteed in the Before Report trigger, since Oracle Reports may have to start some work on data cursors before that trigger based on the definition of the report. One thing Oracle Reports Builder always does before the Before Report trigger is to describe the tables involved and open cursors. Any change to the tables after that will not be seen by the report.
A built-in package is a group of logically related PL/SQL types, objects, and functions or procedures. It generally consists of two parts: the package spec (including data declarations) and the package body. Packages are especially useful because they allow you to create global variables.
Oracle provides several packaged procedures that you can use when building or debugging your PL/SQL-based applications. Your PL/SQL code can make use of the procedures, functions, and exceptions in the Oracle Reports Builder built-in package (SRW), and numerous Tools built-in packages, as described below.
Oracle Reports Builder is shipped with a built-in package (SRW), a collection of PL/SQL constructs that include many functions, procedures, and exceptions you can reference in any of your libraries or reports.
The PL/SQL provided by the SRW package enables you to perform such actions as change the formatting of fields, run reports from within other reports, create customized messages to display in the event of report error, and execute SQL statements.
You can reference the contents of the SRW package from any of your libraries or reports without having to attach it. However, you cannot reference its contents from within another product, for example, from SQL*Plus.
Constructs found in a package are commonly referred to as "packaged" (that is, packaged functions, packaged procedures, and packaged exceptions).
Topic "SRW built-in package" in the Reference section of the Oracle Reports online Help
Several client-side built-in packages are provided that contain many PL/SQL constructs you can reference while building applications or debugging your application code. These built-in packages are not installed as extensions to the package STANDARD. As a result, any time you reference a construct in one of the packages, you must prefix it with the package name (for example, TEXT_IO.PUT_LINE).
The Tools built-in packages are:
Provides Dynamic Data Exchange support within Oracle Reports Builder components.
Provides procedures, functions, and exceptions for when debugging your PL/SQL program units. Use these built-in subprograms to create debug triggers and set breakpoints with triggers.
Provides procedures and functions for executing dynamic SQL within PL/SQL code written for Oracle Reports Builder applications.
Provides procedures, functions, and exceptions you can use to create and maintain lists of character strings (VARCHAR2). This provides a means of creating arrays in PL/SQL Version 1.
Provides a foreign function interface for invoking C functions in a dynamic library.
Provides an interface for invoking Java classes from PL/SQL.
Enables you to extract high-level information about your current language environment. This information can be used to inspect attributes of the language, enabling you to customize your applications to use local date and number format. Information about character set collation and the character set in general can also be obtained. Facilities are also provided for retrieving the name of the current language and character set, allowing you to create applications that test for and take advantage of special cases.
Provides procedures, functions, and exceptions you can use for tuning your PL/SQL program units (for example, examining how much time a specific piece of code takes to run).
Provides constructs that allow you to read and write information from and to files. There are several procedures and functions available in Text_IO, falling into the following categories:
file operations. The FILE_TYPE record, the FOPEN and IS_OPEN functions, and the FCLOSE procedure enable you to define FILE_TYPE variables, open files, check for open files, and close open files, respectively.
output (write) operations. The PUT, PUTF, PUT_LINE, and NEW_LINE procedures enable you to write information to an open file or output it to the PL/SQL Interpreter.
input (read) operations. The GET_LINE procedure enables you to read a line from an open file
Enables you to interact with Oracle environment variables by retrieving their values for use in subprograms.
Enables you to access and manipulate the error stack created by other built-in packages such as DEBUG.
In addition to using exceptions to signal errors, some built-in packages (for example, the DEBUG package) provide additional error information. This information is maintained in the form of an "error stack".
The error stack contains detailed error codes and associated error messages. Errors on the stack are indexed from zero (oldest) to n-1 (newest), where n is the number of errors currently on the stack. Using the services provided by the TOOL_ERR package, you can access and manipulate the error stack.
Provides a means of extracting string resources from a resource file with the goal of making PL/SQL code more portable by isolating all textual data in the resource file.
The following packages are used only internally by Oracle Reports. There are no subprograms available for external use with these packages.
Contains constructs used by Reports for private PL/SQL services.
Calls subprograms stored in the database. Calls to this package are automatically generated Note: STPROC is obsoleted in Oracle Reports 11g Release 2 (11.1.2).
Facilitates calling Java from PL/SQL.
Topics for each of the Tools built-in packages under in the Reference > PL/SQL Reference > Built-in Packages section of the Oracle Reports online Help.
Triggers check for an event. When the event occurs they run the PL/SQL code associated with the trigger.
Report triggers are activated in response to report events such as the report opening and closing rather that the data that is contained in the report. They are activated in a predefined order for all reports.
Format triggers are executed before an object is formatted. A format trigger can be used to dynamically change the formatting attributes of the object.
Validation triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form.
Database triggers are procedures that are stored in the database and implicitly executed when a triggering statement such as INSERT, UPDATE, or DELETE is issued against the associated table.
Report triggers execute PL/SQL functions at specific times during the execution and formatting of your report. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as customize the formatting of your report, perform initialization tasks, and access the database. To create or modify a report trigger, you use the Report Triggers node in the Object Navigator. Report triggers must explicitly return TRUE or FALSE.
Oracle Reports has five global report triggers. You cannot create new global report triggers. The trigger names indicate at what point the trigger fires:
Before Report trigger: Fires before the report is executed but after queries are parsed.
After Report trigger: Fires after you exit the Paper Design view, or after report output is sent to a specified destination, such as a file, a printer, or an e-mail ID. This trigger can be used to clean up any initial processing that was done, such as deleting tables. Note, however, that this trigger always fires, whether or not your report completed successfully.
Between Pages trigger: Fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. In the Paper Design view, this trigger only fires the first time that you go to a page. If you subsequently return to the page, the trigger does not fire again.
Before Parameter Form trigger: Fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters.
After Parameter Form trigger: Fires after the Runtime Parameter Form is displayed. From this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the Runtime Parameter Form. Columns from the data model are not accessible from this trigger. If the Runtime Parameter Form is suppressed, the After Parameter Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data.
Order of report trigger execution
The order of events when a report is executed is as follows:
Before Parameter Form trigger is fired.
Note:
If the Parameter Form is used on the Web, the Before Parameter Form trigger fires twice: once when the Parameter Form is displayed, and a second time when the parameters are submitted. This is because Oracle Reports executes in a stateless fashion. There is no session to return to, so the Before Parameter Form trigger has to fire the second time to ensure that the parameters selected on the Parameter Form and passed on the command line are valid.Runtime Parameter Form appears (if not suppressed).
After Parameter Form trigger is fired (unless the user cancels from the Runtime Parameter Form).
Report is "compiled".
Queries are parsed.
Before Report trigger is fired.
SET TRANSACTION READONLY is executed (if specified with the READONLY command line keyword or setting).
The report is executed and the Between Pages trigger fires for each page except the first one. (Note that data can be fetched at any time while the report is being formatted.) COMMITs can occur during this time due to: SRW.DO_SQL with DDL, or if ONFAILURE=COMMIT, and the report fails.
COMMIT is executed (if READONLY is specified) to end the transaction.
After Report trigger is fired.
COMMIT/ROLLBACK/NOACTION is executed based on what was specified with the ONSUCCESS command line keyword or setting.
In steps 4 through 9, avoid DDL statements that would modify the tables on which the report is based. Step 3 takes a snapshot of the tables and the snapshot must remain valid throughout the execution of the report. In steps 7 through 9, avoid DML statements that would modify the contents of the tables on which the report is based. Queries may be executed in any order, which makes DML statements unreliable (unless performed on tables not used by the report).
If you specify READONLY on the command line, you should avoid DDL altogether. When you execute a DDL statement (for example, with SRW.DO_SQL), a COMMIT is automatically issued. If you are using READONLY, this will prematurely end the transaction begun by SET TRANSACTION READONLY
As a general rule, any processing that will affect the data retrieved by the report should be performed in the Before Parameter Form or After Parameter Form triggers. (These are the two report triggers that fire before anything is parsed or fetched.) Any processing that will not affect the data retrieved by the report can be performed in the other triggers.
Consistency is guaranteed if you use DML or DDL in (or before) the After Form Trigger. However, consistency is not guaranteed in the Before Report trigger, since Oracle Reports may have to start work on data cursors before that trigger based on the definition of the report. Before the Before Report trigger, Oracle Reports describes the tables involved and opens cursors. Any change to the tables after that will not be seen by the report.
If you are sending your report output to the Paper Design view or Previewer, you should note that some or all of the report triggers may be fired before you see the report output. For example, suppose that you use SRW.MESSAGE to issue a message in the Between Pages trigger when a condition is met. If there are forward references in the report (for example, a total number of pages displayed before the last page), Oracle Reports may have to format ahead to compute the forward references. Hence, even though you have not yet seen a page, it may already have been formatted and the trigger fired.
In report triggers, you can use the values of report-level columns and parameters. For example, you might need to use the value of a parameter called COUNT1 in a condition (for example, IF :COUNT1 = 10). Note, though, that you cannot reference any page-dependent columns (that is, a column with a Reset At property set to Page) or columns that rely on page-dependent columns.
In the Before and After Parameter Form, and Before and After Report triggers, you can set the values of parameters (for example, give them a value in an assignment statement, :COUNT1 = 15). In the Before and After Report triggers, you can also set the values of report-level, placeholder columns.
In the Between Pages trigger, you cannot set the values of any data model objects. Note also that the use of PL/SQL global variables to indirectly set the values of columns or parameters is not recommended. If you do this, you may get unpredictable results.
A lexical reference cannot be used to create additional bind variables after the After Parameter Form trigger fires. For example, suppose you have a query like the following (note that the WHERE clause is replaced by a lexical reference):
SELECT ENAME, SAL FROM EMP
&WHERE_CLAUSE
If the value of the WHERE_CLAUSE parameter contains a reference to a bind variable, you must specify the value in the After Parameter Form trigger or earlier. You would get an error if you supplied the following value for the parameter in the Before Report trigger. If you supplied this same value in the After Parameter Form trigger, the report would run.
WHERE SAL = :new_bind
A format trigger is a PL/SQL function executed before an object is formatted. A trigger can be used to dynamically change the formatting attributes of the object. For example, you can use a format trigger to cause a value to display in bold if it is less than zero. Another example is to use a format trigger to use scientific notation for a field if its value is greater than 1,000,000.
A format trigger can fire multiple times for a given object, whenever Oracle Reports Builder attempts to format the object. Consider the case where Oracle Reports Builder starts to format the object at the bottom of a page. If the object does not fit on the page, Oracle Reports Builder stops formatting and reformats on the following page. In this case, the format trigger will fire twice. It is therefore not advisable to do any kind of "persistence" operation, such as logging, in this trigger.
The Oracle Reports Builder SRW built-in package contains PL/SQL procedures with which you can quickly change the format attributes of an object. These include procedures to:
change the border pattern and color of an object
change the interior pattern and color of an object
change the font size, style, weight, spacing, and justification of a field or boilerplate text
change the format mask of a field
access a field's value
See the topic "Format trigger" in the Reference section of the Oracle Reports online Help.
Validation triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form.
Note:
For JSP-based Web reports, the Runtime Parameter Form displays when you run a report in Oracle Reports Builder, but does not display in the runtime environment. If parameters are not specified on the Runtime Parameter Form, the validation trigger returns false and generates error messagerep-546 Invalid Parameter Input error. Thus, you need to provide the parameters in an alternate way, as described in Section 1.9.4, "About Parameter Forms for Web reports".Validation triggers are also used to validate the Initial Value property of the parameter. Depending on whether the function returns TRUE or FALSE, the user is returned to the Runtime Parameter Form.
See the topic "Validation trigger" in the Reference section of the Oracle Reports online Help.
Database triggers are procedures that are stored in the database and implicitly executed when a triggering statement such as INSERT, UPDATE, or DELETE is issued against the associated table. Triggers can be defined only on tables, not on views. However, triggers on the base table of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against a view.
A trigger can include SQL and PL/SQL statements that execute as a unit, and can invoke other stored procedures. Use triggers only when necessary. Excessive use of triggers can result in cascading or recursive triggers. For example, when a trigger is fired, a SQL statement in the trigger body potentially can fire other triggers.
By using database triggers, you can enforce complex business rules and ensure that all applications behave in a uniform manner. Use the following guidelines when creating triggers:
Use triggers to guarantee that when a specific operation is performed, related actions are performed.
Use database triggers only for centralized, global operations that should be fired for the triggering statement, regardless of which user or database application issues the statement.
Do not define triggers that duplicate the functionality already built into Oracle. For example, do not define triggers to enforce data integrity rules that can be easily enforced using declarative integrity constraints.
Limit the size of triggers (60 lines or fewer is a good guideline). If the logic for your trigger requires much more than 60 lines of PL/SQL code, it is better to include most of the code in a stored procedure, and call the procedure from the trigger.
Be careful not to create recursive triggers. For example, creating an AFTER UPDATE statement trigger on the EMP table that itself issues an UPDATE statement on EMP causes the trigger to fire recursively until it has run out of memory.
For additional information about how triggers are used in applications, see the Oracle Application Developer's Guide. See the Oracle Concepts Manual for more information about the different types of triggers.