Chapter 28

Advanced Topics in Report Design

Coding PL/SQL Triggers in Reports *

Different Types of Triggers and Their Usage *

Action Triggers and Their Usage *

Report Triggers and Their Usage *

Debug Triggers and Their Usage *

Format Triggers and Their Usage *

Exercises *

Writing and Referencing Common Code *

Exercises *

Creating and Referencing a PL/SQL Library *

Placing Program Units in the Library *

Attaching the Library to Use the Program Units *

Exercises *

Using Report Builder Built-In Packages *

Report Builder SRW Built-In Package Contents *

Executing Reports from PL/SQL with SRW *

Using SRW for Processing SQL *

Tracing Report Activity with SRW *

SRW for Report Formatting *

Using SRW to Create Temporary Tables *

Web-Enabling Reports with SRW *

Exercises *

Using SRW Package Procedures and Functions *

Using Procedures to Execute Reports *

Exercises *

Output Messages at Run Time *

Available Exceptions in SRW *

Exercises *

Using Temporary Tables in Reports *

Exercises *

Modifying Visual Attributes at Run Time *

Using the SRW.SET_ATTR( ) Procedure *

Using the Set of SRW.SET_ATTR_DESC( ) Procedures *

Exercises *

Maximizing Performance Using Reports Server *

Comparing Local Client and Report Server Reporting *

Exercises *

The Reports Server Architecture *

Exercises *

Viewing and Scheduling Reports in Queue Manager *

Scheduling Reports with Oracle Reporting ActiveX Control *

Viewing Reports in Queue Manager *

Exercises *

Invoking Oracle Reporting ActiveX Control from Other Apps *

Exercises *

Building Reports for Different Environments *

Building Reports for Different Environments *

Exercises *

Considerations When Building Reports for Different GUIs *

Exercise *

Character Mode Reports and Their Settings *

Exercises *

Facilities for Building Reports in Other Languages *

Exercises *

Chapter Summary *

Two-Minute Drill *

In this chapter, you will cover the following advanced topics in report design:

This is the final chapter on developing reports for OCP Exam 5 in the Developer track. The chapter covers advanced topics of report design. Some of these topics include PL/SQL trigger development and the use of built-in PL/SQL packages available in Report Builder. In your report development efforts, you will encounter situations where triggers are useful, or in fact required. You have seen some of these situations already, with validation triggers for system parameters and report triggers for placeholder column value population. You will find uses for the Report Builder built-in packages as well. Another important topic covered in this final chapter is the use of Reports Server for enterprise reporting that is efficient and fast. Part of an enterprise reporting system is your recognition of the requirements for building reports for deployment in different environments and in different languages. All in all, the contents of this chapter comprise 18 percent of OCP Exam 5 content.

Coding PL/SQL Triggers in Reports

In this chapter, you will cover the following points on coding PL/SQL triggers in reports:

PL/SQL is the programming backbone of the Oracle database and Developer/2000 suite. You can incorporate PL/SQL into many places and aspects of your reports. This section talks about some of those places and uses. In this section, you will cover the different types of triggers and their usage in the report. You will cover the development and reference of common code throughout a report. Finally, you will cover the creation and reference of PL/SQL libraries in your report. Your knowledge of OCP exam content will be rounded out by coverage provided in these crucial areas. It is important to understand these topics if you want to become certified on the Oracle Developer/2000 toolset.

Different Types of Triggers and Their Usage

You have already seen several different types of triggers and their usage in Report Builder. This lesson recaps those triggers to complete your understanding of them. There are four different types of triggers in Report Builder, and they are action triggers, report triggers, debug triggers, and format triggers. The rest of the section covers each of these trigger types and their usage in the report. Some of this may be a review. You should take the time to understand the material to ensure your success on the OCP exam.

Action Triggers and Their Usage

Action triggers are used in the report for the purpose of handling a set of operations associated with an event in the report. The triggering event for action triggers is the pressing of a button in the Runtime Previewer. Sometimes, in the definition of complex reports, you will want to include buttons in the output of one report that basically generate output from another report in a separate report definition (.rdf) file. Such reports can be thought of as "master/detail/detail" reports, where the report containing the buttons that fire other reports are the master/detail reports and those reports that are fired when you press the button are the second layer of detail. The PL/SQL code contained in the action trigger needn’t only fire a report, either—it can execute any PL/SQL operation permitted at that point in your report execution.

Because of the nature of buttons in Report Builder, you cannot test the functionality of an action trigger from within the Live Previewer view of your report output—you must view the report in the Runtime Previewer so that Report Builder will both generate the button and allow you to press it. In addition, you cannot test the execution of an action trigger from within the PL/SQL interpreter module of Report Builder.

Action triggers are created in the following way. From within the Layout Model of your report, click on the Button button to activate that tool and draw the button object in the Layout Model using the general usage guidelines for Layout Model tools covered in the unit. When finished, double-click on your new button object in the Layout Model and define the Type property to be PL/SQL Trigger from the drop-down box that appears when you click on the property. Then, click on the PL/SQL Trigger property in the Property Palette and then click on the Definition button that appears next to that property. The PL/SQL Editor module then appears, where you can define the PL/SQL that will execute when the button is pressed. In this case, your action trigger fires the execution of another report with the use of a procedure called run_report( ) found in the SRW built-in package. More about this package appears in the next section, while the actual PL/SQL code that might appear in such an action trigger appears in the following code block. Note that this code is taken from the drildone.rdf report that comes with the Developer/2000 2.0 software release. This report is found in the report30/demo/bitmap subdirectory under the Oracle software home directory containing your Developer/2000 software.

procedure U_1ButtonAction is
begin
srw.run_report('saledone.rdf paramform=yes
P_custname="'||:custname||'" P_salesrep="'||:salesrep||'"');
end;

Report Triggers and Their Usage

Report triggers are a cornerstone of your report. Report Builder makes it possible for you to define PL/SQL for these built-in triggers that will fire at various points during the execution of your report. There are five different categories of report triggers. They are BEFORE REPORT, AFTER REPORT, BETWEEN PAGES, BEFORE PARAMETER FORM, and AFTER PARAMETER FORM. These names give an accurate indication of when the triggers fire. Your involvement with these triggers is simply to define the PL/SQL code that will run at each of the trigger firing times.

Think for a moment about validation triggers, which were mentioned last chapter in relation to the coverage of the runtime Parameter Form. Recall that validation triggers fire twice in the course of report processing. When considered in light of the report triggers, it should now be obvious both at which time the validation triggers fire, and the very fact that validation triggers are, in effect, a subset of the report triggers available for definition in Report Builder. One of the report triggers that is a validation trigger is the BEFORE PARAMETER FORM trigger, which fires before the runtime Parameter Form is displayed, and can be used to validate or even define runtime parameters for the report. The other report trigger that is a validation trigger is the AFTER PARAMETER FORM trigger, which fires after the runtime Parameter Form is displayed, and can be used to validate and change values for the parameters defined in the runtime Parameter Form. These two triggers are used to execute any PL/SQL that you might want to use to affect the data shown in the report.

The other three report triggers—BEFORE REPORT, BETWEEN PAGES, and AFTER REPORT—fire third, fourth and fifth, respectively, at the intervals described by their names. The BEFORE REPORT trigger fires after the queries used in a report are parsed and data is fetched by the Oracle RDBMS but before the report actually processes. It is a good time to perform any initialization or setup, such as creation of temporary tables to store intermediate datasets for the report. The BETWEEN PAGES trigger fires after the first page is generated but before the second page is generated, and then between each subsequent page generated by the report. This trigger is good for special formatting you want to accomplish on the second and subsequent pages of your report. This trigger only fires once within the Runtime Previewer, but fires as defined when the report is run for real. Finally, the AFTER REPORT trigger is useful for exit handling such as removal of temporary tables you created in your database as part of the BEFORE REPORT trigger or data cleanup.

Debug Triggers and Their Usage

Recall from the discussion of Procedure Builder in Unit II that there was ample coverage of debug triggers and their usage. Debug triggers in Procedure Builder give you insight on the behavior and performance of your PL/SQL block with the use of runtime debugging. Report Builder offers the same functionality for your PL/SQL blocks in reports as well. For more information about defining and using debug triggers, refer to Unit II content on debugging PL/SQL program units using Procedure Builder.

Format Triggers and Their Usage

Finally, format triggers allow for dynamic report formatting by defining a test condition to see if the object defined will appear in the report. The format trigger returns either TRUE or FALSE, depending on the criteria you define in your PL/SQL trigger block. If the trigger returns TRUE, then the object is displayed in the report output. If the trigger returns FALSE, the object is not displayed in the report output—although any data retrieved from the database or calculated in a formula will still be retrieved or calculated even though it isn’t displayed. A format trigger is defined in the Layout Model view of your report through the use of the Format Trigger property in the Property Palette under the Advanced Layout node. Format triggers can either increase display prominence or eliminate from report output the object to which they associate. The method the format trigger may use for this task includes functions and procedures from the SRW package, which will be talked about in the next section.

TIP: There are several formatting and usage restrictions on format triggers, not the least of which is the inability to set values for report columns from within the PL/SQL of the format trigger. There are others, and you should consult the Report Builder online help documents to understand all of them.

Exercises

  1. Identify several different types of triggers available in Report Builder. Which triggers handle validation of Parameter Form values? Which triggers handle the execution of drill-down "detail" reports fired from a master report?
  2. What is the name of the package mentioned in this lesson that allows you to fire a detail report?

Writing and Referencing Common Code

It is important to know how to write and reference common PL/SQL code in Developer/2000. This understanding starts with knowing a few facts about PL/SQL code you define for any aspect of your application, such as on your report. For every trigger you write in your report to handle anything a trigger supports, or for any PL/SQL block you define that fires in support of an event like pressing a button, Report Builder tracks the code block and makes it available for modification in several ways. If you haven’t closed it already, look at the Object Navigator contents for the drildone.rdf file. If you did close it, reopen the report definition file using instructions provided in the previous lesson. Figure 28-1 displays the contents of the Object Navigator module for the drildone.rdf report.

Figure 1: Object Navigator contents for drildone.rdf

Open the Layout Model of this report by choosing the Tools | Report Editor menu command and then clicking on the Layout Model button in the interface. Find the button object in the report layout and double-click on it to see the Property Palette for that object. Click on the PL/SQL Trigger property in the Property Palette to make the interface button appear, and then click on the interface button to invoke the PL/SQL Editor module with the action trigger code displayed in the window. Don’t make any changes—just take a second to observe the contents, and that the name of the module is U_1ButtonAction( ). Now click the Close button for the PL/SQL Editor module and return to the Object Navigator.

Notice that there is a drill-down node in this module called Program Units that should have a small + sign in the box next to the name of the node. Expand the node by clicking on the + next to the node name, double-click on the node name, or click once on the node name and then click on the Expand button in the Tool Palette for the Object Navigator module. You should see an entry for a PL/SQL program unit of the same name as that action trigger you just looked at. Open the PL/SQL Editor for that program unit by right-clicking on the PL/SQL program unit name in the Object Navigator and choose the Program Unit Editor… option listed at the bottom of the menu box that appears. You will now be looking at the same program unit you just saw. When compiled, any PL/SQL block under this node in the Object Navigator will have further drill downs available for program unit specification and referenced and referring program units. Figure 28-2 displays the contents of the PL/SQL Editor module for this action trigger.

Figure 2: PL/SQL Editor module with U_1ButtonAction( ) displayed

Report Builder, then, keeps track of all PL/SQL program units and lists them under this node in the Object Navigator module for your report. This trigger is available within the report for you to use. However, it is not available for use outside the current report. To use procedures throughout other reports, the PL/SQL program unit must either be stored in the database or in an external PL/SQL library that can then be attached to the report for use. The next lesson covers how to create external PL/SQL libraries in Report Builder.

Creation of common code is handled in Report Builder in the following way. Say, for example, that you want to define a procedure that displays a simple message, "I am a report." To do this, click on the Program Units node in the Object Navigator once to highlight and then click on the Create button. The familiar New Program Unit dialog box appears, where you must define a name and the PL/SQL block type for your new code. Type hello in the text box for the name of the procedure and then choose the appropriate radio button, then click OK. Report Builder then launches you into the PL/SQL Editor module, as shown in Figure 28-3. You can then define a simple PL/SQL block such as the one shown in the following code block:

procedure hello is
begin
text_io.put_line('I am a report');
end;

Figure 3: PL/SQL Editor module with HELLO( ) displayed

Once written, you can reference this or any other program unit in the report from any other program unit in the report. You can reference stored procedures in the Oracle database from within your program units as well. You can also reference program units in external PL/SQL libraries that are attached to your report. You will learn how to create these libraries in the next lesson. The ability to reference stored procedures in the Oracle database from your PL/SQL code in Report Builder gives rise to another interesting feature of Report Builder—the ability you have to move your program units that are local to the report into the Oracle database, and vice versa. Storing Report Builder PL/SQL blocks in the Oracle database is handled in the following way. First, you must connect to the Oracle database in any of the following ways. From within the Object Navigator with your report still open, double-click on the Database Objects node and the login prompt will appear, where you enter your username, password, and database connect string to log in to the Oracle database. Or, you can choose the File | Connect command and log into the database. Finally, you can also press ctrl-j to obtain the login screen.

After logging into Oracle, you will see a + next to the Database Objects node in the Object Navigator module. Drill down into the node to see the different schema owners of objects in the database. To place your local program unit hello( ) into the Oracle database under your username, first drill down into the Database Objects node to your username. Then, drill down into your username. You will see four new nodes under your username, Stored Program Units, PL/SQL Libraries, Tables, and Views. Click and hold on the hello( ) procedure appearing under the Program Units node in the report, and drag the program unit down to the Stored Program Units node appearing under your username until a line appears across the Object Navigator module under this node. Then release your mouse button. It may take some time for the report program unit to be copied into the database, depending on the size of the PL/SQL program, but it will get there eventually.

Exercises

  1. Under what Object Navigator node will your PL/SQL trigger code appear?
  2. What process is used for moving program units into the database for storage? What is the utility in doing so?

Creating and Referencing a PL/SQL Library

The final task you will cover in this section is the creation of PL/SQL libraries. Recall from Unit II that a PL/SQL library can be created on the client side to store PL/SQL code you want to make available across reports. To create a PL/SQL library, first you must have some program units to put into a library. You should first write a few procedures and functions. Use some of the simple ones described in the previous lesson to get set up for building a PL/SQL library. Any of the program units stored under the Program Units node in your Object Navigator module for any of the reports can be placed in a PL/SQL library, although care should be taken to define PL/SQL blocks that are reusable across reports.

Placing Program Units in the Library

Once your code is developed, place them into a PL/SQL library in the following way. First, click on the PL/SQL Libraries node in the Object Navigator once to highlight it. Be sure you are clicking on this node and not the Attached Libraries node appearing as a drill down under your report. This Attached Libraries node handles the attachment of a previously defined and working library to your report, not the development of new libraries. After clicking on the PL/SQL Library node once to highlight it, click the Create button in the Tool Palette on the Object Navigator. A new set of items appears beneath this node, the most prominent of which is a new library name, which is automatically generated by Oracle in the format LIB_nnn. You can rename the library as you wish later. At this point, the library is open and ready for development, but not for reference in the report you currently have open in Report Builder. Two nodes appear beneath the node for your new library, called Program Units and Attached Libraries. The program units you are about to add will appear below the first node just named once you add them to the library. As evidenced by the second node, you can attach libraries to your libraries as well.

Add your program units to the open library in the following way. From the Program Units node appearing under the open report, click and hold on a program unit such as the U_1ButtonAction( ) procedure appearing in drildone.rdf. Move the pointer down to the Program Units node underneath the node for your new library. Once you have moved to a place where dropping the program unit is permitted in the library, release the mouse button. You should now see the program unit listed under the Program Units drill-down node for the open library. Click on the Save button in the Tool Palette for Object Navigator, or choose the File | Save menu option to save the library. When using either option, be sure that the Program Units or PL/SQL Libraries node is highlighted so that you’re saving the library. When finished, click once on the name of the PL/SQL library you just created, and either press the Delete key or choose the File | Close menu option to close your library.

Attaching the Library to Use the Program Units

Attaching a library to use in your report is handled in the following way. First, click on the Attached Libraries node once to highlight it and then click on the Create button. Actually, in this situation the name of this button is a bit of a misnomer because you are actually attaching a library that is already created. Think of it as "creating" a new attachment, if not a new library. Anyway, you click on the Create button, and an interface similar to the one in Figure 28-4 appears to guide you through the attachment.

Figure 4: Attach Library interface

Use of the Attach Library interface is as follows. In the text box next to the word "Library," you identify your library filename using its absolute path—either by keying one in or by clicking on the Browse button to locate it using the Open File interface provided by the operating system. Three radio buttons appear to help you identify the source for your library. If you want to attach a library from the database, click the Database radio button. If you want to attach a library from the file system, click the File System radio button. If you don’t care (though normally you will have a pretty good idea of where the library has been saved), click the Either radio button. When finished, click the Attach button, and your library will appear as a drill-down node underneath the Attached Libraries node for your report in the Object Navigator module.

With the library attached, you can now refer to PL/SQL code in the library from within your report. That PL/SQL code can be used within several different reports as well as within other libraries. Note that you cannot modify the PL/SQL code in the library, only run it. If you need to make a change to a PL/SQL program unit in an attached library, you must first open the library.

Exercises

  1. Can you execute code in an open library? Why or why not? Can you modify code in an attached library? Why or why not?
  2. Given several PL/SQL program units exist, how do you create a library comprised of those program units?

Using Report Builder Built-In Packages

In this section, you will cover the following points on using built-in packages in Report Builder:

It has already been covered in these units that Developer/2000 and the Oracle database comes equipped with many built-in packages that extend the functionality of applications you develop in conjunction with them. These packages handle various tasks such as reading and writing files, embedding objects into your application, and many more. The uses of many built-in packages in Report Builder have already been described. This section covers the use of one important package that hasn’t been explained but has already been mentioned. This package is called SRW, and it handles much of the key functionality provided by Report Builder. You will learn more about the use of its procedures and functions and about the output messages it may give when the report executes.. The use of temporary tables as intermediate storage places for report data will be covered in this section as well. Finally, the processes involved in modifying visual attributes at run time is offered as well.

Report Builder SRW Built-In Package Contents

The SRW package offers many of the capabilities given through Report Builder’s graphical interface, except the package offers those features in procedures and functions. In fact, this package offers over 50 procedures and functions that allow you to accomplish many things with your application, including the following:

Without these procedures and functions, you wouldn’t be able to do most of these things from within your reports. For example, you would not be able to develop "master/detail/detail" reports where next to detail records appear buttons that fire a PL/SQL block that, in turn, kicks off another report and passes it appropriate values to run. This type of depth is an enormous value-added feature for reporting on data warehouse applications. SRW makes these things possible. The following lessons give an outline of most of the functions available in the SRW package, broken out by the categories identified in the preceding list of bullets. The full list of packages available within Report Builder is displayed in Figure 28-5.

Figure 5: Packages available in Report Builder

Executing Reports from PL/SQL with SRW

As identified, SRW allows you to execute another report from within PL/SQL code. The key functions involved in this activity is run_report( ), which accepts variables to do its job—such as the name of the report to be run and parameters to feed the job—then feeds this information into Reports Runtime. Two exceptions, run_report_batchno, and run_report_failure, support the cornerstone run_report( ) procedure by handling situations where the report was run with the BATCH parameter equaling no (meaning the report must be run interactively), and in general report failure situations. Another function, geterr_run( ), can also be used for determining exactly what error occurred when run_report_failure gets raised. The next lesson will cover the activity of running reports from other reports in more detail.

Using SRW for Processing SQL

SRW offers a packaged procedure called do_SQL( ) that handles processing of SQL statements from PL/SQL. A supporting exception called do_SQL_failure exists when the do_SQL( ) procedure gets an error. Before reading any further, recall in Unit I the comment that PL/SQL integrates seamlessly with Oracle SQL and the Oracle database. Yet, here you see evidence that there is a packaged procedure that does the same thing. Is it better or worse to use a packaged procedure to execute your SQL statements? Unfortunately, the answer is yes and no. For most DML processing involving static SQL, you should simply write the SQL statement in your PL/SQL code. However, there is one situation where you can’t use SQL running directly from PL/SQL, and that is the situation where you want to create tables. More on that one shortly. You can use it for update, insert, and delete statements, too, but do_SQL( ) is a lot slower than regular SQL statements running in Oracle.

Tracing Report Activity with SRW

The tracing facilities offered in Report Builder are also available in the SRW package. The procedures in this category include trace_start( ) and trace_end( ) for starting and ending a trace, respectively. To remove and set options for the tracing run, the trace_rem_option( ) and trace_add_option( ) procedures are available as well. Special predefined types are used to pass values to these procedures, and they will be covered more in the next lesson.

SRW for Report Formatting

A blizzard of options unfolds for you in the SRW package for report formatting. There are about 25 procedures in this package prefixed with the word "set" that handle such items as printer options, fonts, character spacing, background color, text color, borders, and more.

Using SRW to Create Temporary Tables

Picture this. You are trying to create a report that has several intermediate steps, each of which depends on the filtering activity of the previous step. Your reports work against 20 different production tables. You analyze the situation and determine that you need temporary tables that will be used as holding grounds for your report data. The question then becomes how to create them. You can, for example, create a production table that always sits out there ready for report use, but if you have several reports all needing temporary tables, this option can become hard to manage. Instead, you can use the do_SQL( ) procedure to handle the creation and removal of temporary tables.

Web-Enabling Reports with SRW

Finally, you can deploy your reports on the Web using SRW. Several of the "set" procedures associated with formatting and output also provide Web support. The procedures available for this purpose include the following:

Exercises

  1. Identify the package that contains many procedures offering functionality provided by Report Builder.
  2. What are some of the capabilities this built-in package provides you? Identify at least one function or procedure from the package associated with providing that capability.

Using SRW Package Procedures and Functions

The procedures in the SRW package are used in the same way that other package procedures and functions are used. You can reference the SRW package from any PL/SQL block in Report Builder by referring to the procedure you want to use by name and prefixing the reference with the name of the package. Thus, if you wanted to use the procedure run_report( ) in the SRW package, you might write the following block in the PL/SQL Editor for that report:

Procedure U_2ButtonAction is
begin
...
srw.run_report( ... );
...
end;

Using Procedures to Execute Reports

Since we’ve started with run_report( ), let’s just focus on running reports from PL/SQL with the run_report( ) procedure in SRW. This procedure accepts just one parameter: a text string. The text string you pass to this procedure should contain every item you would have included to run the same report from the command line except the r30run executable. Items you include in the parameter you pass to run_report( ) include command line options, the name of the report being run, and so on. For example, say you have a report called sfbm.rep that outlines the sales figures by month. This report accepts a user-defined parameter called MONTH that you pass a known three-character string representing a month, such as jan or nov, along with the usual suspects for system parameters. The following code block illustrates the sample PL/SQL block again, this time with the command line parameter defined. As always, be sure you are connected to the database before you execute the Runtime Previewer to see the output for your report.

procedure U_2ButtonAction is
begin
srw.run_report('report=sfbm month=sep batch=yes');
end;

TIP: For the REPORT parameter, a .rep filename must be specified.

You should now understand the basic premise of using SRW procedures and functions in your PL/SQL in the report. These next few lessons will cover the use of procedures for some of the other tasks SRW allows you to do, such as creating temporary tables and formatting output.

Exercises

  1. How are procedures and functions referenced in PL/SQL code if they are stored in packages?
  2. What parameter is passed to run_report( )? What does the parameter define?

Output Messages at Run Time

An output message at run time will tell you if there are problems with the report’s execution. These messages are generated when exceptions defined for the procedure or function are raised. The run_report( ) procedure has two exceptions it raises that you should know about: the run_report_batchno exception and the run_report_failure exception. If your report does not execute properly, various errors will kick out in support of each exception. The run_report_batchno exception is raised when your command line string passed to run_report( ) causes the report to run interactively. This cannot happen. In order to use run_report( ), you must flag the report to run in batch by specifying yes to the BATCH parameter. The run_report_failure exception is raised for general report failure.

In the course of running your reports with the SRW procedure run_report( ), you will generate output messages at the time of report execution that you should know how to address. You will not know if the command line you pass to run_report( ) is valid or invalid at report compile time because all the compilation does is check to see of the text string you passed is valid text. Thus, if you want to see the output message for the error, you must get familiar with any exceptions associated with the procedure or function in SRW that you are using, and then code an explicit exception handler in the exception section of the PL/SQL block from which you are calling the SRW procedure.

Recall that several exceptions exist in conjunction with the procedures of SRW. The run_report( ) procedure has run_report_batchno that gets raised by run_report( ) when your report command line causes the report you attempt to run to do so in interactive (as opposed to batch) mode. This procedure has the run_report_failure exception that is raised when more general problems arise. If you want your report to handle these situations gracefully, you must code an exception handler in the PL/SQL block from which you call run_report( ). The following code block shows the code for U_2ButtonAction( ) again, this time with exceptions defined:

procedure U_2ButtonAction is
txtstr CHAR(100);
begin
srw.run_report('report=sfbm month=sep batch=yes');
exception
when srw.run_report_batch_no then
SRW.message(150,'Report was executed where batch did not equal no.');
raise SRW.program_abort;
when srw.run_report_failure then
txtstr := srw.geterr_run;
SRW.message(110,txtstr);
raise SRW.program_abort;
end;

Now let’s talk about the actual code you have in the exception handler for this example. You identified the different exceptions raised by run_report( ) in your when clauses in the exception handler by calling another SRW procedure: message( ). The message( ) procedure puts a message on the screen in a dialog box that you defined in the procedure call. One of two things can happen after an exception within run_report( ) is raised. After you review the error in the dialog box and accept it by clicking the OK button, the "master" report can either continue execution or it can abort. In the exception handler defined for run_report_batchno, if this exception is raised in run_report( ) a dialog box will pop up with the message you passed to message( ). When you click the OK button, report execution continues. When the run_report_failure exception is raised by your call to run_report( ), the dialog box again opens with the message you passed to message( ) for that call and you click OK to acknowledge it. But, after you click OK, the execution of your report stops because you raise the program_abort exception. Use of this exception is not mandatory; you should use it only when serious exceptions are raised.

TIP: The geterr_run( ) function returns a specific error message for failure during execution of run_report( ), and is used to define the text string you pass to message( ) in this situation. The text string you define as the variable used to store the result from geterr_run( ) should always be of type CHAR(100).

Let’s talk a little more about the message( ) procedure in the SRW package. It accepts two parameters: a number and a text string. This procedure causes a dialog box to appear on the screen during report execution that contains the text string and number you pass to message( ). For example, the dialog box that will be displayed for the call to message( ) shown in the when run_report_failure clause of the exception handler in the code block you just reviewed is shown in Figure 28-6.

Figure 6: Dialog box displayed by a call to message( )

TIP: The SRW.program_abort exception kills execution of your entire report and must be raised explicitly by your PL/SQL block within the exception handler to work. There is definitely a question that pertains to this subject on the OCP exam, although it might be mistakenly be referred to as a procedure or function! Don’t be fooled, though, it is an exception.

Available Exceptions in SRW

The following is a list of exceptions that are raised within the execution of procedures and functions in SRW. Next to each exception is a basic description of when the exception is raised, and from which procedures and/or functions in SRW the exception may be raised.

TIP: The numeric value you pass to message( ) can be used to identify the area of PL/SQL code in the report that is the origin for the dialog box message being displayed. The number should be between 1 and 10 digits, but if the number is less than 5 (i.e., 59), leading zeros will be padded to make the number of digits shown 5 (i.e., SRW-00059). This feature can be useful for tracking down PL/SQL containing bugs.

Exercises

  1. What must you do in order to properly display messages of exceptions raised by procedures like do_SQL( ) and run_report( ) in the SRW package?
  2. What exception kills execution of your report when it is raised? How is this exception typically raised in your PL/SQL blocks?
  3. Identify the functionality of the message( ) procedure. What parameters is it passed? Identify the functionality of the null_arguments exception. From where might it be raised?
  4. What is the difference between program_abort and user_exit( )?

Using Temporary Tables in Reports

Temporary tables are sometimes used in reports to store data while the report runs. In some cases, your reports will obtain a set of data from the database by running the data through multiple filters. In any event, you may find it useful to store data from a report in a temporary table. However, there are some challenges for doing so. Two obvious places you will target for your creation and elimination of the temporary table include the beginning and end of the report, respectively. Report Builder answers the challenge with two built-in times in your report where a trigger fires. Those two points are the BEFORE REPORT and AFTER REPORT triggers, respectively.

TIP: Although BEFORE REPORT and AFTER REPORT may seem like obvious candidates for building temporary tables to be used in reports, you can create or remove your temporary tables anywhere in the report. Just be sure that you don’t attempt to remove a temporary table before creating it, or leave a temporary table out there after the report is done.

However, as soon as you solve the obvious problem, an even bigger challenge arises. This challenge is executing the create table statement from a PL/SQL block properly in your report. You can explore several options for creating your table, one of which may be to create a stored procedure for doing so in the Oracle database that you call from PL/SQL code in the report. You then set up the create table statement through the use of the DBMS_SQL package, which allows you to open, parse, and execute SQL statements on the fly from within PL/SQL. You could even set up the calls to DBMS_SQL within the PL/SQL in your report. But a problem exists with this method in that issuing create table or other DDL statements via DBMS_SQL can cause hangs. Take the time now to review the comments in the package specification for DBMS_SQL to better understand the functionality it provides.

Your method for creating temporary tables in reports will be to use the do_SQL( ) procedure available in the SRW package instead. This procedure allows you to execute any SQL statement including create table, update, and others. However, given the integration of SQL statements into PL/SQL, your best bet is to use do_SQL( ) only in situations like creating temporary tables or other DDL operations not permitted directly in PL/SQL.

Use of the do_SQL( ) procedure in SRW is handled as follows. You pass the procedure a text string containing any valid SQL statement, such as create table. The do_SQL( ) procedure then parses and executes your statement. Unlike calls to server-side stored functions in the DBMS_SQL package, there is no need to define variables to store the address of your cursor in memory; do_SQL( ) handles all that behind the scenes. Your BEFORE REPORT trigger for creating temporary tables used by the report may look something like the code in the following block:

-- Code for the BEFORE REPORT trigger
function BeforeReport return boolean is
begin
srw.do_SQL('create table tmptbl1 (foo number(10), foo_name varchar2(10))');
return (TRUE);
exception
when srw.do_SQL_failure then
srw.message(90, 'Fatal error when creating table tmptbl1. Abort.');
raise srw.program_abort;
end;

While the code for the AFTER REPORT trigger may look something like the contents of the following code block:

-- Code for the AFTER REPORT trigger
function AfterReport return boolean is
begin
srw.do_SQL('drop table tmptbl1 cascade constraints');
return (TRUE);
exception
when srw.do_SQL_failure then
srw.message(90, 'Fatal error when dropping table tmptbl1. Abort.');
raise srw.program_abort;
end;

TIP: If you include reference to any objects in the report such as columns or variables, you should precede those references with a colon, just like any other bind variable.

Exercises

  1. What procedure is used to execute DDL statements like create table and drop table from PL/SQL blocks in your report?
  2. At what places in a report might you define a PL/SQL block to call this procedure to create and drop temporary tables in your report?

Modifying Visual Attributes at Run Time

Modifying visual attributes of your report at run time is an area of change between Developer/2000 1.6 and 2.0. In releases of Developer/2000 prior to 2.0, the set_attr( ) procedure is used to modify visual attributes of your report, and you passed both a code for the attribute you wanted to change along with the value you wanted to change the attribute to. In 2.0 or later, the lion’s share of procedures available in the SRW package pertain to setting and modifying visual report attributes during run time. About 25 procedures are designed for this purpose, organized around the general form set_attr_desc( ), where attr_desc corresponds to a short description for the attributes that used to be passed to set_attr( ), while adding a few more. For backward compatibility with Developer/2000 1.6 certification, the use of set_attr( ) will be covered along with that for version 2.0.

Using the SRW.SET_ATTR( ) Procedure

This is a fairly complicated process where you first set an attribute mask and then set the value for that mask in a special record called srw.attr. Once the srw.attr record is set, you call the set_attr( ) procedure, passing two parameters, the first of which is almost always going to be 0.. The second is the attr record where your attributes are defined. The following example presents where you set attributes for the text color to red in situations where summary values are negative numbers. Several other attributes are available—consult the Report Builder online help documents for more information. The following code block displays your use of the attr record and the call to set_attr( ):

function F_FormatSumValueColor return boolean is
begin
if :colsum < 0 then
srw.attr.mask := SRW.GCOLOR_ATTR;
srw.attr.gcolor := 'red';
srw.set_attr(0,srw.attr);
end if;
return (TRUE);
end;

Notice that your first step in the setup of the set_attr( ) procedure call is to define a value for srw.attr.mask, an element in the srw.attr record. This definition sets up the next definition of the actual value for the attribute you specified for srw.attr.mask. Finally, you make the call to set_attr( ). Each attribute that is set with set_attr( ) has a special code, like srw.attr.gcolor for text color. For more information about attributes and their special codes, check out the Report Builder online help documentation.

TIP: Four situations exist where the first variable you pass to set_attr( ) will not be 0. When you are setting attributes for befreport_escape, aftreport_escape, befpage_escape and aftpage_escape, you must pass the value set automatically in the SRW package corresponding to report_id. Even so, escape sequences are dying out with GUIs in full force.

Using the Set of SRW.SET_ATTR_DESC( ) Procedures

With Developer/2000 2.1, the setting of visual attributes at run time is greatly simplified with the elimination of the need for defining an srw.attr record. Instead, you simply call the procedure that handles defining the visual attribute you want to define, and pass it the appropriate value for the attribute. The text color change example described in the previous lesson would be handled in Developer/2000 2.1 with the set_text_color( ) procedure in the SRW package, which accepts one parameter, a text string defining color. Consider the following code block, which performs the same function as the one in the previous lesson:

function F_FormatSumValueColor return boolean is
begin
if :colsum < 0 then
srw.set_text_color('red');
end if;
return (TRUE);
end;

TIP: Several restrictions exist with setting attributes for report deployment to the Web. This topic is not covered in the OCP exam, and is therefore beyond the scope of our discussion here. If you have questions about it, consult Report Builder online help documentation.

Exercises

  1. What two ways exist for defining visual report attributes with PL/SQL at run time? Which is new to Developer/2000 2.0? Which is provided for backward compatibility?
  2. What is the name of the record used in conjunction with defining report attributes available in Developer/2000 prior to release 2.0? What elements exist in that record, and how are the elements in the record determined?

Maximizing Performance Using Reports Server

In this section, you will cover the following points on maximizing performance using Reports Server:

So far, the architecture assumed for report deployment in the discussion has been the traditional client/server architecture, where reports are run and printed from either the same machine as that running the client application or the same machine as that housing the database. With the advent of N-tier applications, however, you have yet a third environment from which to run your reports—the application server. This section covers use of the Reports Server architecture in conjunction with N-tier applications. A comparison of both environments is offered, along with an overview of the architecture supporting Reports Server. The use of Queue Manager to schedule report jobs is given as well, along with an advanced topic—embedding ActiveX controls into your applications for direct interface with Reports Server.

Comparing Local Client and Report Server Reporting

Two methods exist for report processing and distribution. You can execute and print reports from the PC on your desktop, or from a server. The first method demonstrates a "fat client" setup, where the client application is packed with application logic and reporting mechanisms to handle all the needs of the distributed application. The second shows the possibility of moving toward a "thin client" setup, where most information processing is handled centrally via a server. Figure 28-7 summarizes much of the discussion that follows regarding the comparison of local client and report server reporting architectures.

Figure 7: Client reporting vs. server reporting comparison

Certain benefits exist for each model. From the client perspective, the benefits are distributed processing lessens the processing burden handled by any individual machine in the system. For example, if an OLTP application handles all transactions processed as well as all the reporting, there could be performance degradation on the application overall. The client-centric distributed processing model makes use of the explosion of processing power, memory, and disk space available on PCs today. If your report contains a lot of processing, such as heavy PL/SQL processing in conjunction with formula or summary columns, you may want to distribute the report load onto the client to improve overall performance for the system.

There are drawbacks to the client-centric processing model, however. The first problem comes when you want to distribute a new report to each client. There may arise issues with portability, such as the file systems on two PCs not being the same. Issues with portability arise also when your office uses heterogeneous operating systems on the desktop. The development cost of client/server applications has been high as well, with the cost of PC upgrades, software upgrades, and distribution. Scalability can be a challenge for these and other reasons as well.

A development has occurred in office computing that many companies including Oracle are paying attention to. That development is the explosion in Internet technology. This new development allows an organization to return to the server-centric system deployment perfected over the past 30 years with the use of mainframe technology, coupled with advances in GUI development. The most obvious benefit of this architecture is portability. Since most of your software runs on a server, you don’t need to worry as much about porting applications between machines. Another advantage is scalability. The Internet is highly scalable, making it easy to add more users to the system. The overall cost for deployment may be less expensive as well, given a fixed cost for a limited number of powerful servers and inexpensive network computers working mainly through Web browsers. This architecture extends to report server processing as well, where a dedicated machine handles report processing and dissemination of reports through the organization, resolving the portability and deployment issues by using only one machine to handle all report processing.

Despite this apparent swing back in favor of centralized processing, reminiscent of mainframe processing of years gone by, there are some weaknesses in network computing that need to be addressed. The first is the very nature of connections via the Internet. You basically need to create a connection between client and server in a TCP/IP network, which is a connectionless protocol—thus, the need for an application server connected to the database synchronously, to which the thin-client browser application interacts in the connectionless paradigm. However, Oracle Reports does extend much of its current functionality to deploy reports to the Web as well. Companies like Oracle will continue to fill in other technology gaps such as security. Given the difficulties most companies have with development and maintenance of client/server applications, and as the gaps in what Internet technology can do fill, more organizations will move their systems to the Internet or intranet.

NOTE: Since starting Reports Server varies from operating system to operating system, and because it is not a topic tested by OCP, it will not be covered here. If you want info on how to start Reports Server, refer to the Developer/2000: Guidelines for Building Applications manual that comes with your Developer/2000 software release.

Exercises

  1. Describe the advantages of handling report processing from the client. What are its disadvantages?
  2. Describe the advantages of handling report processing from a network-centric perspective. What are the disadvantages to network computing.

The Reports Server Architecture

With these thoughts about client/server and network computing in mind, turn attention now toward how Oracle Reports implements a network computing paradigm with Reports Server. The server process itself runs on a machine that can be dedicated to its effort. Rather than handling report processing themselves, clients send their reports to the Reports Server for processing, where the report jobs are held in a queue. There are one or several report-processing engines running that handle the actual execution of the report. As reporting engines become free, Reports Server sends another report job from the queue to be processed. The Reports Server can also start new runtime engines if the queue gets too large, and shut down report engines if the queue is empty and the engines sit idle.

As one may imagine, Reports Server handles report job scheduling as well. The execution time and how often a report runs is tracked using features in the Queue Manager, a tool that works in conjunction with Reports Server. A certain amount of information about job execution is tracked as well through the Queue Manager. The Report Server architecture works in the following way. You push your report to the Reports Server with the use of the r30cli executable. Reports Server then receives the report for processing and places the report in the report queue. Available runtime engines pick up reports to be run off the queue and process them. When finished, the output is placed in a location defined in the report definition. The report output is then printed to a destination in the report definition, sent to a user via email through Oracle Office or MAPI, or made available for Web browsers to pick up through a Web server using Reports Web cartridges or a Web server using the Common Gateway Interface (CGI) running the Reports CGI executable. This N-tier architecture is displayed in Figure 28-8.

Figure 8: Reports Server architecture

TIP: The Queue Manager allows you to interface directly with the Reports Server to see which reports are running and those that remain to be run.

Exercises

  1. What is the architecture of Reports Server? What tool is used to see the reports being executed in Reports Server?
  2. What are three ways users see reports through Reports Server?

Viewing and Scheduling Reports in Queue Manager

A report is held in a queue on the Reports Server until a runtime engine becomes available for processing the report. A tool called Queue Manager handles viewing and scheduling reports for processing in the Reports Server. The use of Queue Manager is handled as follows. You first start the execution of Queue Manager by clicking the appropriate icon or by typing r30rqv or r30rqv32 from the operating system command line, depending on whether you use UNIX or Windows. When you begin execution of Queue Manager, you will be prompted to enter the name of a queue to manage, as shown in Figure 28-9. You may choose to ignore this when opening Queue Manager by clicking the Skip button, or specify the name of a Reports Server listener on your network as it appears in the tnsnames.ora file or in the Oracle Names server.

Figure 9: Specifying a queue to manage with Queue Manager

Scheduling Reports with Oracle Reporting ActiveX Control

Once you specify your queue to manage and click OK, Queue Manager opens to its main interface and you can begin viewing and scheduling jobs in your report queue. The Queue Manager main interface will be shown later. For now, your first task is going to be to schedule a job to run at a specific time. For this example, we will schedule execution of a new report called db.rep. The job is scheduled by clicking on Job | New, which opens the Run Report dialog box. Since this is a new job, you will see question marks for the report to be run and the database connection string Reports Server will use to run the report. Click on the Set Options button to invoke the Oracle Reporting ActiveX Control Properties interface, shown in Figure 28-10. The ActiveX Control Properties interface has several tabs that require elements to be defined. The following discussion presents those tabs and what you need to define for each of them.

Figure 10: Oracle Reporting ActiveX Control interface

General Tab

This is where you define your report name, name of the output file, output destination, and name of the Reports Server that will handle the job. To define the report name, click on the button appearing to the right of the Report Name text box to browse for your report runtime file with the .rep extension. Then, select the report you want to run from the file system. When finished, click OK. Your report destination may be defined from the report Destination drop-down list in this tab as well. You can define a printer, file, or Oracle InterOffice username as the output name in the text box appearing below the report Destination list. Finally, the default Reports Server shown in that drop-down list should suffice as well. Figure 28-11 shows the contents of the ActiveX Control interface General tab.

Figure 11: General tab of Oracle Reporting ActiveX Control interface

Data Source Tab

Use of this tab is standard: simply define the username, password, and database connection string Reports Server will use to execute the report job.

Output Options Tab

This tab defines output options such as whether the report runs in bitmap or character mode, print page orientation, and a driver for formatting output in Web formats. You can define the size of print pages and number of copies to be printed as well. Your definition of these elements corresponds to runtime parameters. In this example, select Bitmap for Report Mode, Portrait for print page Orientation, and Letter for Page Size. The Output Options tab is shown in Figure 28-12.

Figure 12: Output Options tab of Oracle Reporting ActiveX Control interface

Parameters Options Tab

Use this tab to define options about system and user parameters. If you want to use a command file containing specifications for your parameters, you define it on this page. You can show or suppress the runtime Parameter Form for the report using the check box for that purpose on this page as well. Finally, user parameters can be identified here as well. The Parameters Options tab is shown in Figure 28-13.

Figure 13: Parameters Options tab of Oracle Reporting ActiveX Control interface

Server Options Tab

This tab is used to define options about how Reports Server handles the execution of this report. Items defined include how Reports Server handles the remote procedure call. Options for this item include sending the report to a local Reports Server for processing, sending to a remote Reports Server and waiting until the Reports Server comes back with report completion (synchronous execution), or sending to a remote Reports Server and polling for status until the job completes (asynchronous execution). For the most part, you will use asynchronous execution. The value specified for Report Run Progress Frequency is how long in seconds Queue Manager will wait before asking Reports Server for status again. The value specified for Report Run Timeout is how long in minutes after which if the report hasn’t run, it won’t be run. Specifying both to be zero means that polling and timing out features are turned off for this report. Finally, you can specify the name of the report as you want it to appear in the report queue. Figure 28-14 shows the Server Options tab in the ActiveX control. When you are finished defining your options in each tab, click OK at the bottom of the ActiveX interface.

Figure 14: Server Options tab in Reports Server ActiveX Control interface

Scheduling Options Tab

This tab handles the actual scheduling of your job in Reports Server. You can schedule a job to run immediately or at a specific time and date by clicking on the appropriate radio buttons in the interface. You can also specify the frequency at which the job will repeat. In this case, let’s say you will schedule the job to execute at 12:30 PM on December 25, 2000, and then once a week thereafter. Figure 28-15 displays the settings you would choose for report scheduling based on this description.

Figure 15: Scheduling Options tab in Reports Server ActiveX Control interface

Runtime Options Tab

In this tab, you specify a few different things: how transactions behave in the report, values for CURRENCY, THOUSANDS, and DECIMAL system parameters, size of a memory array to be used for array fetching by Reports Server as it obtains your data, and the size of the buffer you will use to store chunks of data retrieved from columns of LONG datatype. You can specify transaction processing within the report to be Transactions Read-Only, Automatic Commit, or Use Non-Blocking SQL with the appropriate radio buttons. Also, you can specify what transaction-processing operation should be done if the report run succeeds or fails, such as commit on success or rollback on failure. Figure 28-16 displays this tab configured in the manner described.

Figure 16: Runtime Options tab in Reports Server ActiveX Control interface

Debugging Options Tab

The final tab is used for runtime debugging of your report. In it, you can define tracing options similar to those available in Report Builder. You can also define error, log, and tracing files to contain associated information about the report run. Finally, you can define how Reports Server will modify the files specified for tracing as well.

Figure 17: Debugging Options tab in Reports Server ActiveX Control interface

TIP: This discussion is necessarily high-level for the purpose of preparing you for the OCP exam. You should practice use of Reports Server on your own before you certify.

Viewing Reports in Queue Manager

Once your report has been scheduled, you can view it in the Queue Manager interface, displayed in Figure 28-18. Along the top of the interface are controls you can use to view different categories of reports. From left to right, the tools are the Queue drop-down list, the Show Past Jobs button, the Show Current Jobs button, the Show Scheduled Jobs button, the Show All My Jobs button, the Show All Jobs button, and the Refresh button . These tools mirror functionality provided in the View menu in the Queue Manager interface as well. Below those tools is an open area where reports queued on the Reports Server are displayed by category depending on which button is toggled down. To see the job you just scheduled in the Queue Manager, click on the Show Scheduled Jobs button in the Queue Manager interface, or on the View | Scheduled Jobs menu command.

Queue drop-down list

Show Past Jobs button

Show Current Jobs button

Show Scheduled Jobs button

Show All My Jobs button

Show All Jobs button

Refresh button

Figure 18: Queue Manager interface

Exercises

  1. Identify the interface used to schedule report jobs with the Reports Server from Queue Manager. What are some of the different features of this interface and how are they used?
  2. Identify the tools in the Queue Manager interface that show different views on jobs scheduled with Reports Server. What menu contains commands that mirror these button tools?

Invoking Oracle Reporting ActiveX Control from Other Apps

The ActiveX control used to schedule reports with Reports Server can be invoked from other applications besides the Queue Manager. However, to do so is fairly complicated. It is beyond the scope of this certification guide to discuss deployment of ActiveX control deployment in the detail required for most programmers, given the fact that this topic constitutes only a small portion of OCP test content. For the purpose of knowing enough to pass the OCP exam, you must perform the following tasks to use the Oracle Reporting ActiveX control in another application, such as Oracle Forms. In general, when using ActiveX controls, you must register the ActiveX control with the client machine you will call the ActiveX control from. Two executables are available for this purpose from ActiveX control vendors or others: regActiveX32.exe and regsvr32.exe. You must also ensure that all required dynamic link library files with the extension .dll are placed in the windows/system directory on the client machine. The second task for using an ActiveX control in your application is to place the object into your application for use. The actual steps for this activity depend on the development environment into which you are deploying the ActiveX control.

TIP: Deployment of ActiveX controls in other applications is not possible on the Web or on UNIX machines.

Exercises

  1. What executables are used to manually register an ActiveX control?
  2. In what environments is it not possible to use ActiveX controls?

Building Reports for Different Environments

In this section, you will cover the following points on building reports for different environments:

The final area you will cover for developing reports is the requirements for building reports in different environments. The meaning of "different environments" is twofold, as either a different machine can be a different environment or a different country can be a different environment. This section identifies factors for report development in both instances. The use of the MODE parameter for building character and bitmap reports in different environments and considerations for building reports for different user interfaces are described in some detail as well. The special characteristics of character-based reports will also be reiterated, along with the facilities for building reports in different human (not computer) languages.

Building Reports for Different Environments

A report can run in one of two modes, character and bitmap. Whether the report runs in character or bitmap mode depends on the value specified for the MODE parameter. Permitted values for this parameter are character and bitmap, respectively. This parameter can be set in three different ways: either as a command line parameter passed in to Reports Runtime, as a runtime Parameter Form, or in the Property Palette for the MODE parameter.

As identified early in the unit, some parameters available for use on the command line when running a report can have different meanings depending on whether the report mode is bitmap or character. For example, the DESFORMAT system parameter is used to define the driver for a printer when you run a character mode report, while it means the driver that should be used for formatting output when a bitmap report is sent to a file. The PAGESIZE parameter represents the size of printed page in inches, centimeters, or picas for bitmap reports, while that same parameter defines the size of a page in characters for character-based reports. Other parameters, such as TERM, are ignored when MODE=bitmap because the only time Reports Runtime cares about the UNIX terminal being used is when the report is character-based. Thus, you will want to pay attention to the parameters you pass to Reports Runtime when executing reports in character or bitmap mode.

Exercises

  1. Identify the parameter that determines whether the report runs in character or bitmap mode.
  2. What parameters whose have different meanings depending on whether the report runs in bitmap or character mode?
  3. Name a parameter used only when a report runs in character mode?

Considerations When Building Reports for Different GUIs

You need to take the differences between user interfaces into account when deploying reports for different ones. For example, character-based reports may look different on different terminal types. Also, your margin layout that includes a graphic for bitmap reports will not look the same on a character-based report. These are all considerations you must make when building reports for different user interfaces.

Several factors come into play when deploying reports via the Web as well. The appearance of a report displayed in HTML will not necessarily look the same from one Web browser to another, based on whether the browser window is resized on the desktop. HTML is also not as precise a layout tool as other publishing formats. Several products have emerged to allow for precision formatting such as what you would expect for hard copy to be deployed in the Web through the portable document format (PDF), and Oracle Reports supports deployment of reports in this format to take advantage of the benefits provided by this format.

Exercise

  1. What are some factors to consider when deploying reports to different user interfaces based on character or bitmap mode?
  2. What are some factors to consider when deploying reports to the Web?

Character Mode Reports and Their Settings

You should take a moment to review the list of parameters defined for Reports Runtime at the beginning of the chapter. Some of the parameters you should review have already been named. They include DESFORMAT, TERM, PAGESIZE, and MODE. For design of report Layout Model and Data Model aspects for character-based reports, you should use the appropriate settings in the Property Palette for the report. On a new report, set the Initial Value property for the MODE parameter to Character. Set the Design in Character Units property for the report to Yes. Report page width and height properties should be set in terms of characters, 80 × 66 for letter-sized pages in portrait orientation, 132 × 41 for letter-sized pages in landscape orientation. It may be useful for you to change the font displayed in your Layout Model to Courier 12 point—the font used in character-based report output.

TIP: Remember, there are properties you can set to develop character-based reports in the property sheet for the report, opened within the Property Palette.

Exercises

  1. What properties will you set for the MODE parameter to produce a character-based report? What properties will you set for the report overall for character-based reports?
  2. What font is used to display output for character-based reports?

Facilities for Building Reports in Other Languages

Report Builder offers facilities for building reports that display information from languages besides English. There are procedures and functions available to test the language environment to determine if, for example, text information must be displayed from right to left, as in Hebrew and Arabic languages, or if the decimal place should be represented with a comma (,) instead of a period (.). Report Builder has a package called ORA_NLS. A partial list of functions in this package that may appear on the OCP exam and a brief description of their usage follows:

Oracle Reports displays information in a language based on the values set for language environment variables for the machine running or displaying the report. You can set variables like NLS_LANG for the purpose of determining the native language of the computer on which the report is run or displayed as well. Values for this environment variable fall into the form language_territory.characterset. The default value for NLS_LANG for American users is AMERICAN_AMERICA.WE8IS08859P1. In some cases, the development of the product may be done in one language while the deployment may be in another. For these situations, two other environment variables are provided. Those variables are DEVELOPER_NLS_LANG and USER_NLS_LANG, which can each be specified as different language sets.

TIP: These discussions have been necessarily brief to help you focus on the content as tested in the OCP exam. For more information about national language support, consult the Oracle Server Administrators Guide, Appendix C.

Exercises

  1. Identify some functions that are part of the ORA_NLS package. What are their respective purposes?
  2. Identify some environment variables used to determine how language data will be displayed in a report.

Chapter Summary

The final chapter of the unit covering OCP Exam 5 instructed you on several advanced topics in Oracle Reports, including coding PL/SQL triggers, use of built-in packages in Report Builder, maximizing performance with Reports Server, and building reports for different environments. These advanced topics comprise a total of 18 percent of OCP Exam 5 test content.

The first area you covered was coding the PL/SQL triggers in reports. You learned about the differences between different triggers. You covered action triggers, or those triggers that occur as the result of an event such as the clicking of a button. You also covered use of the main report triggers that fire before and after processing of the Parameter Form, before and after report processing, and between report pages. Another set of triggers you covered included debug triggers and their usage, which are helpful in ironing out problems with PL/SQL execution in a report. The final set of triggers you covered were format triggers, which are used to dynamically alter the format of a report based on certain conditions.

Tasks involved in writing common code was the next topic you covered in the chapter. You identified how to reach PL/SQL blocks defined in your report through various methods using the Object Navigator module. You learned about the use of the report Runtime Previewer to get a working button to test execution of action trigger code and covered how to move PL/SQL program units into the database for storage and what the value of doing this is. The topic of creating and referencing code in a PL/SQL library and how to attach a library to a report was covered as well.

After that, the chapter moved on to covering use of Report Builder built-in packages such as the SRW package. It was explained that this SRW package contains many utilities for executing reports from within other reports, processing SQL DDL statements like creating temporary tables, report tracing, formatting dynamically at run time, and Web deployment of reports. Use of the run_report( ) was covered, along with the geterr_run( ) function and the exceptions that can be raised by run_report( ). The use of do_SQL( ) for creating temporary tables was covered as well. The importance of understanding the exceptions that can be raised by SRW package procedures and functions, and coding exception handlers to make report processing go smoothly, were other topics you covered. Review these topics before taking the OCP exam, including the exception srw.program_abort, and why it may be invoked explicitly by you to cancel overall report execution.

The creation of temporary tables was another area covered in detail this chapter. Triggers can fire to create and remove the temporary tables, but since PL/SQL cannot execute create_table statements, you must use the srw.do_SQL( ) procedure to handle these sorts of activities in your PL/SQL code. Sample PL/SQL code you may use in your BEFORE REPORT and AFTER REPORT triggers was offered, but it was also pointed out that it doesn’t matter when you create or remove the temporary tables in report execution so long as the table is not removed before it is created, or left out there after the report completes.

The two methods for modifying visual attributes on your reports at run time were also covered in the chapter. The first was the use of set_attr( ), along with a special record called srw.attr, which has a mask element and another element depending on what attribute is being set. This is the method used in Developer/2000 versions prior to 2.0. After version 2.0, you have a large supply of procedures with names in the format set_attr_desc( ), where attr_desc is an attribute description. Calls to the "set" procedures in version 2.0 are generally preferred to defining the srw.attr record then calling set_attr( ) in versions prior to 2.0 because of the relative ease in the former method.

Use of Reports Server was another topic of note covered in this chapter. The use of client-side reporting and its advantages and disadvantages were offered, along with a description of using Reports Server for the purpose of handling report processing. For the OCP exam, make sure you understand Figure 28-8, where the overall Reports Server architecture is explained. Queue Manager and its use for scheduling and viewing reports queued in Reports Server was another topic covered by the chapter. The scheduling of reports with the Oracle Reporting ActiveX Control interface was covered in some detail, and a brief outline for calling the ActiveX control from other applications was touched on as well.

The final topic covered was that of building reports to run in character and bitmap mode, along with the details of defining reports to run in each. Whether a report executes in character or bitmap mode depends on whether the report is run with the MODE parameter set to bitmap or character. Several parameters were identified that either mean different things or aren’t used depending on which mode is used to execute the report. Other properties in a report were identified for use when you want to develop a character-based report specifically. Last, the considerations and facilities for building reports to use for other languages were offered. Use of the ORA_NLS package and environment variables like NLS_LANG, USER_NLS_LANG, and DEVELOPER_NLS_LANG were discussed. Your understanding of these elements is key to success with OCP Exam 5.

Two-Minute Drill

Hosted by uCoz