10g (9.0.4)
Part Number B10602-01
 Home |
 Contents |
 Index |
| Oracle Reports Building Reports 10g (9.0.4) Part Number B10602-01 |
|
This chapter introduces the concepts for advanced users of Oracle Reports. Each topic in this chapter is also included in the Advanced Concepts section of Reports Builder online help (see Section 3.1.1, "Using the online help").
Topics are grouped into the following sections:
The topics in this section build on the basic concepts discussed in Section 1.2, "Reports".
You can add a title to a report in either of the following ways:
When you use the Report Wizard to add a title and do not select a template for your report output, the title is inserted into the margin of the report with default attributes defined by Reports Builder. You can modify the attributes in the Paper Layout view.
When you use the Report Wizard to add a title, and do select a predefined template or a user-defined template file for your report output, Reports Builder looks for a boilerplate text object named B_OR$REPORT_TITLE defined for the selected template:
For layouts created using the Report Block Wizard, the title is inserted into the new layout as a group title rather than into the margin of the report. In this case, the attributes are set per the Default properties (under the Title node in the Property Inspector) of the selected template, and B_OR$REPORT_TITLE is ignored. If you do not select a template, the title uses the default attributes defined by Reports Builder.
Section 3.5.6, "Adding a title to a report"
Section 3.12.5, "Formatting the report title in a template"
Section 2.7.1, "About templates"
Report sectioning enables you to define multiple layouts in the same report, each with a different target audience, output format, page layout, page size, or orientation. You can define up to three report sections, each with a body area and a margin area: the names of the sections are Header, Main, and Trailer. By default, a report is defined in the Main section. In the other sections, you can define different layouts, rather than creating multiple separate reports. For example, a single report can include an executive summary for senior management in one section and also a detailed breakdown for individual managers in another section. If you wish, you can use the margin and body of the Header and Trailer sections to create a Header and Trailer page for your reports.
In the Object Navigator, the report sections are exposed in the Object Navigator under the Paper Layout node as Header Section, Main Section, and Trailer Section.
For detailed information about section-level distribution, see Chapter 36, "Bursting and Distributing a Report". This chapter also covers distribution based on a repeating section, then e-mail those sections based on the distribution.xml.
You can use sectioning and distribution to publish your report output in HTML, and also send a Postscript version to the printer.
You can send an executive summary of the report to a senior management, and also mail detailed breakdowns to individual managers. In this example, a single report with two report sections needs to be created: a portrait-sized summary section and a landscape-sized detail Section. Associating the detail section with a data model group that lists the managers and then altering the destination on each instance of the data model group sends the output to the appropriate managers.
Section 2.8.3, "About report distribution"
Section 3.10.1, "Displaying a section layout view"
Section 3.10.2, "Creating a default layout for a section"
A report can be defined using inches, centimeters, or points. The unit of measurement is independent of the device on which you build the report. As a result, you can design reports that will run on any platform on which Reports Builder runs. You can change a report's unit of measurement in these ways:
A report page can have any length and any width. Because printer pages may be smaller or larger than your paper report's "page," the concept of physical and logical pages is used. A physical page is the size of a page that is output by your printer. A logical page is the size of one page of your report; one logical page may be made up of multiple physical pages.
For each section (header, main, trailer) of a report:
In this example, one logical page is made up of six physical pages. The logical page is three physical pages wide and two physical pages high. Consequently, Horizontal Panels per Page size is 3 and Vertical Panels per Page size is 2. If you wanted the logical page to be two physical pages wide and three physical pages high, you would specify a Horizontal Panels per Page size of 2 and a Vertical Panels per Page size of 3.
For detailed information about using and adding fonts in Oracle Reports, including font configuration files, font aliasing, troubleshooting font issues, and font types, refer to the chapter "Fonts in Oracle Reports" in the Oracle Application Server Reports Services Publishing Reports to the Web manual, available on the Oracle Technology Network Oracle Reports Documentation page (http://otn.oracle.com/docs/products/reports/content.html).
Using the Conditional Formatting and Format Exception dialog boxes, you can specify output formatting attributes (font and/or color) for a selected layout object based on conditions that exist. The conditions that you define are called format exceptions.
You can display the Conditional Formatting dialog box from the Paper Layout view or Paper Design view in any of the following ways:
The Format Exception dialog box displays when you click New or Edit in the Conditional Formatting dialog box, and enables you to quickly and easily specify output formatting attributes for a selected layout object based on defined conditions. After you specify conditions and formatting for the current layout object in the Format Exception dialog box, the entire definition is exported to a PL/SQL format trigger. If a format trigger already exists for the layout object, the definition in the Format Exception dialog box overwrites the existing trigger code when you confirm the Reports Builder prompt.
You can edit the format trigger manually via the PL/SQL Editor; however, if you subsequently modify the definition using the Format Exception dialog box, Reports Builder displays a prompt to overwrite the existing format trigger.
Section 3.9.1.5, "Applying conditional formatting to a layout object"
A nested matrix report is a matrix report in which at least one parent/child relationship appears within the matrix grid.
A nested matrix report has more than two dimensions; therefore, it has multiple dimensions going across and/or down the page. For example, look at the report below (Figure 2-2). Notice that for each year there is a nested list of related departments. Also notice that the list of jobs (the across values) appears only once. Because the job values appear only once, a summary of each category of jobs can be made to line up with the values it summarizes.
For an example, see the example report in Chapter 26, "Building a Nested Matrix Report".
Section 2.3.7, "About matrix objects"
Section 1.3.7, "About matrix reports"
A matrix with group report is a group above report with a separate matrix for each value of the master group. For example, for each year (master) in the report below there is a unique matrix that contains only that year's departments and jobs. This means that a summary of each job category may not line up with the values it summarizes because the position of each job category in the matrix may vary for each year.
A multi-query matrix with group report is similar to a nested matrix report in that it has more than two dimensions. For example, in the following report, notice that for each year there is a nested list of related departments.
The advantage of using multiple queries is that you get a real break, or master/detail relationship, for the nesting groups (e.g., notice that in the multi-query example above, Year 80 shows only record 20; with a single query, Year 80 would show all records whether or not they contain data for Year 80). If you want to suppress detail records that do not contain data for a particular master record, you must use multiple queries.
For a complete example, see the example report in Chapter 27, "Building a Matrix with Group Above Report".
Section 2.3.7, "About matrix objects"
Section 1.3.7, "About matrix reports"
The topics in this section build on the basic concepts discussed in Section 1.2.2, "About Web reports".
Section 1.2.2, "About Web reports"
Oracle Reports supports JavaServer Pages (JSPs) as the underlying technology to enable you to enhance Web pages with information retrieved using Reports Builder. JSP technology is an extension to the Java servlet technology from Sun Microsystems that provides a simple programming vehicle for displaying dynamic content on a Web page. A JSP is an HTML page with embedded Java source code that is executed in the Web server or application server. The HTML provides the page layout that is returned to the Web browser, and the Java provides the business logic.
JSPs keep static page presentation and dynamic content generation separate. Because JSPs cleanly separate dynamic application logic from static HTML content, Web page designers who have limited or no Java programming expertise can modify the appearance of the JSP page without affecting the generation of its content, simply using HTML or XML tags to design and format the dynamically-generated Web page. JSP-specific tags or Java-based scriptlets can be utilized to call other components that generate the dynamic content on the page.
JSPs have the .jsp extension. This extension notifies the Web server that the page should be processed by a JSP container. The JSP container interprets the JSP tags and scriptlets, compiles the JSP into a Java servlet and executes it, which generates the content required, and sends the results back to the browser as an HTML or XML page.
A JSP can be accessed and run from a browser-based client, typically over the Internet or a corporate intranet. Unlike traditional client-server applications, JSP applications:
When a JSP is called for the first time, it is compiled into a Java servlet class and stored in the Web server's memory. Because it is stored in memory, subsequent calls to that page are very fast, thereby avoiding the performance limitations seen with traditional Common Gateway Interface (CGI) programs, which spawn a new process for each HTTP request.
For additional background information about JSP technology, see the JavaSoft web site at http://www.javasoft.com/products/jsp/.
In Oracle Reports, you use JSPs to embed data retrieved using the data model into an existing Web page to create a JSP-based Web report. You can create new JSP reports, or save existing reports as JSP reports. New reports are by default saved as JSP reports. The benefit of saving reports as JSPs is that JSPs are text files that are easy to edit as opposed to, for example, the binary .rdf format. When a report is saved as a JSP file, the data model is embedded using XML tags. The entire report can now be defined using XML tags and saved as an XML file.
Using the Oracle Reports custom JSP tags, you can easily add report blocks and graphs to existing JSP files. These tags can be used as templates to enable you to build and insert your own data-driven Java component into a JSP-based Web report. Not only can you edit the HTML or XML code that encapsulates the report block, but you can also edit the report block in the JSP itself, by modifying, adding or deleting their bodies and attributes.
Not only can you edit the HTML code that encapsulates the report block, but you can also edit the report block in the JSP itself, by modifying, adding or deleting their bodies and attributes.
The Report Editor's Web Source view displays the source code for your Web report, including HTML, XML, and JSP tags.
In prior releases, Oracle Reports introduced Web links that you can add to paper-based reports, which become active when you display your paper report in a Web browser or PDF viewer. For JSP reports, hyperlinks have to be created manually, and if the hyperlinks need to substitute data values, the data values must be provided via the rw:field JSP tag. For example:
<a href="http://server/path/rwservlet?report=department.jsp&p_deptno=<rw:field
id="F_Deptno" src="Deptno"/>"> <rw:field id="F_Deptno" src="Deptno">10</rw:field> </a>
http://otn.oracle.com/products/reports/content.html).
You can preview a JSP-based Web report by clicking the Run Web Layout button in the toolbar, or by choosing Program > Run Web Layout, to run the Web Source. Reports Builder displays Web reports in your default browser.
You do not need to have the Reports Server configured to use this functionality. Reports Builder includes an embedded OC4J (Oracle Container for Java) server in it. If you are using this option to preview a JSP report locally, if your JSP depends on external files, such as images, or if you want to check the generated Java files, it is important to understand what Reports Builder does with the temporary files. Each instance of the Builder has its own OC4J server listening on different port, so you can have multiple Builder sessions at a time. If not specified, the Builder automatically looks for a free port in the default range.
A JSP gets converted into a .java file and compiled into a class file. When the class file is executed, it will return HTML in an .html file. This file and the .java and .class files are all located in the $REPORTS_TEMP/docroot directory. $REPORTS_TEMP can be passed in as a command line parameter to Reports Builder, thus allowing you to override the default location for the docroot directory. The contents of the docroot directory are cleaned up when you exit the Reports Builder.
By default, the Reports Builder document root is the docroot directory under the directory specified by the $REPORTS_TMP environment variable (e.g., c:/temp/docroot). The end user can override this default docroot from the command line (using the WEBSERVER_DOCROOT command line keyword). If your JSP depends on external files, such as images, style sheets, and so on, make sure you copy them into the docroot directory. Better yet, you can specify the WEBSERVER_DOCROOT command line value to be your document root directory.
A JSP gets translated into a .java file and compiled into a .class file. When the class file is executed, it will return HTML in an .html file. This file and the .java and .class files are all located in the docroot directory. The docroot directory structure looks as follows after running emp.jsp (note that we use the default docroot, which is $REPORTS_TMP/docroot):
temp
docroot
3000 working directory for instance of the Builder
default
defaultWebApp temporary JSP working directory temp _pages
_empxxx.class compiled Java class _empxxx.java translated Java file log OC4J log directory global-application.log server.log orion-conf OC4J configuration files directory stderr.log debug log when WEBSERVER_LOG=yes stdout.log 3002 another instance's working directory css template style sheets images template images WEB-INF lib reports_tld.jar web.xml rwerror.jsp template error JSP empxxx.jsp working copy of emp.jsp empxxx.html resulting output
Section 3.7.15.4, "Displaying report output in your Web browser"
Section 3.7.16.3, "Printing a report from your Web browser"
This topic discusses the Web links that you can add to paper-based reports that will become active when you run your report to an HTML file and display it in a Web browser.
In most cases, you can define Web links in an object's Property Inspector. You can specify column and field names in the link value to create dynamic links at runtime. If you require more complex implementation of Web links, such as conditional settings, you must specify the link using PL/SQL and the Reports Builder built-in packaged procedure SRW.SET_ATTR.
Reports output in HTML format can include the following types of Web links:
Additionally, your report can include the following headers and footers that use escapes to add HTML tags to your paper-based report:
This topic discusses the Web links that you can add to paper-based reports that will become active when you run your report to a PDF file and display it in a PDF viewer.
Reports output in PDF format can include the following types of Web links:
In most cases, you can define Web links in an object's Property Inspector. You can specify column and field names in the link value to create dynamic links at runtime. If you require more complex implementation of Web links, such as conditional settings, you must specify the link using PL/SQL.
A hyperlink is an attribute of an object that specifies a hypertext link to either of the following destinations:
You can set the Additional Hyperlink Attributes property to specify additional HTML to be applied to the hyperlink.
Section 3.6.7.1.7, "Creating a hyperlink using the Property Inspector"
Section 3.6.7.2.7, "Creating a hyperlink using PL/SQL"
A hyperlink destination is an attribute of an object that identifies the destination of a hypertext link.
Section 3.6.7.1.8, "Creating a hyperlink destination using the Property Inspector"
Section 3.6.7.2.8, "Creating a hyperlink destination using PL/SQL"
A bookmark is an attribute of an object that specifies a string that is a link to the object.
Section 3.6.7.1.10, "Creating a bookmark using the Property Inspector"
Section 3.6.7.2.10, "Creating a bookmark using PL/SQL"
Section 3.6.7.1.11, "Creating a bookmark on break columns using the Property Inspector"
(PDF output only) An application command line link is an attribute of an object that specifies a command line to be executed when the object is clicked.
An object that is associated with a application command line link cannot also be the source of a Web link (a hyperlink).
Section 3.6.7.1.9, "Creating an application command line link using the Property Inspector"
Section 3.6.7.2.9, "Creating an application command line link using PL/SQL"
Section 3.6.7.1.1, "Creating an HTML document header using the Property Inspector"
Section 3.6.7.2.1, "Creating an HTML document header using PL/SQL"
Section 3.6.7.1.2, "Creating an HTML document footer using the Property Inspector"
Section 3.6.7.2.2, "Creating an HTML document footer using PL/SQL"
Section 3.6.7.1.3, "Creating an HTML page header using the Property Inspector"
Section 3.6.7.2.3, "Creating an HTML page header using PL/SQL"
Section 3.6.7.1.4, "Creating an HTML page footer using the Property Inspector"
Section 3.6.7.2.4, "Creating an HTML page footer using PL/SQL"
Section 3.6.7.1.5, "Creating an HTML Parameter Form header using the Property Inspector"
Section 3.6.7.2.5, "Creating an HTML Parameter Form header using PL/SQL"
Section 3.6.7.1.6, "Creating an HTML Parameter Form footer using the Property Inspector"
Section 3.6.7.2.6, "Creating an HTML Parameter Form footer using PL/SQL"
Style sheets (or cascading style sheets) refer to HTML extensions that provide powerful formatting flexibility. With style sheet support, your HTML documents can include any of the following:
This means that the sophisticated formatting in a report is preserved when you format the report as an HTML document. Without style sheet extensions, your HTML documents display only basic text formats and imported images. With style sheets, images of highly formatted text can be replaced with text objects of equivalent style, color, and font. Text objects can be positioned to overlay image objects. All text is fully searchable, and fewer images have to be downloaded.
To view an HTML document that takes advantage of style sheets, you must display it in a browser that supports style sheets.
The following elements are not supported by HTML style sheet extensions:
Section 3.7.15.4, "Displaying report output in your Web browser"
Section 3.7.16.3, "Printing a report from your Web browser"
The topics in this section build on the basic concepts discussed in Section 1.7, "Data Model Objects".
A summary column performs a computation on another column's data. Using the Report Wizard or Data Wizard, you can create the following summaries: sum, average, count, minimum, maximum,% total. You can also create a summary column manually in the Data Model view, and use the Property Inspector to create the following additional summaries: first, last, standard deviation, variance.
If your report requires a customized computation, for example, one that computes sales tax, create a formula column (see Section 3.8.7, "Creating or editing a formula column").
Section 3.8.8, "Creating a summary column"
A formula column performs a user-defined computation on the data of one or more column(s), including placeholder columns. For example, :ITEMTOT *.07 is a formula that performs a computation on one column, while :SAL + :COMM performs a computation using two columns in a record. You create formulas in PL/SQL using the PL/SQL Editor.
Section 3.8.7, "Creating or editing a formula column"
Section 2.6.8, "About formulas"
A placeholder is a column for which you set the datatype and value in PL/SQL that you define. Placeholder columns are useful when you want to selectively set the value of a column (e.g., each time the nth record is fetched, or each time a record containing a specific value is fetched, etc.). You can set the value of a placeholder column in the following places:
Section 3.8.9, "Creating or editing a placeholder column"
Section 2.6.8, "About formulas"
You can reference user parameters, system parameters and columns as either bind references or lexical references.
Bind references (or bind variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries. Bind references may not be referenced in FROM clauses or in place of reserved words or clauses.
You create a bind reference by typing a colon (:) followed immediately by the column or parameter name. If you do not create a column or parameter before making a bind reference to it in a SELECT statement, Reports Builder will create a parameter for you by default.
Bind references must not be the same name as any reserved SQL keywords. For more information, see the Oracle9i Server SQL Language Reference Manual.
In the following example, the value of DFLTCOMM replaces null values of COMMPLAN in the rows selected.
SELECT CUSTID, NVL(COMMPLAN, :DFLTCOMM) COMMPLANFROM ORD;
The value of CUST is used to select a single customer.
SELECT ORDID, TOTALFROM ORD WHERE CUSTID = :CUST;
All non-aggregate expressions such as NVL(COMMPLAN, :DFLTCOMM) in the SELECT clause must be replicated in the GROUP BY clause.
SELECT NVL(COMMPLAN, :DFLTCOMM) COMMPLAN, SUM(TOTAL) TOTALFROM ORD GROUP BY NVL(COMMPLAN, :DFLTCOMM);
The value of MINTOTAL is used to select customers with a minimum total of orders.
SELECT CUSTID, SUM(TOTAL) TOTALFROM ORD GROUP BY CUSTID HAVING SUM(TOTAL) > :MINTOTAL;
The value of SORT is used to select either SHIPDATE or ORDERDATE as the sort criterion. Note that this is not the same as ORDER BY 1 because :SORT is used as a value rather than to identify the position of an expression in the SELECT list. Note that DECODE is required in this example. You cannot use a bind variable in an ORDER BY clause unless it is with DECODE.
SELECT ORDID, SHIPDATE, ORDERDATE, TOTALFROM ORD ORDER BY DECODE(:SORT, 1, SHIPDATE, 2, ORDERDATE);
References in CONNECT BY and START WITH clauses are used in the same way as they are in the WHERE and HAVING clauses.
procedure double is begin; :my_param := :my_param*2; end;
The value of myparam is multiplied by two and assigned to myparam.
Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH. Use a lexical reference when you want the parameter to substitute multiple values at runtime.
You cannot make lexical references in a PL/SQL statement. You can, however, use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL. Look at the example below.
You create a lexical reference by typing an ampersand (&) followed immediately by the column or parameter name. A default definition is not provided for lexical references. Therefore, you must do the following:
Parent Query:
SELECT DEPTNO FROM EMP
Child Query:
SELECT &PARM_1 COL_1, &PARM2 COL_2
FROM EMP WHERE &PARM_1 = :DEPTNO
Note how the WHERE clause makes a bind reference to DEPTNO, which was selected in the parent query. Also, this example assumes that you have created a link between the queries in the Data Model editor with no columns specified.
SELECT ENAME, SAL FROM EMP &where_clause
If the value of the WHERE_CLAUSE parameter contains a reference to a bwind variable, you must specify the value in the After Form trigger or earlier. You would get an error if you supplied the following value for the parameter in the Before Report trigger:
WHERE SAL = :new_bind
If you supplied this same value in the After Form trigger, the report would run.
SELECT &P_ENAME NAME, &P_EMPNO ENO, &P_JOB ROLEFROM EMP;
P_ENAME, P_EMPNO, and P_JOB can be used to change the columns selected at runtime. For example, you could enter DEPTNO as the value for P_EMPNO on the Runtime Parameter Form. Note that in this case, you should use aliases for your columns. Otherwise, if you change the columns selected at runtime, the column names in the SELECT list will not match the Reports Builder columns and the report will not run.
SELECT ORDID, TOTALFROM &ATABLE;
ATABLE can be used to change the table from which columns are selected at runtime. For example, you could enter ORD for ATABLE at runtime. If you dynamically change the table name in this way, you may also want to use lexical references for the SELECT clause (look at the previous example) in case the column names differ between tables.
SELECT ORDID, TOTALFROM ORD WHERE &CUST;
CUST can be used to restrict records retrieved from ORD. Any form of the WHERE clause can be specified at run-time.
SELECT NVL(COMMPLAN, DFLTCOMM) CPLAN, SUM(TOTAL) TOTALFROM ORD GROUP BY &NEWCOMM;
The value of NEWCOMM can be used to define the GROUP BY clause.
SELECT CUSTID, SUM(TOTAL) TOTALFROM ORD GROUP BY CUSTID HAVING &MINTOTAL;
The value of MINTOTAL could, for example, be used to select customers with a minimum total of orders.
SELECT ORDID, SHIPDATE, ORDERDATE, TOTALFROM ORD ORDER BY &SORT;
The value of SORT can be used to select SHIPDATE, ORDERDATE, ORDID, or any combination as the sort criterion. It could also be used to add on to the query, for example to add a CONNECT BY and START WITH clause.
Parameters in CONNECT BY and START WITH clauses are used in the same way as they are in the WHERE and HAVING clauses.
SELECT &COLSTABLE;
COLSTABLE could be used to change both the SELECT and FROM clauses at runtime. For example, you could enter DNAME ENAME, LOC SAL FROM DEPT for COLSTABLE at runtime.
SELECT * FROM EMP &WHEREORD;
WHEREORD could be used to change both the WHERE and ORDER BY clauses at runtime. For example, you could enter WHERE SAL > 1000 ORDER BY DEPTNO for &WHEREORD at runtime.
SELECT &BREAK_COL C1, MAX(SAL)FROM EMP GROUP BY &BREAK_COL;
BREAK_COL is used to change both the SELECT list and the GROUP BY clause at runtime. The Initial Value of the parameter &BREAK_COL is JOB. At runtime, the user of the report can provide a value for a parameter called GROUP_BY_COLUMN (of Datatype Character).
In the Validation Trigger for GROUP_BY_COLUMN, you call the following PL/SQL procedure and pass it the value of GROUP_BY_COLUMN:
procedure conv_param (in_var IN char) is beginif upper(in_var) in ('DEPTNO','EMPNO','HIREDATE') then:break_col := 'to_char('||in_var||')' ;else:break_col := in_var;end if;end;
This PL/SQL ensures that, if necessary, a TO_CHAR is placed around the break column the user chooses. Notice how in SQL, you make a lexical reference to BREAK_COL. In PL/SQL, you must make a bind reference to BREAK_COL because lexical references are not allowed in PL/SQL.
Bind references are used to replace a single value in SQL or PL/SQL. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING,CONNECT BY, and START WITH clauses of queries. Binds may not be referenced in the FROM clause. An example is:
SELECT ORDID,TOTALFROM ORD WHERE CUSTID = :CUST
Lexical references are placeholders for text that you embed in a SELECT statement, when you want the parameter to substitute multiple values at runtime. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH. You cannot make lexical references in PL/SQL. Before you reference a lexical parameter in a query you must have predefined the parameter and given it an initial value. An example is:
SELECT ORDID, TOTALFROM &ATABLE
A non-linkable query is a detail query that contains column objects that prevent the query from being linked to via a column-to-column link (when you create a column-to-column link, Reports Builder adds a WHERE clause to your query). If you attempt to create such a link, a message dialog box displays, which prompts you to choose whether to create a group-to-group query (using the parent groups), or to cancel the operation. A non-linkable query displays the non-linkable query icon in its title bar.
Instead, you can create a group-to-group link (when you create a group-to-group link, Reports Builder does not add a WHERE clause to your query) between the two queries and add a WHERE clause to the child query's SELECT statement, using a bind variable to reference the parent column. See Section 3.8.6, "Creating a data link".
For example, suppose you want to create a column-to-column link between the ADDRESS.STREET column in your child query and the LOC1 column in your parent query. You can create a group-to-group link, and then modify the child query SQL statement to say:
SELECT * FROM EMP E WHERE E.ADDRESS.STREET = :LOC1
Section 1.7.4, "About data links"
Section 1.7.1, "About queries"
Section 2.3.4.1, "About bind references"
In Reports Builder, data is defined independent of format (layout). Therefore, you should be aware of when to use data links instead of groups.
The layouts of a master/detail report that uses two queries and a data link, and a group report that uses one query and two groups can be identical. Following is an example of a default master/detail report and a group report that query the same data. Notice the difference between the two reports: unlike the group report, the master/detail report displays department 40. This is because the data link in the master/detail report causes an outer-join: the link automatically fetches unrelated data. If you are designing a group report that requires an outer-join, explicitly add it to your SELECT statement via (+).
A master/detail/detail report, as shown in the figure below, is a report that contains three groups of data: for each master group, two unrelated detail groups are displayed. To produce a master/detail/detail report or any variation of it, you must use data links. If you try to produce this report with a control break using a single query and three groups the query will establish a relationship between the two detail groups.
Section 1.7.4, "About data links"
A matrix object merely defines a relationship between two repeating frames: it isn't really owned by anything, nor does it own anything. A matrix object is created only for layouts with a Matrix layout style. A report may have multiple matrices within it, provided that the data model contains the necessary groups. Reports Builder creates one matrix object for each pair of intersecting, perpendicular repeating frames.
The repeating frames are the dimensions of the matrix and the matrix object contains the field that will hold the "filler" or values of the cell group. One of the repeating frames must have the Print Direction property set to Down and the other must have the Print Direction property set to Across in order to form a matrix.
Suppose that you have a group named Group1 that contains a column called C_DEPTNO, which gets its values from the database column DEPTNO. A group called Group2, contains column C_JOB, which gets its values from the database column JOB, and column C_DEPTNO1, which is used for linking to Group1's query. A group called Group3 contains a column called SUMSAL, which is a summary of the database column SAL.
Job Analyst Clerk Manager 10 $1300 $2450 Dept 20 $6000 $1900 $2975 30 $ 950 $2850
In this example:
If you need to build a more complex matrix, you can do so by adding more columns to Group1 and Group2. For example, instead of having Group1 just contain department numbers, it could also contain the locations (LOC) of the departments. The matrix might then look something like this:
Job Loc Dept Analyst Clerk Manager New York 10 $1300 $2450 Dallas 20 $6000 $1900 $2975 Chicago 30 $ 950 $2850
Section 1.3.7, "About matrix reports"
Section 2.1.7, "About nested matrix reports"
Section 2.1.8, "About matrix with group reports"
Section 3.9.1.3, "Creating a matrix object"
Section 3.8.5, "Creating a matrix (cross-product) group"
Section 3.5.3, "Creating a nested matrix report"
The topics in this section build on the basic concepts discussed in Section 1.8, "Layout Objects".
Several important concepts and properties apply to layout objects:
Section 3.5.4, "Creating a default layout for a report"
Section 3.10.2, "Creating a default layout for a section"
Section 2.6.12.2, "About format triggers"
The Properties section of the Reports Builder online help
When you select one of the default layout styles in the Report Wizard, Reports Builder creates the necessary layout objects, based upon the report's data model. For example, if you want to build a mailing label report and have defined an appropriate data model, simply choose the mailing label default style. Reports Builder automatically creates the report's layout objects and displays them in the Layout Model view. You can completely customize any default layout you create. You can cut, copy, paste, move, resize, and edit each layout object that Reports Builder generates for you.
Layout defaulting is governed by the following rules:
Reports Builder defaults report layout according to the following rules:
Exception: For tabular, form-like, group left/above, and matrix reports, the default for CHAR (if you reduce the default width) and LONG fields have a Horizontal Elasticity property setting of Fixed and a Vertical Elasticity property setting of Variable. As a result, all of the field's value will be displayed, instead of truncated, by word-wrapping any data to the next lines.
Section 3.5.4, "Creating a default layout for a report"
Section 3.10.2, "Creating a default layout for a section"
You can add an image to a report by:
By default, images display in fields so that they appear in the printed report, not only in the Previewer.
Oracle Reports 10g (9.0.4) enhances imaging support via the REPORTS_OUTPUTIMAGEFORMAT environment variable and OUTPUTIMAGEFORMAT command line keyword. The image formats supported in the report definition are: JPEG (all types), PNG, BMP, TIFF, GIF, CGM, and OGD. The enhancements in imaging support provide the capability to generate complex graphics-intensive reports with high fidelity image output. Additionally on UNIX, the dependency on a windowing system for displaying images is removed; the PostScript printer driver screenprinter.ppd provides surface resolution for images.
You can include an unlimited number of image objects without running out of local disk space by using non-caching references. A non-caching reference causes objects to be read from the database only when needed while a report is processing. You must be connected to an ORACLE V7.1 or later database to use this feature.
If you reference a URL for an image, the image is displayed when you format your report for HTML output. For other output formats, the URL text displays in the Paper Design view; in the output destination (for example, a file or PDF document), nothing is displayed. It is your responsibility to verify that the URL exists; Reports Builder does not validate the existence of the resource nor the syntax of the protocol. The size of the object that contains the URL defines the size of the image in the HTML output. Any elasticity properties applied to the object are ignored.
Section 3.9.8.6, "Importing a drawing or image"
Section 3.9.8.7, "Selecting an image from the database"
Section 3.9.8.8, "Selecting an image URL from the database"
Section 3.9.8.9, "Linking an image object to a file"
Section 3.9.8.10, "Linking an image object to a URL"
Anchors fasten an edge of one object to an edge of another object, ensuring that they maintain their relative positions. For example, you can anchor boilerplate text to the edge of a variable-sized repeating frame, guaranteeing the boilerplate's distance and position in relation to the repeating frame, no matter how the frame's size might change.
Anchors determine the vertical and horizontal positioning of a child object relative to its parent. The child object may be either outside of or contained within the parent.
Since the size of some layout objects may change when the report runs (and data is actually fetched), you need anchors to define where you want objects to appear relative to one another. An anchor defines the relative position of an object to the object to which it is anchored. Positioning is based on the size of the objects after the data has been fetched rather than on their size in the editor. It should also be noted that the position of the object in the Paper Layout view affects the final position in the report output. Any physical offset in the layout is incorporated into the percentage position specified in the Anchor properties.
There are two types of anchors:
When you anchor a child object to a parent object, the x and y coordinates of the anchor's attachments are important.
If the parent object is located above or below the child object:
If the parent object is located to the right or left of the child object:
If you need to position an object outside a repeating frame or frame, but you want the object to be "owned" by the repeating frame or frame (i.e., to be formatted when its "owner" is formatted), create an anchor that is attached to an object inside the frame or repeating frame.
You can create anchors to be "collapsible." Collapsing anchors help avoid unnecessary empty space in your report. Such empty space can occur when the parent object does not print on the same page as the child object, either because the parent and child can not fit on the same page or because of an assigned Print Condition. A collapsing anchor allows the child object to move into the position that would have been taken by the parent had it printed. The child object will also maintain its relative position as defined by the anchor.
Reports Builder creates implicit anchors at runtime in the body region. The margin algorithm differs slightly.
If the "push path" direction is Down, anchor the pushee object's top 0% to the pusher object's bottom 0%.
If the "push path" direction is Across, anchor the pushee object's left 0% to the pushed object's right 0%.
In the first case, M_Sums is in the "push path" of both B_Text1 and R_Ename. Because M_Sums is equidistant from B_Text1 and R_Ename, though, the normal criteria (shortest distance) for determining implicit anchors does not work in this case. Consequently, the formatting algorithm will randomly create an implicit anchor between M_Sums and either B_Text1 or R_Ename at runtime. To avoid this behavior, you could create an explicit anchor between M_Sums and B_Text1 or R_Ename.
In the second case, B_Text3 is in the "push path" of M_Emp. Since the bottom edges of M_Emp and R_Mgr are virtually in the same position, though, B_Text3 could be implicitly anchored to either M_Emp or R_Mgr. Consequently, the formatting algorithm will randomly create an implicit anchor between B_Text3 and either M_Emp or R_Mgr at runtime. To avoid this behavior, you could create an explicit anchor from B_Text3 to one of the objects or remove the explicit anchor between R_Ename and B_Text2. Removing the explicit anchor would cause R_Mgr to be treated as a descendant of M_Emp and, therefore, the implicit anchor would always be created between B_Text3 and M_Emp.
(Note that Case 2 is most likely to occur in character mode, where it is common to have the edges of objects overlap in the Paper Layout view.)
Reports Builder creates implicit anchors for all Type B objects in the margin region using the Body algorithm. For each Type A object, however, Reports Builder creates an implicit anchor from the top-left corner of the object to the top-left corner of the margin. No Type A object will be implicitly anchored to another Type A object. (This ensures that Type A objects will not be pushed off the page. However, they may be overwritten by another Type A object, if they are found on the same layer.)
Section 3.9.5.1, "Anchoring objects together"
Section 3.9.5.2, "Viewing implicit anchors"
Section 3.9.5.3, "Moving an anchor"
Color and pattern selections are applied to an entire object (e.g., you can apply a color to all the text in the object but not to a segment of the text).
You can change colors and patterns in your report in the following ways:
Additionally, you can set color palette preferences to specify how it is used by a report (see Section 3.2.6, "Setting color palette preferences") and modify the color palette to change the definition of individual colors (see Section 3.9.6.5, "Modifying the color palette").
To change the color palette being used by the current report, you can import a new color palette. You can also export the current color palette for use by other reports. (See Section 3.9.6.6, "Importing or exporting a color palette".)
Section 3.9.6.2, "Changing colors"
Section 3.9.6.3, "Changing patterns"
Section 3.9.6.4, "Changing colors and patterns using PL/SQL"
Section 3.9.4.2, "Changing object border attributes"
Topics "Oracle CDE1 color palette", "Default color palette", "Grayscale color palette", and "Pattern color palette" in the Reference > Color and Pattern Palettes section of the Reports Builder online help
Topic "SRW built-in package" in the Reference > PL/SQL Reference > Built-in Packages section of the Reports Builder online help
Topic "Template properties" in the Properties section of the Reports Builder online help
You can resize queries, groups, frames, repeating frames, fields, matrix objects, and boilerplate objects. You cannot resize anchors. However, an anchor is automatically resized if you move one of the objects it anchors.
Section 3.9.12.1, "Resizing objects"
Section 3.9.12.2, "Making multiple objects the same size"
Section 3.9.11.3, "Adjusting parent borders automatically"
In the Paper Layout view, objects must be on a layer above the objects that enclose them. For example, the fields that belong to a repeating frame must be at least one layer above the repeating frame in the Paper Layout view. If not, then they are not considered to be enclosed by the repeating frame any longer and will cause a frequency error at runtime. When you move or group objects in the Paper Layout view, it is possible to change the layering such that you will get frequency errors when you run the report. To avoid this problem, you should take advantage of Confine or Flex mode when moving objects in the Paper Layout view.
Section 3.9.4.3, "Changing the current mode (Confine or Flex)"
Section 3.9.11.7, "Changing object layering"
Section 3.9.11.2, "Moving an object outside its parent"
Section 3.9.11.1, "Moving multiple objects"
Section 3.9.11.6, "Aligning objects"
The topic in this section builds on the basic concepts discussed in Section 1.9, "Parameter Form Objects".
Parameter Form HTML extensions enable you to enhance your Runtime Parameter Form with HTML tagged text and JavaScript when your paper reports are run via the Web. To enhance your Paper Parameter Form for displaying on the Web, you can:
You can access the Parameter Form Builder from the Object Navigator or by choosing Tools > Parameter Form Builder.
Section 1.2.2, "About Web reports"
Section 1.9.4, "About Parameter Forms for Web reports"
Section 2.2.3, "About Web links for HTML output"
The topics in this section discuss the use of PL/SQL in 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 drop-down list to recompile them.
Section 3.13.3.1, "Creating a local program unit"
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).
Section 3.13.3.2, "Creating a stored program unit"
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.
Section 3.13.2.4, "Inserting syntax into the 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 3.13.5.1, "Creating an external PL/SQL library"). |
For a detailed example of using PL/SQL in a report, see Chapter 37, "Building a PL/SQL Report".
Suppose that you have a report with the following groups and columns:
Groups Columns Summary-----------------------------------------RGN REGIONRGNSUMSAL SUM(DEPTSUMSAL)COSTOFLIVINGDEPT DNAMEDEPTNODEPTSUMSAL SUM(EMP.SAL)JOB JOBHEADCOUNT COUNT(EMP.EMPNO)EMP ENAMEEMPNOSALCOMM
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
Section 3.13.3.1, "Creating a local program unit"
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.w
Section 3.13.3.2, "Creating a stored program unit"
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.
Section 3.13.5.1, "Creating an external PL/SQL 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.
Section 3.13.5.5, "Attaching a PL/SQL library"
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 (i.e., the PL/SQL Formula property).
A column of datatype Number can only have a formula that returns a value of datatype NUMBER. A column of Datatype Date can only have a formula that returns a value of datatype DATE. A column of Datatype Character can only have a formula that returns a value of datatype CHARACTER, VARCHAR, or VARCHAR2.
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"
Section 3.13.4.3, "Creating or editing a formula column"
Section 3.13.4.4, "Creating a placeholder column"
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.
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 use a Filter of Last or Conditional, 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 effect summaries in other groups which depend on this query. For example, if you set Maximum Rows to Fetch to 8 any summaries based on that query will only use the 8 records retrieved.
Group filters cannot be added to groups if Filter Type is First or Last.
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;
Section 3.13.4.2, "Creating or editing a group filter"
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.
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.
/* 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;
/* 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;
empCur rcPackage.empCurType; BEGIN OPEN empCur FOR SELECT * FROM emp; RETURN empCur; END; /* Note, rcPackage is a local program unit defined as: */ PACKAGE rcPackage IS TYPE empCurType IS REF CURSOR RETURN emp%ROWTYPE; 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;
/* 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 (e.g., ** 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;
Section 3.8.1.9, "Creating a query: Ref Cursor Query tool"
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 Reports Builder built-in package (SRW), and numerous Tools built-in packages, as described below.
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, e.g., from SQL*Plus.
Constructs found in a package are commonly referred to as "packaged"; i.e., packaged functions, packaged procedures, and packaged exceptions.
Topic "SRW built-in package" in the Reference section of the Reports Builder 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 (e.g., TEXT_IO.PUT_LINE).
The Tools built-in packages are:
Provides Dynamic Data Exchange support within 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 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 (e.g. 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:
Allows you to interact with Oracle environment variables by retrieving their values for use in subprograms.
Allows 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 (e.g., 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.
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 Reports Builder 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, use Report Triggers in the Object Navigator. Report triggers must explicitly return TRUE or FALSE. Reports Builder has five global report triggers (you cannot create new global report triggers):
For information about these triggers, see the Reference section of the Reports Builder online help.
The Before Report trigger fires before the report is executed but after the queries are parsed.
You can think of order in this way:
Consistency is guaranteed if you use DML, DDL in (or before) the After Form Trigger. However, consistency is not guaranteed in the Before Report trigger, since Reports Builder may have to start work on data cursors before that trigger based on the definition of the report. Before the Before Report trigger, Reports Builder describes the tables involved and opens cursors. Any change to the tables after that will not be seen by the report.
Section 3.13.3.5, "Creating a report trigger"
Section 3.13.3.6, "Deleting a report trigger"
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 Reports Builder attempts to format the object. Consider the case where Reports Builder starts to format the object at the bottom of a page. If the object does not fit on the page, 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 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:
See the topic "Format trigger" in the Reference section of the Reports Builder online help.
Section 3.13.4.1, "Creating or editing a format trigger"
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 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 message |
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 Reports Builder online help.
Section 3.11.4, "Validating a parameter value at runtime"
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:
For additional information about how triggers are used in applications, see the Oracle9i Application Developer's Guide. See the Oracle9i Concepts Manual for more information about the different types of triggers.
Section 3.13.3.7, "Creating a database trigger"
The topics in this section discuss the use of templates in Reports Builder.
Templates define common characteristics and objects that you want to apply to multiple paper-based reports. For example, you can define a template that includes the company logo and sets fonts and colors for selected areas of a report.
When you use the Report Wizard to create a paper-based report, you use the Templates page of the wizard to apply a template (.tdf file) to the report. The Templates page lists the default templates, as well as any templates that you have created.
When you choose a template, objects in the margin area of a template are imported into the same locations in the current report section, overwriting any existing objects. The characteristics (formatting, fonts, colors, etc.) of objects in the body area of the template are applied to objects in the body area of the current report section. Any template properties, parameters, report triggers, program units, and attached libraries you have defined are also applied. You can apply different templates to each section of the report. However, if you are applying one of the default templates, you cannot combine two report blocks that use different default templates in a single report. All of your report blocks in any one report must use the same default template.
If you later apply another template to a report, the existing template objects will be deleted in the current report section.
Section 3.12, "Define a Template"
In the Layout Body area of a template, you can define Default and Override attributes under the following Object Navigator nodes:
The Default node in the Object Navigator defines the default visual attributes (formatting, fonts, colors, etc.) for all report styles. If you want to define attributes for individual report styles, you do so under the Override node. When you apply a template to a report, all Default attributes are applied to the report, except for attributes that are localized under the Override node.
Under the Override node in the Object Navigator, you can define attributes for individual report styles. Each report style contains one or more sections that map to groups in the report:
Single-section report styles: Tabular, Form, Mailing Label, Form Letter
Multiple section report styles: Group Left, Group Above, Matrix, Matrix with Group
For the report styles that support multiple groups, you can create additional sections as needed. Sections are mapped to groups as follows:
Same number of groups as sections: one-to-one mapping (the first section is mapped to the first group, the second section to the second group, and so on).
More groups than sections: one-to-one mapping until the next-to-last section. Then, all subsequent groups are mapped to the next-to-last section, and the last group is mapped to the last section. If only one section exists, all groups are mapped to it.
More sections than groups: one-to-one mapping until the next-to-last group. Then, the last group is mapped to the last section.
Section 3.12.2, "Defining default template attributes"
Section 3.12.3, "Defining override template attributes"
When you apply a template to a report, all of the following objects, properties, and attributes from the template are applied to the current report section:
In addition, all of the layout objects in the margin of the template are copied into the same location in the current report section.
Section 3.12.4, "Applying a template to a report"
In templates, Sections, Frames, Fields, Labels, Headings, and Summaries properties all may inherit their values.
The Default properties inherit the values preset by Reports Builder. When a property is set to its default Reports Builder value, the icon next to it in the Property Inspector is a small circle. Default properties become localized when you change their values, or select them and click the Localize Property button in the toolbar. When a property is localized, the icon next to it changes to a square. To return the properties to their inherited values, select the properties and click the Inherit Property button in the toolbar.
The properties of Override Sections inherit their values from the Default properties. When a property inherits from a Default property, the icon next to it in the Property Inspector is an arrow. Properties in the Override Sections become localized when you change their values, or select them and click the Localize Property button in the toolbar. When a property is localized, the icon next to it changes to an arrow with a red cross through it. To return the values of properties in the Override Sections to their inherited values, select the properties and click the Inherit Property button in the toolbar.
The Template Editor is a work area in which you can define objects and formatting properties for your templates. It is similar to the Paper Layout view of the Report Editor. You can create, delete, and modify objects (e.g., page numbers, text, and graphics) in the margin area. You cannot create and delete objects in the body area, but you can modify the properties of body objects in the Property Inspector (Tools > Property Inspector).
The Report Style drop-down list allows you to view the layout for a selected report style. To define default settings for all report styles, you can choose Default from the Report Style drop-down list. To make changes for an individual report style, you can select that report style from the Report Style drop-down list to specify settings that override the default.
You can access the Template Editor in the following ways:
When creating a new template:
When displaying an existing template:
The topics in this section discuss the various output formats and capabilities in Reports Builder.
In prior releases, Oracle Reports formatted the sections of a report in sequential order: Header section, followed by Main section, followed by Trailer section. This release introduces the capability to change the order in which the three sections of a report are formatted.
The format order can be set in either of the following ways:
This feature is useful for formatting any report section first to retrieve information that is known only at the time of formatting, such as page numbers, then using that information in the formatting of a previous section.
For example, to create a table of contents (TOC) for a report, you can format the Main section first and use report triggers to build a table containing the TOC entries. When the first element for the TOC is formatted, a trigger fires and creates a row in the TOC table containing the TOC entry and the page number. After the Main section has completed formatting, the format order setting can define that the Header section is formatted next. The Header section can contain a report block based on the TOC table. After formatting, you can output your report with a TOC (the Header section), followed by the report body (the Main section), followed by the Trailer section.
For the steps to create a TOC for a report, see the example reports in Chapter 34, "Building a Paper Report with a Simple Table of Contents and Index" and Chapter 35, "Building a Paper Report with a Multilevel Table of Contents".
A note about page numbering:
The page numbering of a report follows the format order. For example, in a report with a Header section of 2 pages, a Main section of 8 pages, and a Trailer section of 3 pages, with Format Order set to Main-Trailer-Header, the page numbering will be as follows in the report output: 12, 13 (Header pages, which were formatted last), 1, 2, 3, 4, 5, 6, 7, 8, (Main pages, which were formatted first) 9, 10, 11(Trailer pages, which were formatted second).
If you do not need to examine report output in the Previewer (e.g., you may have to generate large volumes of output from a fully-tested report or run several reports in succession), you can run your report in batch using rwrun. This leaves you free to pursue other tasks while your reports are running.
You can run reports in batch mode from the command line, or use a command file to specify arguments. A command file can save you a great deal of typing if you wish to run several reports using the same set of arguments.
You can also use the Reports Server to batch process reports by specifying BACKGROUND=YES on the command line (valid for rwclient, rwcgi, or rwservlet) to run reports asynchronously (the client sends the call to the server, then continues with other processes without waiting for the report job to complete; if the client process is killed, the job is canceled).
Section 3.7.2, "Running a report from the command line"
Section 3.7.3, "Running a report using a command file"
The Reference > Command Line section of the Reports Builder online help (for information about BATCH and BACKGROUND)
Report distribution enables you to design a report that can generate multiple output formats and be distributed to multiple destinations from a single run of the report. You can create distributions for an entire report, and for individual sections of the report. For example, in a single run of a report, you can generate HTML output, send a PostScript version to the printer, and also e-mail any or all sections of the report to a distribution list.
You can define the distribution for a report in any of the following ways:
For detailed information about when you'd use the report-level vs. section-level distribution, see Chapter 36, "Bursting and Distributing a Report". This chapter also covers distribution based on a repeating section, then e-mail those sections based on the distribution.xml.
To distribute a report, you first define the distribution, then enable the distribution, as described in Section 3.7.11, "Distributing a report to multiple destinations".
As an alternative to defining the distribution for a report or report section in the Distribution dialog box, you can also create a DST file and specify its name on the command line via the DESTINATION keyword to distribute the report.
If a DST file is specified on the command line, the distribution that it defines overrides the distribution defined using the Distribution dialog box.
|
Note: If you trace report distribution to identify distribution errors (see Section 3.14.19, "Tracing report distribution"), the trace file format is very similar to the DST file, so you can cut and paste to generate a DST file from the trace file. |
The format of each line of a DST file is as follows:
dist_ID:output_def
where
dist_ID is an identifier for a distribution destination.
output_def is a series of rwrun or rwclient command line keywords that specify the distribution definition. In addition, the following parameter is valid:
LEVEL specifies the scope of the distribution.
Values for LEVEL
REPORT means that the distribution applies to the entire report.
Header_Section means that the distribution applies to the header section only.
Main_Section means that the distribution applies to the main (body) section only.
Trailer_Section means that the distribution applies to the trailer section only.
Default
REPORT
The definition in this example sends report output to an HTML file, 3 copies of the main section to a printer, and the header section to a PDF file.
;dst file (specified via the DESTINATION keyword on the command line) DEST1: DESNAME=testdst1.HTM DESTYPE=file DESFORMAT=HTML COPIES=1 LEVEL=Report DEST2: DESNAME=\\luna\2op813 DESTYPE=printer DESFORMAT=BITMAP COPIES=3 LEVEL=Main_Section DEST3: DESNAME=SECTION1.pdf DESTYPE=file DESFORMAT=pdf COPIES=1 LEVEL=Header_Section
Section 2.1.2, "About report sectioning and sections"
Pluggable destinations can be used to distribute any content that an engine (not only the Oracle Reports engine) has created in the Reports Server's cache. Oracle Reports provides several out-of-the-box destinations:
You can also define access to your own custom destination by using the Oracle Reports Java APIs to implement a new destination component in the Reports Server. You can choose for your jobs to use an out-of-the-box destination or your customized destination to determine the destination for the output in the cache.
For information and steps to implement and register a destination class, then use the destination with Oracle Reports, see the FTP Destination tutorial available via the Oracle Reports Plugin Exchange on OTN (http://otn.oracle.com/products/reports/pluginxchange/content.html).
"Configuring Destinations for OracleAS Reports Services" in the Oracle Application Server Reports Services Publishing Reports to the Web manual, available on the Oracle Technology Network Oracle Reports Documentation page (http://otn.oracle.com/docs/products/reports/content.html).
Event-driven publishing enables you to set up a report to execute when a certain action has been performed. For example, when an employee submits an expense report, new data is being inserted into the database. When this insert event (e.g., a database trigger or an Advanced Queuing (AQ) message) occurs, a report is sent to the employee's manager via their portal page or e-mail notifying them to approve/reject this expense report.
For detailed information, refer to the chapter "Event-Driven Publishing" in the Oracle Application Server Reports Services Publishing Reports to the Web manual, available on the Oracle Technology Network Oracle Reports Documentation page (http://otn.oracle.com/docs/products/reports/content.html).
Using the Before Report, Between Pages, or format triggers, you can switch to different printer trays as your report formats. This allows you to easily print pages of the same report on different sheets of paper (e.g., letterhead, forms, colored).
You can determine the names of the printer trays defined for your printer in the Page Setup dialog box, then use SRW.SET_PRINTER_TRAY to set the printer tray as desired.
Section 3.7.16.5, "Switching the printer tray"
Oracle Reports uses XML (Extensible Markup Language) in the following ways:
XML is a form of encoding text formats that can be read by many different applications. The XML tags can be used to output information or as a basis for building a pluggable data source to exchange electronic data with a third-party application (EDI).
You may change the XML properties that control XML output for your report at three levels: report, group, and column. Note that in any Reports Builder-generated XML file, your output mimics the data model, structured by groups and columns. For information on how to view your changes in XML output, see Section 3.7.7, "Generating XML output".
For detailed information about using XML for report distribution and customizing reports through XML, see the chapters "Creating Advanced Distributions" and "Customizing Reports with XML" in the Oracle Application Server Reports Services Publishing Reports to the Web manual, available on the Oracle Technology Network Oracle Reports Documentation page (http://otn.oracle.com/docs/products/reports/content.html).
Section 3.7.7, "Generating XML output"
Section 3.7.1, "Running and dispatching a report from the user interface"
The XML PDS section of the Reports Builder online help.
Reports Builder can generate report output to Hypertext Markup Language (HTML) and Hypertext Markup Language with a Cascading Style Sheet (HTMLCSS) files, containing the formatted data and all objects. HTML is a form of encoding text formats that can be read by many different Web page developing software packages, such as Microsoft Front Page, and Web browsers. You can use the software's editing and graphics features to modify and enhance your report output. When you generate your report output to an HTML or HTMLCSS file, you can distribute the output to any HTML destination, including e-mail, printer, OracleAS Portal, and Web browser.
desnameb.htm. The report output filename is desnamed.htm.
desname0.gif... desname17.gif).
Table 2-1 Report to HTML font sizes
| Report font size | HTML font size |
|---|---|
|
less than 8 |
1 |
|
8 through 9 |
2 |
|
10 through 12 |
3 |
|
13 through 15 |
4 |
|
16 through 20 |
5 |
|
21 through 30 |
6 |
|
more than 30 |
7 |
Section 1.2.2, "About Web reports"
Section 2.2.3, "About Web links for HTML output"
Section 3.7.5, "Generating HTML or HTMLCSS output"
HTML page streaming enables you to display individual pages of your HTML/HTMLCSS report output in your Web browser, without having to download the entire report. From the first page of the report, you can navigate to any page in the rest of the report. When you click a bookmark or hyperlink with a destination:
You can specify the navigation controls script for a report in either of the following ways:
With HTML page streaming, each page is formatted as a separate HTML document. If your HTML file is named myreport.htm and there are no bookmarks, the new files are named as follows:
To specify HTML to be displayed on only the first (header) or last (footer) pages of your report, set the Before Report or After Report properties or use the SRW.SET_BEFORE_REPORT_HTML or SRW.SET_AFTER_REPORT_HTML PL/SQL procedures. To specify global HTML to apply to the entire report, such as background colors or images, set the Before Page properties or SRW.SET_BEFORE_PAGE_HTML PL/SQL procedure. The Reports Builder-generated HTML logo appears only on the last page of your report.
To enable page streaming when you format your report as HTML or HTMLCSS output, you must specify PAGESTREAM=YES on the command line. This option can not be set via the Reports Builder user interface.
Section 3.7.15.5, "Displaying individual pages of HTML report output"
Section 3.6.7.2.11, "Adding navigation controls for HTML page-streamed output using PL/SQL"
Reports Builder can generate report output to PDF files, containing the formatted data and all objects. When you generate your report output to a PDF file, you can distribute the output to any PDF destination, including e-mail, printer, OracleAS Portal, and Web browser.
For detailed information about PDF enhancements and capabilities in Oracle Reports, which include compression, font aliasing, font subsetting, font embedding and accessibility tags, refer to the chapter "PDF in Oracle Reports" in the Oracle Application Server Reports Services Publishing Reports to the Web manual, available on the Oracle Technology Network Oracle Reports Documentation page (http://otn.oracle.com/docs/products/reports/content.html).
Section 1.2.2, "About Web reports"
Section 2.2.4, "About Web links for PDF output"
Section 3.7.6, "Generating PDF output"
Reports Builder can generate report output to Rich Text Format (RTF) files, containing the formatted data and all objects. RTF can be read by many different word processing software packages, such as Microsoft Word. You can use the software's editing and graphics features to modify and enhance your report output. When you generate your report output to an RTF file, you can distribute the output to any RTF destination, including e-mail, printer, OracleAS Portal, and Web browser.
Section 3.7.8, "Generating RTF output"
Section 3.7.1, "Running and dispatching a report from the user interface"
Reports Builder can generate report output that includes a delimiter to delimited files (e.g., files that contain comma-separated or tab-separated data), which are easily imported into spreadsheets or for use with word processors.
Oracle Reports provides two choices for generating delimited output:
You can specify a delimiter (a character or string of characters) to separate the data (boilerplate or field objects) in your report output in either of the following ways:
When you generate delimited output, you can further distinguish the cells by using a cell wrapper. A cell wrapper can consist of any series of characters, such as a comma or parentheses.
For example, if the data in your report output include the same character as the delimiter (e.g. a comma), you can use the parentheses cell wrapper to distinguish each cell:
(1,000,000),(3,6000),(543),(2,003,500)...
You can run predefined macros on report output generated in delimited (.csv) format when opening it using Microsoft Excel. This is similar to generating an Excel file on a predefined template with embedded macros to manipulate the worksheet. This is useful if you intend to use Excel to open a .csv file generated by Oracle Reports, and further manipulate it on Windows clients.
To create and load macros when opening a .csv file using Microsoft Excel:
personal.xls, in the root_directory\operating_system\Profiles\user_name\Application Data\Microsoft\Excel\Xlstart or root_directory\Program Files\Microsoft Office\Office\XLStart directory.
personal.xls is opened by default whenever you use Excel, allowing you to use the macros you created and saved.
When you generate delimited output, the data displays according to the positions of the objects in the Paper Layout view.
If the text file contains a field labeled "ID" (in uppercase) as the first field, you will be unable to open the file in Microsoft Excel. The following delimited output causes an error in Excel:
ID, name, title, dept, etc.
If you want to generate delimited output that contains an ID field, try changing the database column name to lowercase, i.e., id, or re-arranging the order of the fields.
Section 3.7.9, "Generating delimited output"
Section 3.7.1, "Running and dispatching a report from the user interface"
Section 1.2.2, "About Web reports"
Reports Builder can generate report output to text files, containing the formatted data and all objects. When you generate your report output to text, and the running mode is character (MODE=CHARACTER, or MODE system parameter Initial Value property set to Character), the result is pure text output, which can be read by many different applications. If the running mode is bitmap (MODE=BITMAP, or MODE system parameter Initial Value property set to Bitmap), the result is PostScript output, which can be read and rendered only by PostScript-compatible applications (such as a PostScript printer).
Section 3.7.10, "Generating text output"
To create a character-mode report, you first create a bit-mapped report, then convert that report to an ASCII (character-mode) report. The process will create a new character-mode version of your bit-mapped report; the original bit-mapped report remains unchanged.
After conversion, many of your fields and text objects may need to be resized. Also, graphical objects such as images and drawings will not be included in your character-mode report. The following lists summarize what is supported in each output format:
Section 3.5.9, "Creating an ASCII (character-mode) report"
Section 3.2.5, "Setting properties for an ASCII (character-mode) report"
Section 3.7.1, "Running and dispatching a report from the user interface"
Oracle Reports allows you to access any data source. See Section 3.15.1, "Accessing non-Oracle data sources".
The topics in this section discuss information related to accessing other data sources in Reports Builder.
The Pluggable Data Sources section of the Reports Builder online help, including the topics:
Database roles provide a way for end users to run reports that query tables to which they do not have access privileges. For example, a report may query a table that includes sensitive information such as salary data, even though the final report does not display this data.
Database roles are checked in the runtime environment only. If a table requires special access privileges, end users without those privileges cannot run a report that retrieves data from that table. However, if a database role is defined for the report, end users with privileges for that role can run the report using Reports Runtime (rwrun).
If you try to open a report in Reports Builder for which a database role has been defined, you will be prompted for the role password. Typically, only the report designer and DBA have this information.
Section 3.16.1, "Setting a database role"
Oracle Net Services is Oracle's remote data access software that enables both client-server and server-server communications across any network. It supports distributed processing and distributed database capability. Oracle Net Services runs over and interconnects many communication protocols. Oracle Net Services is backwardly compatible with Net8 and SQL*Net.
In prior releases, user exits provided a way to pass control from Reports Builder to a program you have written, which performs some function, and then returns control to Reports Builder. You could write ORACLE Precompiler user exits, OCI (ORACLE Call Interface) user exits, or non-ORACLE user exits to perform tasks such as complex data manipulation, passing data to Reports Builder from operating system text files, manipulating LONG RAW data, supporting PL/SQL blocks, or controlling real time devices, such as a printer or a robot.
Now, you can call Java methods using the ORA_JAVA built-in package and the Java Importer. This reduces the need to have user exits in a report and allows for a more open and portable deployment. You may also use the
ORA_FFI built-in package, which provides a foreign function interface for invoking C functions in a dynamic library. With the availability of these new built-in packages, the use of user exits is being deprecated in Oracle Reports, though makefiles will still be supplied to permit you to continue to work with existing user exits.
In prior releases, the Oracle Call Interface (OCI) provided a set of standard procedures that you could call in your 3GL programs to call Oracle Reports executables. These procedures (written in C) were shipped with the Reports Builder, Reports Runtime, and Reports Converter executables. For example, to run a Reports Builder report from a Pro*FORTRAN program, you could add a RWCRRB procedure call to your program to run the report using the Reports Runtime executable.
Now, the OCI is obsolete. Instead, use the rwclient.exe command line interface or the JSP tag library.
The topics in this section discuss debugging reports in Reports Builder.
Debugging an application is an iterative process in which application errors are identified and corrected. In general, quickly identifying and locating failing code is essential to successfully debugging your application.
Section 3.14.1, "Debugging a report"
Section 3.14.2, "Running a report in debug mode"
The PL/SQL Interpreter is your debugging workspace, where you can display source code, create debug actions, run program units, and execute Interpreter commands, PL/SQL, and SQL statements.
By default, two panes are always open in the PL/SQL Interpreter: Source pane and Interpreter pane.
Debugging features include the following:
The PL/SQL Interpreter's Source pane displays a read-only copy of the program unit currently selected in the Object Navigator pane.
The numbers along the left hand margin correspond to the line numbers of the displayed program unit.
In addition, the symbols described below may appear in the margin.
The following commands are available when using the PL/SQL Interpreter:
The PL/SQL Interpreter can be invoked from report code (triggers, user-named program units, libraries, etc.) by creating debug actions in the code. These are instructions which that the execution of PL/SQL program units so they can be monitored.
Each debug action you create is automatically assigned a unique numeric ID. While debugging, you can refer to this ID to browse, display, or modify a specific debug action via Reports Builder debug commands.
You can display detailed information about one or more debug actions, including its ID, source location, and whether or not it is enabled. You can temporarily disable specific debug actions and then re-enable them later if necessary.
There are two types of debug actions: breakpoints and debug triggers.
Breakpoints suspend execution at a specific source line of a program unit, passing control to the PL/SQL Interpreter.
Create breakpoints to identify specific debugging regions. For example, create a breakpoint at lines 10 and 20 to debug the code within this region.
With breakpoints, suspension occurs just before reaching the line on which the breakpoint is specified. At this point, use the PL/SQL Interpreter's features to inspect and/or modify program state. Once satisfied, resume execution with the GO or STEP commands, or abort execution using the RESET command.
Debug Triggers are a general form of debug action that associate a block of PL/SQL code with a specific source line within a program unit. When a debug trigger is encountered, Reports Builder executes the debug trigger code.
Create a debug trigger to execute a block of PL/SQL code provided at debug time:
Debug triggers are especially useful as conditional breakpoints. You can raise the exception DEBUG.BREAK from within a trigger. For example, the debug trigger shown below establishes a conditional breakpoint on line 10 of my_proc, which will be reached only if the local NUMBER variable my_sal exceeds 5000:
PL/SQL>line 10 is +> IF Debug.Getn('my_sal') > 5000 THEN +> Raise Debug.Suspend; +> END IF;
You can create debug actions (breakpoints and debug triggers) in the PL/SQL Interpreter in the following ways:
When you create a debug action, attach the breakpoint or debug trigger to a program unit source line that is "executable." A source line is considered executable if it contains one or more statements for which the PL/SQL compiler generates code. For example, source lines containing assignment statements and procedure calls are executable, while source lines containing comments, blank lines, declarations, or the NULL statement are not executable.
Section 3.14.3, "Setting a breakpoint"
Section 3.14.4, "Setting a debug trigger"
The current execution location specifies the next PL/SQL source line to be executed. It corresponds to what is commonly referred to as the program counter, or PC.
When control passes to the PL/SQL Interpreter while running a program (e.g., when a breakpoint is encountered or following a step operation), the Source pane in the PL/SQL Interpreter automatically displays the source line associated with the current execution location.
Use the LIST command in the Interpreter pane to manually display the current execution location.
For example, entering:
.LIST PC
will list the current execution location in the Source pane.
The current scope location dictates where the PL/SQL Interpreter looks for local variables and parameters. It corresponds to the current execution location of one of the PL/SQL subprograms on the stack.
Each time a program unit's execution is interrupted (e.g., by a debug action), the scope location is initialized to the execution location of the subprogram at the bottom of the stack.
Once execution has been interrupted, you can change the current scope location to another frame on the stack. This enables you to view local variables in another subprogram in the call chain.
Section 3.14.14, "Displaying the current scope location"
When a debug action interrupts program execution, the PL/SQL Interpreter takes control and establishes what is known as a debug level. At a debug level, you can enter commands and PL/SQL statements to inspect and modify the state of the interrupted program unit as well as resume execution.
Since any PL/SQL code interactively entered at a debug level may itself be interrupted (for example, by encountering another breakpoint), it is possible for debug levels to nest. To facilitate distinguishing one debug level from another, the levels are numbered. The most deeply nested level is assigned the highest number. Numbering starts at zero with the outermost level.
The 0th or outermost level is commonly referred to as top level. Top level has no associated program state since it is the outermost level at which program units are originally invoked. When code invoked from top level is interrupted, debug level 1 is established. Similarly, interrupting code invoked from debug level 1 establishes debug level 2, and so on.
The PL/SQL Interpreter command prompt reflects the current debug level. When the PL/SQL Interpreter enters levels below top level, the prompt includes a prefix containing the current debug level number. For example, the PL/SQL Interpreter command prompt at debug level 1 appears as:
(debug 1)PL/SQL>
At runtime, you can modify and compile any program unit, menu item command, or trigger that is not on the current stack.
Although runtime code modification is not communicated back to Reports Builder, you can interactively test possible fixes, before returning to implement the eventual fix.
|
 Copyright © 2002, 2003 Oracle Corporation. All Rights Reserved. |
|