2
Advanced Concepts

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.

Examples

Example 1: SELECT clause

SELECT &P_ENAME NAME, &P_EMPNO ENO, &P_JOB ROLE

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

Example 2: FROM clause

SELECT ORDID, TOTAL

FROM &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.

Example 3: WHERE clause

SELECT ORDID, TOTAL

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

Example 4: GROUP BY clause

SELECT NVL(COMMPLAN, DFLTCOMM) CPLAN, SUM(TOTAL) TOTAL

FROM ORD
GROUP BY &NEWCOMM;

The value of NEWCOMM can be used to define the GROUP BY clause.

Example 5: HAVING clause

SELECT CUSTID, SUM(TOTAL) TOTAL

FROM ORD
GROUP BY CUSTID HAVING &MINTOTAL;

The value of MINTOTAL could, for example, be used to select customers with a minimum total of orders.

Example 6: ORDER BY clause

SELECT ORDID, SHIPDATE, ORDERDATE, TOTAL

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

Example 7: CONNECT BY and START WITH clauses

Parameters in CONNECT BY and START WITH clauses are used in the same way as they are in the WHERE and HAVING clauses.

Example 8: Multiple 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.

Example 9: PL/SQL and SQL 

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
begin

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

2.3.4.3 Differences between bind and lexical references

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,TOTAL 

FROM 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, TOTAL

FROM &ATABLE

2.3.5 About non-linkable queries

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

See also

Section 1.7.4, "About data links"

Section 1.7.1, "About queries"

Section 2.3.4.1, "About bind references"

2.3.6 About links versus groups

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. Below 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 (+).

Figure 2-3 Default master/detail and group report that query same data

Text description of the illustration a_links_vs_breaks1.gif

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.

Figure 2-4 Master/detail/detail report

Text description of the illustration a_links_vs_breaks2.gif

See also

Section 1.7.2, "About groups"

Section 1.7.4, "About data links"

2.3.7 About cursor variables

Like a cursor, a cursor variable points to a result set from a multi-row query. But, cursors differ from cursor variables the way constants differ from variables. Whereas a cursor is static, a cursor variable is dynamic because it is not tied to a specific query. You can open a cursor variable for any type-compatible query. This gives you more flexibility.

2.3.8 About matrix objects

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.

Restrictions

• The down repeating frames must be below the across repeating frames in a matrix.

• A matrix object must always be on top of the repeating frames that form it (i.e., it must be one or more layers above its horizontal and vertical repeating frames). Reports Builder prevents you from moving the matrix below its horizontal and vertical repeating frames.

• Moving a matrix also causes its two repeating frames to move.

• A matrix object cannot be anchored to another object and other objects cannot be anchored to it (i.e., a matrix object cannot be the parent or child object for an anchor).

• To copy a matrix, you must select the matrix and its two repeating frames. If you select the matrix object by itself, nothing will be copied to the paste buffer. If you select the matrix and one of the repeating frames, only the repeating frame is placed in the paste buffer.

• A matrix object can only be resized by resizing its associated repeating frames.

• You cannot use Alignment or Size Objects from the Layout menu on matrix objects.

• The source groups of the repeating frames that make up the dimensions of a matrix must be from the same cross-product group.

• Repeating frames whose source groups are in the same "family" hierarchy (i.e., are descendants or ancestors of each other) must have the same Print Direction. Parent-child relationships within a cross-product group are used to create nesting in the matrix. As a result, the repeating frames associated with such groups must print in the same direction on the page.

• You can put a border on a matrix object just as you would any other object, but the width will always be the minimum width possible. You cannot widen the border due to the closeness of the objects in a matrix layout.

Example

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:

• The Vertical Repeating Frame is the repeating frame that contains Group2 (the job titles).

• The Horizontal Repeating Frame is the repeating frame that contains Group1 (the department numbers).

• The Cross Product Group is Group4 (the group that is the parent of Group1 and Group2).

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

See also

Section 1.3.7, "About matrix reports"

Section 2.1.6, "About nested matrix reports"

Section 2.1.7, "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"

2.4 Layout Objects

The topics in this section build on the basic concepts discussed in Section 1.8, "Layout Objects".

• About layout objects

• About layout defaulting

• About images

• About anchors

• About changing colors and patterns

• About resizing objects

• About moving and layering objects in the Paper Layout view

2.4.1 About layout objects

Several important concepts and properties apply to layout objects:

• the frequency with which you want the object to appear in the report, specified by the Print Object On property

• how Reports Builder fetches and formats data for instances of repeating frames, specified by the Column Mode property

• whether to keep an object and the object to which it is anchored on the same logical page, specified by the Keep With Anchoring Object property

• whether to try to keep the entire object and its contents on the same logical page, specified by the Page Protect property

• format triggers, which are PL/SQL functions executed before an object is formatted that can dynamically change the formatting attributes of objects

• report layout, generated by defaulting applied by Reports Builder, modified in the Paper Layout view, or created from scratch.

See also

Section 3.5.4, "Creating a default layout for a report"

Section 3.5.5, "Creating an additional report layout"

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

2.4.2 About layout defaulting

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. Forexample, 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:

• All previously-defined layout objects for the report will be overwritten (including format triggers) unless you define the area in which you wish to create the layout as one which does not already contain layout objects.

• One report can have any number of different formats. For example, you can build a report that has a tabular format on the top of the first page, and a matrix format on the bottom of the same page by creating an additional report layout (see Section 3.5.5, "Creating an additional report layout").

• Once you have created a report layout, any further changes you make to the data model will not automatically be included in the layout. For example, if you create a query after you have created a report layout and then run your report, the data from the new query will not appear in the report output. To incorporate your changes, you need to either redefault or modify the layout.

Reports Builder defaults report layout according to the following rules:

1. Unless otherwise noted, a group with a Print Direction of Across defaults identically to a group with a Print Direction of Down, except that the default format is transposed. To quickly determine the defaulting of an Across group, do the following:
   • Draw the down layout for the group on translucent paper.

   • Turn the page over as if it was a page in a book.

   • Rotate the page counter-clockwise 90 degrees.

2. In form letter reports, all default fields are hidden, and have a Horizontal Elasticity property setting of Variable and a Vertical Elasticity property setting of Fixed. In all other reports, all default fields have a Horizontal and Vertical Elasticity property setting of Fixed.

   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.

   Caution: For form letter and mailing label reports, the default for CHAR (if you reduce the default width) and LONG fields have a Horizontal Elasticity and Vertical Elasticity property setting of Fixed. As a result, the field value will be truncated if the size of the data is greater than the size of the field.

3. If one or more selected fields have a Horizontal Elasticity or Vertical Elasticity property setting of Variable, Reports Builder does the following for Down reports:
   • creates a frame called M_groupname_EXP that has a width equal to the width of the repeating frame that owns the variable-sized field, and a length of one field.

   • places the frame around that variable-width field and any other fields that are found to the right or left of it. This ensures that all fields to the right and left of the Variable field begin to print "on the same line," even though the Variable field may span several lines.

4. For Across reports, transpose the above rule.

5. Summaries that are owned by the report, not by a group, are allowed in all report layout styles. They are all formatted in the following way:
   • If the layout style is form-like, form letter, or mailing label, the summary is a report column. A report column is formatted like any other database column for that report style.

   • If the layout style is tabular, group left/above, or matrix, and the column on which the summary performs its function is not selected, the summary is a report column. (It is formatted as stated in the bullet above.) Otherwise, the summary is a report summary. It is formatted left-justified at the end of the report, with the label to the left of the field (if there is room).

6. If the layout style is tabular, group left/above, or matrix, a summary is defaulted like a database column if the column it summarizes is not selected. Otherwise, a summary is defaulted like summaries (i.e., appearing inside the M_groupname_FTR frames).

7. If the layout style is tabular, group left/above, or matrix, Reports Builder places one summary type per line, in the following order:
   • SUM

   • AVERAGE

   • MINIMUM

   • MAXIMUM

   • COUNT

   • FIRST

   • LAST

   • % OF TOTAL

   • STANDARD DEVIATION

   • VARIANCE

8. If the layout style is tabular, group left/above, or matrix, the summary label will appear to the far-left of the group footer frame (M_groupname_FTR). If there is not room for the full label, the label will be truncated.

See also

Section 3.5.4, "Creating a default layout for a report"

Section 3.10.2, "Creating a default layout for a section"

2.4.3 About images

You can add an image to a report by:

• selecting a column in the database that contains images (see Section 3.9.8.7, "Selecting an image from the database")

• importing the image from a file into the report layout (see Section 3.9.8.6, "Importing a drawing or image")

• linking an image object to a file (see Section 3.9.8.9, "Linking an image object to a file")

• for HTML output, selecting a column in the database that contains URL links to images or linking an image object to a URL (see Section 3.9.8.8, "Selecting an image URL from the database" and Section 3.9.8.10, "Linking an image object to a URL")

By default, images display in fields so that they appear in the printed report, not only in the Previewer.

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.

2.4.4 About anchors

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. The end of the anchor with a symbol on it is attached to the parent object, as shown here:

Figure 2-5 An anchor attached to the parent object

Text description of the illustration ch8and33.gif

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:

• Implicit anchors. At runtime, Reports Builder generates an implicit anchor for each layout object that does not already have an explicit anchor. It determines for each layout object which objects, if any, can overwrite it, then creates an anchor from the layout object to the closest object that can overwrite it. This prevents the object from being overwritten. The implicit anchor functionality saves you from having to define the positioning of each object. Implicit anchors are not visible in the Paper Layout view. However, you can specify in the Object Navigator Options dialog box that the Object Navigator display anchoring information. By default, objects are anchored to the upper left corner of their enclosing object. If this view of the Object Navigator does not show anchoring information for an object, you can assume that the object is anchored to its enclosing object, which might be the frame or the body.

• Explicit anchors. You can create an anchor in the Layout editor using the Anchor tool, dragging from one edge of the child to the one of the parent's edges. Any anchor you create for an object will override its implicit anchoring. Explicit anchors are always visible in the Paper Layout view unless you specify otherwise in the Layout Options dialog box.

Relative positioning 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:

• The vertical distance between the two objects is fixed. For example, in the figure below, the vertical spacing between the parent and Object 1 is fixed.

• The horizontal positioning of the anchor's x-coordinate on the child object is relative to the anchor's x-coordinate on the parent object. For example, in the figure below, the anchor is 50% from the edge of the parent and 75% from the left edge of Object 1. Therefore, when this report is run, Oracle Reports will shift Object 1 25% to the left of the center of the parent.

Figure 2-6 Parent object above child object

Text description of the illustration a_anchors_rp.gif

If the parent object is located to the right or left of the child object:

• The vertical positioning of the two objects is relative. For example, in the figure below, both ends of the anchor are about 80% down from the top edges of the objects. Therefore, when the report is run, Reports Builder will calculate the length of the two objects (as they may expand), calculate the y coordinate that is 80% down for both objects, and position the two objects so that those two points are separated by the amount of space separating them in the Paper Layout view.

• The horizontal positioning between the two objects is fixed. For example, in the figure below, the horizontal spacing between the parent and Object 1 is fixed.

Figure 2-7 Parent object to the right of child object

Text description of the illustration a_anchors_rp2.gif

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.

Collapsing Anchors

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.

2.4.4.1 Implicit anchoring algorithm

Reports Builder creates implicit anchors at runtime in the body region. The margin algorithm differs slightly.

Body algorithm

1. Determine which objects are not entirely enclosed by a repeating frame of frame (directly or indirectly), or explicitly anchored to an object that is enclosed by a frame or repeating frame (directly or indirectly). We'll call these objects Type A objects. (Type A objects are typically group frames, repeating frames, other objects you create that are not owned by a frame or repeating frame, etc..). An object is considered to be enclosed by another object only if all of the following are true:
   • Both objects belong to the same region (Body or Margin).

   • The outermost of the two objects is a frame or repeating frame.

   • The outermost of the two objects is behind the other object.

   • The innermost of the two objects lies entirely within the borders of the other object.

2. Determine all children objects of a frame or repeating frame (these are the non-Type A objects). We'll call these objects Type B objects.

3. Follow this procedure for Type A and Type B objects independently:
   • Find all objects that are of the same type (e.g., Type A), and are on the same layer.

   • Determine which of those objects have potential to "push" other objects of that type. An object has potential to "push" object of that type if it has a Horizontal and/or Vertical Elasticity setting of Variable or Expand, and a second object is located in its "push path" (i.e., in the area in which it can possibly grow). Also, a repeating frame with a Horizontal and/or Vertical Elasticity setting of Fixed or Contract has a "push path": its Print Direction.

   • Create pairs of objects. Each pair must contain a pusher (i.e., the object that will grow) and a pushee (i.e., the object that will be pushed). When creating these pairs, a pushee object cannot be a child of an explicit anchor--those objects are ignored.

   • Go through this loop. For each pair, determine the distance in the "push path" between the pusher and pushee. Next, find the pair with the shortest distance. Finally, create an implicit anchor between those two objects using this algorithm:

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

   • That pair is now treated like one object, and the loop continues until either all objects have one anchor, or nothing will push the remaining, unanchored objects.

   • For each remaining, unanchored object, create an implicit anchor from the top-left corner of the object to the top-left corner of the body region.

   • Move to the next layer, and follow the procedure starting at step 1.

Rules:

• If an object is in the "push path" of two other objects and it is equidistant from the other two objects, the implicit anchoring of the object may vary between executions of the report. For example, the drawing below shows two cases where this could occur:

Figure 2-8 Object in push path of two other objects

Text description of the illustration ch2ru636.gif

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

Margin algorithm

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

See also

Section 3.9.5.1, "Anchoring objects together"

Section 3.9.5.2, "Viewing implicit anchors"

Section 3.9.5.3, "Moving an anchor"

2.4.5 About changing colors and patterns

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:

• In the Reports Builder user interface, use the following tools in the Paper Layout view's tool palette:
  • The Line Color tool is used to customize the color of borders around layout objects. Note: The Windows platform does not support a border pattern (that is, patterns for the Line Color tool).

  • The Fill Color tool is used to fill layout objects with colors and patterns.

  • The Text Color tool is used to change the default text color.

  • The Fill/Line/Text Display, the box directly above the three Color tools, shows the currently selected fill, border, and text. The default fill and border for objects created by Reports Builder is transparent, while the default for objects you create is a black, onepoint line around a white fill.

• In PL/SQL, use the following SRW packaged procedures:
  • SRW.SET_BACKGROUND_BORDER_COLOR

  • SRW.SET_BACKGROUND_FILL_COLOR

  • SRW.SET_FOREGROUND_BORDER_COLOR

  • SRW.SET_FOREGROUND_FILL_COLOR

  • SRW.SET_TEXT_COLOR

  • SRW.SET_FILL_PATTERN

  • SRW.SET_BORDER_PATTERN

• For templates, set the following properties in the template Property Inspector:
  • The Fill Pattern property defines the pattern to use for the space enclosed by the objects. You can define the background and foreground colors of the fill pattern using the Foreground Color and Background Color properties.

  • The Edge Pattern property defines the pattern to use for the borders of the objects. You can define the background and foreground colors of the edge pattern using the Edge Foreground Color and Edge Background Color properties. Note: The Windows platform does not support a border pattern.

  • The Text Color property specifies the text color to use for the object(s).

Additionally, you can set color palette preferences to specify how it is used by a report (see Section 3.2.7, "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".)

See also

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

2.4.6 About resizing objects

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.

Caution: When you resize boilerplate text, be very careful that all of the text fits within the object. If font descends (the space left for the lower parts of letters like g and q) do not fit, the line of text will appear in the Report Editor view, but, when the report is run, the line will not appear in the output. When you click a handle and drag it, the two edges that join at the corner will be resized; that is, the object will grow or reduce in both the x and y directions.

See also

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"

2.4.7 About moving and layering objects in the Paper Layout view

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.

See also

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"

2.5 Parameter Form Objects

The topic in this section builds on the basic concepts discussed in Section 1.9, "Parameter Form Objects".

2.5.1 About Parameter Form HTML extensions

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:

• Create boilerplate text with HTML tags for adding hyperlinks or any other HTML tagged text to your Parameter Form (see Section 3.6.7.1.14, "Creating a boilerplate text object for HTML tags").

• Insert parameter fields with JavaScript for defining input or select events, such as raising errors when users enter invalid data in a parameter field (see .Section 3.11.12, "Creating HTML Parameter Form input or select events").

• Create a Parameter Form header (a before form escape) for placing a logo or some standard links in the header of the HTML Parameter Form (see Section 3.6.7.1.5, "Creating an HTML Parameter Form header using the Property Inspector" to use the Property Inspector, or Section 3.6.7.2.5, "Creating an HTML Parameter Form header using PL/SQL").

• Create a Parameter Form footer (an after form escape) for placing a logo or some standard links in the footer of the HTML Parameter Form (see Section 3.6.7.1.6, "Creating an HTML Parameter Form footer using the Property Inspector" to use the Property Inspector, or Section 3.6.7.2.6, "Creating an HTML Parameter Form footer using PL/SQL").

You can access the Parameter Form Builder from the Object Navigator or by choosing Tools > Parameter Form Builder.

See also

Section 1.2.2, "About Web reports"

Section 1.9.4, "About Parameter Forms for Web reports"

Section 2.2.4, "About Web links for HTML output"

2.6 PL/SQL

The topics in this section discuss the use of PL/SQL in Reports Builder.

• About the PL/SQL Editor

• About the Stored PL/SQL Editor

• About the Syntax Palette

• About program units

• About stored program units

• About stored program units

• About external PL/SQL libraries

• About attached libraries

• About formulas

• About group filters

• About ref cursor queries

• About built-in packages

• About triggers

2.6.1 About the PL/SQL Editor

The PL/SQL Editor enables you to create and edit PL/SQL program units.

Usage notes

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.

Restrictions

• If you delete a PL/SQL package, function, or procedure, you must also delete all references to it in your report. Otherwise, you will get an error when you compile, generate, or run the report.

• PL/SQL package, function, and procedure names must be unique within the report and may not duplicate the names of any columns, groups, queries, or printable objects.

See also

Section 3.13.3.1, "Creating a local program unit"

2.6.2 About the Stored PL/SQL Editor

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

See also

Section 3.13.3.2, "Creating a stored program unit"

2.6.3 About the Syntax Palette

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.

See also

Section 3.13.2.4, "Inserting syntax into the PL/SQL Editor"

2.6.4 About program units

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

Restrictions

• If you delete a PL/SQL package, function, or procedure, you must also delete all references to it in your report. Otherwise, you will get an error when you compile, generate, or run the report.

• PL/SQL package, function, and procedure names must be unique within the report and may not duplicate the names of any columns, groups, queries, or printable objects.

Example: Referencing PL/SQL function in formulas

Suppose that you have a report with the following groups and columns:

Groups    Columns         Summary 
-----------------------------------------
RGN       REGION
          RGNSUMSAL       SUM(DEPTSUMSAL)
          COSTOFLIVING
		   
DEPT      DNAME
          DEPTNO
          DEPTSUMSAL      SUM(EMP.SAL)
		   
JOB       JOB
          HEADCOUNT       COUNT(EMP.EMPNO)
		   
EMP       ENAME
          EMPNO
          SAL
          COMM

Given these groups and columns, you might create multiple formulas that apply the cost of living factor (COSTOFLIVING) to salaries. To avoid duplication of effort, you could create the following PL/SQL function and reference it from the formulas:

function CompSal(salary number) return number is
begin
  return (salary*CostofLiving);
end;

Following are some examples of how you might reference the PL/SQL function in formulas:

CompSal(:RGNSUMSAL)

or

CompSal(:SAL) + COMM

See also

Section 3.13.3.1, "Creating a local program unit"

2.6.5 About stored program units

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.

See also

Section 3.13.3.2, "Creating a stored program unit"

2.6.6 About external PL/SQL libraries

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.

See also

Section 3.13.5.1, "Creating an external PL/SQL library"

2.6.7 About attached libraries

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

Usage notes

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.

Restrictions

• If Reports Builder cannot find a library that you specify in the Attached Libraries list, a warning will be raised when you accept the dialog box, save the report, or open the report. If you try to run the report or compile the PL/SQL in it, an error will be raised.

• The Attached Libraries list is saved. The next time you open the report or library the list will have the same contents it did when you last saved the report.

• If an external library references another library, you must attach both libraries to the report even if the first library already has the second one attached.

See also

Section 3.13.5.5, "Attaching a PL/SQL library"

2.6.8 About formulas

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.

Restrictions

• You can read and assign values to a column in a formula, if the column is a placeholder or parameter column; you cannot change the value of database columns (values retrieved from the database). For example, you can use the value of a column called COMP in a condition (e.g., IF :COMP = 10) and you can directly set its value in an assignment statement (e.g., :COMP := 15).

• A formula can only make reference to columns that are in the same or a higher group in the group hierarchy. For example, a formula for a report-level column can only reference other report-level columns.

• Formulas are calculated such that any column referenced in the formula will be calculated first. To do so, Reports Builder builds a dependency list, to guarantee proper ordering of calculations. Note that circular dependencies, in which a column references another column which in turn references the first column, either directly or indirectly, are not allowed.

• When using SRW.DO_SQL, we recommend that you do not read database values that are updated or inserted in the same report. There is no guarantee of the exact time Reports Builder will fetch records from the database for formatting the output. Reports Builder does internal "data look-ahead" to optimize performance. Thus, a particular record might already have been accessed before an update is issued to the same record. Reports Builder builds internal dependency lists which guarantee that events, such as invocation of user exits, calculation of summaries, etc., happen in the correct order. However, Reports Builder cannot guarantee these events will be synchronized with its internal data access or with the formatting of data.

Examples

Example 1: Adding values

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;

Example 2: Using conditions

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;

See also

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"

2.6.9 About group filters

A group filter is a PL/SQL function that determines which records to include in a group, if the Filter Type property is PL/SQL. You can use the packaged filters, First and Last, to display the first n or last n records for the group, or you can create your own filters using PL/SQL. You can access group filters from the Object Navigator, the Property Inspector (the PL/SQL Filter property), or the PL/SQL Editor.

The function must return a boolean value (TRUE or FALSE). Depending on whether the function returns TRUE or FALSE, the current record is included or excluded from the report.

Difference between group filters and Maximum Rows to Fetch property

The Maximum Rows to Fetch property restricts the actual number of records fetched by the query. A group filter determines which records to include or exclude, after all the records have been fetched by the query. Since Maximum Rows to Fetch actually restricts the amount of data retrieved, it is faster than a group filter in most cases. If you 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.

Restrictions

Group filters cannot be added to groups if Filter Type is First or Last.

• Group filters cannot be added to cross-product groups.

• The function that you enter for a group filter can only depend upon the following columns:
  • a database column owned by the group's query or a query above it in the data model hierarchy

  • computed columns (formulas or summaries) that depend on unrelated queries (i.e., computed columns that do not depend upon columns in the group, the group's ancestors, or the group's descendants)

• In a group filter, you can read the values of Reports Builder columns and parameters of the correct frequency, but you cannot directly set their values. For example, you can use the value of a parameter called COUNT1 in a condition (e.g., IF :COUNT1 = 10), but you cannot directly set its value in an assignment statement (e.g., :COUNT1 := 10). Note also that the use of PL/SQL global variables to indirectly set the values of columns or parameters is not supported. If you do this, you may get unpredictable results. You also cannot reference any page-dependent columns (i.e., Reset At of Page) or columns that rely on page-dependent columns in a group filter.

Example

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;

See also

Section 3.13.4.2, "Creating or editing a group filter"

2.6.10 About ref cursor queries

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.

Usage notes

• When you make a ref cursor query the child in a data link, the link can only be a group to group link. It cannot be a column to column link.

• If you use a stored program unit to implement ref cursors, you receive the added benefits that go along with storing your program units in the Oracle database.

• You base a query on a ref cursor when you want to:
  1. more easily administer SQL

  2. avoid the use of lexical parameters in your reports

  3. share data sources with other applications, such as Form Builder

  4. increase control and security

  5. encapsulate logic within a subprogram

Furthermore, if you use a stored program unit to implement ref cursors, you receive the added benefits that go along with storing your program units in the Oracle database.

For more information about ref cursors and stored subprograms, refer to the PL/SQL User's Guide and Reference.

Examples

Example 1: Package with ref cursor example

/* This package spec defines a ref cursor
** type that could be referenced from a
** ref cursor query function.
** If creating this spec as a stored
** procedure in a tool such as SQL*Plus,
** you would need to use the CREATE
** PACKAGE command.
*/
 
PACKAGE cv IS
type comp_rec is RECORD 
  (deptno number,
   ename varchar(10),
   compensation number);
type comp_cv is REF CURSOR return comp_rec;
END;

Example 2: Package with ref cursor and function

/* This package spec and body define a ref
** cursor type as well as a function that
** uses the ref cursor to return data.
** The function could be referenced from
** the ref cursor query, which would
** greatly simplify the PL/SQL in the
** query itself. If creating this spec
** and body as a stored procedure in a
** tool such as SQL*Plus, you would need
** to use the CREATE PACKAGE and CREATE
** PACKAGE BODY commands.
*/
 
PACKAGE cv IS
type comp_rec is RECORD
  (deptno number,
   ename varchar(10),
   compensation number);
type comp_cv is REF CURSOR return comp_rec;
function emprefc(deptno1 number) return comp_cv;
END;
 
PACKAGE BODY cv IS
function emprefc(deptno1 number) return comp_cv is
  temp_cv cv.comp_cv;
begin
  if deptno1 > 20 then
    open temp_cv for select deptno, ename,
    1.25*(sal+nvl(comm,0)) compensation
    from emp where deptno = deptno1;
  else
    open temp_cv for select deptno, ename,
    1.15*(sal+nvl(comm,0)) compensation
    from emp where deptno = deptno1; 
  end if;
  return temp_cv;
end;
END;

Example 3: Ref cursor

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;

Example 4: Ref cursor query

/* This ref cursor query function would be coded
** in the query itself. It uses the cv.comp_cv
** ref cursor from the cv package to return
** data for the query.
*/
function DS_3RefCurDS return cv.comp_cv is
  temp_cv cv.comp_cv;
begin
  if :deptno > 20 then
    open temp_cv for select deptno, ename,
    1.25*(sal+nvl(comm,0)) compensation
    from emp where deptno = :deptno;
  else
   open temp_cv for select deptno, ename,
   1.15*(sal+nvl(comm,0)) compensation
   from emp where deptno = :deptno;
  end if;
  return temp_cv;
end;

Example 5: Ref cursor query calling function

/* This ref cursor query function would be coded
** in the query itself. It uses the cv.comp_cv
** ref cursor and the cv.emprefc function from
** the cv package to return data for the query.
** Because it uses the function from the cv
** package, the logic for the query resides
** mainly within the package. Query
** administration/maintenance can be
** done at the package level (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;

See also

Section 3.8.1.8, "Creating a query: Ref Cursor Query tool"

2.6.11 About built-in packages

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.

2.6.11.1 About the Reports Builder built-in package (SRW)

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.

See also

Topic "SRW built-in package" in the Reference section of the Reports Builder online help

2.6.11.2 About Tools built-in packages

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:

• DDE

  Provides Dynamic Data Exchange support within Reports Builder components.

• DEBUG

  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.

• EXEC_SQL

  Provides procedures and functions for executing dynamic SQL within PL/SQL code written for Reports Builder applications.

• LIST

  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.

• ORA_FFI

  Provides a foreign function interface for invoking C functions in a dynamic library.

• ORA_JAVA

  Provides an interface for invoking Java classes from PL/SQL.

• ORA_NLS

  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.

• ORA_PROF

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

• TEXT_IO

  Provides constructs that allow you to read and write information from and to files. There are several procedures and functions available in Text_IO, falling into the following categories:

  • file operations. The FILE_TYPE record, the FOPEN and IS_OPEN functions, and the FCLOSE procedure enable you to define FILE_TYPE variables, open files, check for open files, and close open files, respectively.

  • output (write) operations. The PUT, PUTF, PUT_LINE, and NEW_LINE procedures enable you to write information to an open file or output it to the PL/SQL Interpreter.

  • input (read) operations. The GET_LINE procedure enables you to read a line from an open file

• TOOL_ENV

  Allows you to interact with Oracle environment variables by retrieving their values for use in subprograms.

• TOOL_ERR

  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.

• TOOL_RES

  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 Oracle9i Reports. There are no subprograms available for external use with these packages.

• ORA_DE

  Contains constructs used by Reports for private PL/SQL services.

• STPROC

  Calls subprograms stored in the database. Calls to this package are automatically generated.

• JNI

  Facilitates calling Java from PL/SQL.

See also

Topics for each of the Tools built-in packages under "Built-in Packages" in the Reference section of the Reports Builder online help

2.6.12 About triggers

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.

2.6.12.1 About report triggers

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):

• Before Report trigger

• After Report trigger

• Between Pages trigger

• Before Parameter Form trigger

• After Parameter Form trigger

For information about these triggers, see the Reference section of the Reports Builder online help.

Usage notes

The Before Report trigger fires before the report is executed but after the queries are parsed.

You can think of order in this way:

1. Queries are parsed.

2. Before Report trigger fires.

3. Report is executed (i.e., fetch data+format output)

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.

See also

Section 3.13.3.5, "Creating a report trigger"

Section 3.13.3.6, "Deleting a report trigger"

2.6.12.2 About format triggers

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:

• change the border pattern and color of an object

• change the interior pattern and color of an object

• change the font size, style, weight, spacing, and justification of a field or boilerplate text

• change the format mask of a field

• access a field's value

Examples

See the topic "Format trigger" in the Reference section of the Reports Builder online help.

See also

Section 3.13.4.1, "Creating or editing a format trigger"

2.6.12.3 About validation triggers

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 rep-546 Invalid Parameter Input error. Thus, you need to provide the parameters in an alternate way, as described in Section 1.9.4, "About Parameter Forms for Web reports".

Validation triggers are also used to validate the Initial Value property of the parameter. Depending on whether the function returns TRUE or FALSE, the user is returned to the Runtime Parameter Form.

Example

See the topic "Validation trigger" in the Reference section of the Reports Builder online help.

See also

Section 3.11.4, "Validating a parameter value at runtime"

2.6.12.4 About database triggers

Database triggers are procedures that are stored in the database and implicitly executed when a triggering statement such as INSERT, UPDATE, or DELETE is issued against the associated table. Triggers can be defined only on tables, not on views. However, triggers on the base table of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against a view.

A trigger can include SQL and PL/SQL statements that execute as a unit, and can invoke other stored procedures. Use triggers only when necessary. Excessive use of triggers can result in cascading or recursive triggers. For example, when a trigger is fired, a SQL statement in the trigger body potentially can fire other triggers.

By using database triggers, you can enforce complex business rules and ensure that all applications behave in a uniform manner. Use the following guidelines when creating triggers:

• Use triggers to guarantee that when a specific operation is performed, related actions are performed.

• Use database triggers only for centralized, global operations that should be fired for the triggering statement, regardless of which user or database application issues the statement.

• Do not define triggers that duplicate the functionality already built into Oracle. For example, do not define triggers to enforce data integrity rules that can be easily enforced using declarative integrity constraints.

• Limit the size of triggers (60 lines or fewer is a good guideline). If the logic for your trigger requires much more than 60 lines of PL/SQL code, it is better to include most of the code in a stored procedure, and call the procedure from the trigger.

• Be careful not to create recursive triggers. For example, creating an AFTER UPDATE statement trigger on the EMP table that itself issues an UPDATE statement on EMP causes the trigger to fire recursively until it has run out of memory.

For additional information about how triggers are used in applications, see the Oracle9i Application Developer's Guide. See the Oracle9i Concepts Manual for more information about the different types of triggers.

See also

Section 3.13.3.7, "Creating a database trigger"

2.7 Templates

The topics in this section discuss the use of templates in Reports Builder.

• About the Template Editor

• About templates

• About template attributes

• About applying templates

• About inheritance in templates

2.7.1 About the Template Editor

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.

Access

You can access the Template Editor in the following ways:

When creating a new template:

• Choose File > New > Template.

• In the Object Navigator, click the Templates node, then click the Create button in the toolbar.

When displaying an existing template:

• From the Window menu, choose a window displaying Template Editor Paper Layout view.

• In the Object Navigator, double-click the view icon next to the Paper Layout node for a template.

2.7.2 About templates

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 use the Report Wizard to create a paper-based report, you have the option of applying a template (.tdf file) to the report. 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.

If you later apply another template to a report, the existing template objects will be deleted in the current report section.

See also

Section 3.12, "Define a Template"

2.7.3 About template attributes

In the Layout Body area of a template, you can define Default and Override attributes under the following Object Navigator nodes:

• Frames, which contains the following:
  • a Section Frame node, which defines attributes for the parent frame surrounding the currently selected section.

  • a Headings Frame node, which defines attributes for the parent frame surrounding the column headings.

  • a Fields Frame node, which defines attributes for the parent frame surrounding the fields.

  • a Summaries Frame node, which defines attributes for the parent frame surrounding the summaries (totals).

• Field Labels/Headings, which contains the following:
  • a Character node, which defines attributes for the labels or column headings of character fields.

  • a Number node, which defines attributes for the labels or column headings of number fields.

  • a Date node, which defines attributes for the labels or column headings of date fields.

• Fields, which contains the following:
  • a Character node, which defines attributes for character fields.

  • a Number node, which defines attributes for number fields.

  • a Date node, which defines attributes for date fields.

• Summary Labels, which contains the following:
  • a Character node, which defines attributes for the labels of summaries on character fields.

  • a Number node, which defines attributes for the labels of summaries on numeric fields.

  • a Date node, which defines attributes for the labels of summaries on date fields.

• Summaries, which contains the following:
  • a Character node, which defines attributes for summaries on character fields.

  • a Number node, which defines attributes for summaries on number fields.

  • a Date node, which defines attributes for summaries on date fields.

Default attributes

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.

Override attributes

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.

See also

Section 3.12.2, "Defining default template attributes"

Section 3.12.3, "Defining override template attributes"

2.7.4 About applying templates

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:

• parameters and their validation triggers

• physical page size

• logical page size

• character/bitmap mode

• margin position

• panel print order

• Maximum Horizontal Body Pages property

• Maximum Vertical Body Pages property

In addition, all of the layout objects in the margin of the template are copied into the same location in the current report section.

Note: The template will be applied to the current section of the report (the section displayed in the Paper Layout view) by default. If no section is displayed, the template will be applied to the Main Section of the report by default. If you select the node for the entire report in the Object Navigator, the template will be applied to the Main Section of the report by default. To apply the template to a specific section of your report, select the node for that section in the Object Navigator.

See also

Section 3.12.4, "Applying a template to a report"

2.7.5 About inheritance in templates

In templates, Sections, Frames, Fields, Labels, Headings, and Summaries properties all may inherit their values.

Default properties

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.

Override properties

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.

2.8 Output Formats and Capabilities

The topics in this section discuss the various output formats and capabilities in Reports Builder.

• About batch reporting

• About report distribution

• About event-based publishing

• About switching the printer tray

• About HTML output

• About PDF output

• About XML output

• About RTF output

• About delimited output

• About text output

• About creating an ASCII (character-mode) report

2.8.1 About batch reporting

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

See also

Section 3.7.2, "Running a report from the command line"

Section 3.7.3, "Running a report using a command file"

Topic "Command line keywords" in the Reference section of the Reports Builder online help (for information about BATCH and BACKGROUND)

2.8.2 About report distribution

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.  

For detailed information about when you'd use the report-level vs. section-level distribution, see Chapter 6, "Bursting and Distributing a Report". This chapter also covers the new feature in Oracle9i Reports that lets you distribute based on a repeating section, then e-mail those sections based on the distribution.xml.

You can specify the distribution for a report in any of the following ways:

• using XML, as described in the chapter "Creating Advanced Distributions" in the Oracle9i Reports Publishing Reports manual

• in the Distribution dialog box (in the report or section Property Inspector, under the General Layout node, double-click the Distribution value field (labeled ...) to display the dialog box)

• on the command line via the DESTINATION keyword that specifies a .DST file.

  Note: this method is supported for backward compatibility; the preferred and recommended method of distributing reports is via XML or the Distribution dialog box.

Note: To enable the distribution specified in any of these ways, you can:

• choose File > Distribute.

• specify DISTRIBUTE=YES on the command line.

To verify a successful distribution, you can trace the report distribution as described in Section 3.14.20, "Tracing report distribution".

Usage notes

• You cannot mix character mode and bit-mapped output in one report. The MODE parameter can only be set to one value per the entire report (DEFAULT, BITMAP, or CHARACTER). As a result, you cannot have both within one report.

2.8.2.1 About the .DST file

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. Note: this method is supported for backward compatibility; the preferred and recommended method of distributing reports is via the Distribution dialog box or using XML, as described in the chapter "Creating Advanced Distributions" in the Oracle9i Reports Publishing Reports manual.

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 that if you trace report distribution to identify distribution errors (see Section 3.14.20, "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

Example

/* 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

See also

Section 2.1.2, "About report sectioning and sections"

Section 3.7.10, "Distributing a report to multiple destinations"

Section 3.14.20, "Tracing report distribution"

2.8.3 About event-based publishing

Beginning with Oracle9i Reports, you can execute a report from a PL/SQL stored procedure API. This feature, also known as 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.

2.8.4 About switching the printer tray

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.

See also

Section 3.7.14.5, "Switching the printer tray"

2.8.5 About HTML output

Reports Builder generates 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, Oracle Portal, and Web browser.

Usage notes

• For HTMLCSS, graphics and text can be overlapped.

• Once you have generated your report to an HTML or HTMLCSS file, the data model and looping tags are removed and replaced with the data. You can open the HTML or HTMLCSS file in Reports Builder, but it will be a static text file and not a report.

• For information about paginating HTML or HTMLCSS output, see Section 2.8.5.1, "About HTML page streaming".

See also

Section 3.7.5, "Generating HTML, HTMLCSS or PDF output"

Section 3.7.1, "Running and dispatching a report from the user interface"

Section 1.2.2, "About Web reports"

Section 2.2.3, "About HTML and PDF in Reports Builder"

2.8.5.1 About HTML page streaming

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:

• within the report, the frame that contains the current page will update with the destination page.

• outside the report, the entire base frame (including the bookmark frame, the page, and the navigation frame) will reload.

Navigation controls

You can specify the navigation controls script for a report in either of the following ways:

• via PL/SQL (SRW.SET_PAGE_NAVIGATION_HTML in a Before Report trigger)

• in the Report Property Inspector via the Page Navigation Control Type and Page Navigation Control Value properties

Output file names

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:

• myreport.htm (for the base frame)

• myreportb.htm (for the bookmark file, present only if bookmarks are used in the HTML files)

• myreport_1.htm through myreport_n.htm (for the pages)

• myreportj.htm (for the navigation JavaScript)

Scope of HTML output

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.

Enabling page-streamed output

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.

Delimited output restrictions

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.

See also

Section 3.7.13.5, "Displaying individual pages of HTML report output"

Section 3.6.7.1.12, "Adding navigation controls for HTML page-streamed output using the Property Inspector"

Section 3.6.7.2.11, "Adding navigation controls for HTML page-streamed output using PL/SQL"

2.8.6 About PDF output

Reports Builder generates Adobe Acrobat 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, Oracle Portal, and Web browser.

Usage notes

• If you are building a multi-byte report for multi-byte languages, such as Chinese or Japanese, and you need to alias the font in PDF output, you need the CID fonts named within the Acrobat 4.0 packs. Otherwise, you do not need the CID fonts in the Acrobat 4.0 packs.

• Graphics and text can be overlapped.

• Text can only be rotated by 90-degree variations.

• The foreground color of the object will be used as the fill color (regardless of a specified pattern).

See also

Section 3.7.5, "Generating HTML, HTMLCSS or PDF output"

Section 3.7.1, "Running and dispatching a report from the user interface"

Section 1.2.2, "About Web reports"

Section 2.2.3, "About HTML and PDF in Reports Builder"

2.8.7 About XML output

Oracle9i Reports uses XML (Extensible Markup Language) in the following ways:

• XML tags are used to define Web-based reports (see the topic "XML tag list" in the Reference section of the Reports Builder online help).

• XML is used to define tag-delimited, structured information.

• XML is a supported pluggable data source (PDS). For more information, see Chapter 8, "Building a Report with an XML Pluggable Data Source".

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.6, "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 Oracle9i Reports Publishing Reports manual.

See also

Section 3.7.6, "Generating XML output"

Section 3.7.1, "Running and dispatching a report from the user interface"

2.8.8 About RTF output

Reports Builder generates 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, Oracle Portal, and Web browser.

Usage notes

• Graphics and text can be overlapped.

• Text can only be rotated by 90-degree variations.

• As with PDF output, the foreground color of the object will be used as the fill color (regardless of a specified pattern).

  Note: When you view the report in Microsoft Word in Office 95, you must choose View > Page Layout to see all the graphics and objects in your report.

See also

Section 3.7.7, "Generating RTF output"

Section 3.7.1, "Running and dispatching a report from the user interface"

2.8.9 About delimited output

Delimited output enables you to specify a delimiter (a character or string of characters) to separate the data (boilerplate or field objects) in your report output. Using this feature, you can create report output that is easily imported into spreadsheets or for use with word processors.

You can specify the delimiter in either of the following ways:

• on the command line via the DELIMITER keyword.

• in the Delimited Output dialog box (displayed via File > Generate to File > Delimited).

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

Usage Notes

When you generate delimited output, the data displays according to the positions of the objects in the Paper Layout view.

• If you place A above or to the left of B (where A and B are any boilerplate or field objects) Reports Builder displays each instance of A before each instance of B in every line of output.

Figure 2-9 Delimited output of A above B

Text description of the illustration delim1.gif

• If you create a frame that contains a boilerplate object (A) and encloses a repeating frame that contains a field object (B), each instance of A displays with each instance of B.

Figure 2-10 Delimited output of frame enclosing repeating frame

Text description of the illustration delim2.gif

• In the previous layout, if you add another frame that contains a boilerplate object (C) and encloses another repeating frame that contains a field object (D), A displays for every instance of B, and then C displays for every instance of D.

Figure 2-11 Delimited output of two frames enclosing repeating frames

Text description of the illustration delim3.gif

• If you create a matrix in your report, be sure to align your objects carefully according to the grid in the Layout Model view. If the objects are not aligned, Reports Builder may interpret the extra space as an extra row or column and disrupt your report output.

• If you create a boilerplate object outside of a matrix, each instance of the boilerplate repeats with every row (not column) of the matrix. Note that boilerplates contained in the matrix will not be repeated with field objects outside of the matrix.

Figure 2-12 Delimited output of boilerplate outside matrix

Text description of the illustration delim4.gif

Restrictions

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.

See also

Section 3.7.8, "Generating delimited output"

Section 3.7.1, "Running and dispatching a report from the user interface"

Section 1.2.2, "About Web reports"

2.8.10 About text output

Reports Builder generates 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), the result is pure text output, which can be read by many different applications. If the running mode is bit-mapped (MODE=BITMAP), the result is PostScript output, which can be read and rendered only by PostScript-compatible applications (such as a PostScript printer).

See also

Section 3.7.9, "Generating text output"

2.8.11 About creating an ASCII (character-mode) report

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:

Table 2-2 Supported items in bit-mapped and character-mode reports

See also

Section 3.5.7, "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"

2.9 Data Sources

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

• About database roles

• About Oracle Net Services

• About Oracle9i

2.9.1 About database roles

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). Note: To run a report for which a database role is defined, the end user must run the .rdf file, not the .rep file. When running multiple reports, Reports Runtime automatically switches to the role defined for the current report.

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.

See also

Section 3.16.1, "Setting a database role"

2.9.2 About Oracle Net Services

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 (Net8 replaces SQL*Net in Release 6.0 and later. Net8 is renamed to Oracle Net Services in Release 9i).

2.9.3 About Oracle9i

The topics in this section cover Oracle9i database support in Oracle9i Reports.

2.9.3.1 Oracle9i Server support

The Oracle9i Server provides a number of new features. In general, the features are in these categories:

• Greater scalability. For example, Oracle9i databases can contain tens of terabytes of data, and can support simultaneous access by up to 10000 users. Tables themselves can now contain up to 1000 columns. CHAR and VARCHAR2 datatypes can now be up to 2000 and 4000 bytes, respectively.

• Greater performance and efficiency. Use of these Oracle9i features is handled for you and requires no actions on your part. You automatically receive the speed and reliability benefits of having your data on the Oracle9i Server.

• Large objects (sometimes called LOBs)

• User-defined objects.

2.9.3.2 Oracle9i datatypes supported

This release continues to support the following Oracle9i datatypes:

Large objects (LOBs)

In Oracle9i, programmers can define large objects, often called LOBs. These can store such things as text, images, sound, or video.

Oracle9i Reports supports the following Oracle9i large object datatypes:

• BLOB, or binary large object

• CLOB, or single-byte character large object

• BFILE, or binary file (sometimes referred to as an external file).

User-defined objects

In Oracle9i, programmers can define object types (also called user-defined types), which are composites based on the existing single-element datatypes supported by Oracle9i. Using these object definitions, programmers can then create various kinds of tables.

Currently supported Oracle9i objects include:

• An object table, where the entire table is based on a single object type.

• A column object in a relational table, where that column is based on an object type.

• A REF column in either an object or relational table, where that column's values are pointers to rows in another object table.

Scalar datatypes

Oracle9i Reports supports the new Oracle9i FLOAT datatype. It maps this datatype to its own NUMBER datatype.

2.9.3.3 About Oracle9i large objects (LOBs)

You can access the Oracle9i large object datatypes: binary large objects (BLOBs), character large objects (CLOBs), and binary files (BFILEs). On an Oracle9i server, these datatypes can store objects of up to 4 gigabytes in size. They are similar in purpose to the familiar Oracle7 LONG and LONG RAW datatypes, but offer a number of additional advantages.

You can use Oracle9i large objects (LOBs) to reduce the amount of temporary space used by Oracle9i Reports. On the command line, you can specify whether to cache the LOB on the client. If you choose not to cache the LOB, Oracle9i Reports will store a reference to the LOB instead, and will not cache the LOB in a temporary file. While this reduces the need for temporary space, it slows performance because the column's values must always be retrieved from the database.

Note that you can only specify LOB caching on the command line.

Table 2-3 Comparison of LOBs with LONGs

LOBs in detail

BLOBs are used to store large objects in binary format. CLOBs are used to store character data. These two types are also known as internal LOBs, because their data is stored within the server. The data participates in transactions and integrity control, including commits and rollbacks.

BFILEs, in contrast, are stored outside of the server. The server stores only the operating system address of the file. As a consequence, BFILEs can not participate in database transactions.

Compatibility

Because of the various advantages offered by LOBs (see the table above), you should used LOBs instead of LONGs in any new application.

However, for backward compatibility, LONGs are still fully supported.

Restrictions

• BFILEs are read-only.

• Although Oracle9i Reports will attempt to load the entire BFILE or BLOB when it is read at runtime, it is limited by the amount of runtime virtual memory available.

• For CLOBs, Oracle9i Reports has a size limitation of 65534 bytes.

2.9.3.4 About Oracle9i user-defined objects

In its support for the Oracle9i Server, Oracle9i Reports provides you with access to Oracle9i user-defined objects. These include object tables, column objects in relational tables, and the special kind of column object known as a REF column.

Object tables

In Oracle9i, an object table is based on a user's object type definition. This definition is itself a composite, each attribute or component of which is based on a familiar Oracle9i built-in datatype. (An object type definition can be based on another object type definition, but ultimately the definitions rest on known Oracle9i built-in datatypes.)

Because of this, each column in an object table is treated as an individual data item, in a manner similar to the way it treats the columns of a relational table.

Column objects in relational tables

A database programmer can also base a column in a relational table on an object type definition. This has the effect of embedding a composite object in the relational table.

The embedded column object is expanded into its composite parts, and each part is treated as a column in itself.

For example, if the Oracle9i Server held a 3-column relational table, and one of those columns was a column object, and that column object was based on an object definition having four parts or attributes, then the table would be treated as a 6-column table (the 2 relational columns plus the 4 columns that comprise the object).

In the event one of the individual attributes of the column object's definition was based on yet another object type, then that part is further expanded and treated as a set of individual columns.

REF columns

A column in either an object or relational table can be defined as having a type of REF. A REF column does not hold data directly, but rather has pointers (references) to another object table that holds the actual data.

The reference can be resolved at runtime, so that the user sees the actual referenced data.

How Oracle9i Reports treats objects

An object table is treated very much like a relational table. Each column is individually selectable. Although all columns may be selected, each is treated as a separate item within the application. The table itself does not have any particular identity as an object within the environment.

Similarly, a column object in a relational table is not treated as a single, homogeneous entity. Instead, it treats each of the column object's components as an individual, independent data item. Each component of the column object is selectable. Once selected, each is treated as an independent data item. Even if all of the column object's components are selected, the column object itself does not have any particular identity as an object within the development environment.

Data items selected from an object are given the default name ColumnObjectName_ItemName. Thus, if we select the component named zip from the column object named address in the above example, the data item will have the name address_zip within the application.

Examples

To create objects in the Oracle9i Server, a database programmer first defines an object type, and then creates a specific object based on that type definition. The resulting object can be either an object table or a column object in a relational table.

Example 1

Consider the following definitions. The first creates an object type. The second creates a table of that type.

CREATE TYPE emp_type AS OBJECT
  (name CHAR(30),
   title CHAR(20),
   idnum NUMBER);

CREATE TABLE emp OF emp_type;

In the above example, we create an object table named emp. It has three columns, and those columns have the names and datatypes that are established in the attributes of the emp_type object type definition.

Figure 2-13 Relationship between object type and object table

Text description of the illustration o8cncpt1.gif

Example 2

The following is an example of a column object in a relational table:

CREATE TYPE address_type AS OBJECT
  (street VARCHAR2(40),
   city VARCHAR2(25),
   state VARCHAR2(2)
   zip NUMBER);
   
CREATE TABLE customers
  (cust_id NUMBER,
   name VARCHAR(40),
   address address_type);

In this example, we create a relational table named customers. The table has three columns. The first two columns (cust_id and name) are the usual relational columns. The third column (named address) is a column object. It is built on the object type definition named address_type. This address column has the structure and datatypes established by that object definition. The address column can be thought of as having four parts or components (street, city, state, and zip). Each of these components is of the datatype that was established in the address_type object type definition. (In this case, all are of VARCHAR2 except for zip, which is of datatype NUMBER).

In the following illustration, the third column of a three-column relational table is based on an object type that itself has three parts or components.

Figure 2-14 Third column based on an object type that has three parts

Text description of the illustration o8cncpt2.gif

Note that an Oracle9i object is a composite, built from parts or components. Each of its parts has a name and is of a known Oracle9i datatype. If the object is a table (actually, a row in an object table), it has as many columns or parts as there are attributes in the object type definition. If a column in a relational table is based on an object, then each of the object's parts can be thought of an additional column in that table. Because each of these parts is based on an attribute in the object type definition, these parts are sometimes called attribute columns.

2.9.3.5 About Oracle9i REFs

REF establishes a column object as a pointer to data in another object table. The REF column itself does not hold the data.

For example:

CREATE TYPE dept_type AS OBJECT
 (deptname VARCHAR2(24),
  deptnumber NUMBER);
 
CREATE TABLE dept_table OF dept_type;

CREATE TABLE emp
  (empname VARCHAR2(50),
   empnumber NUMBER,
   empdept REF dept_type);

In the relational table named emp, the column empdept is a REF column. It points to an object of type dept_type.

Figure 2-15 Relational table with reference column to object

Text description of the illustration o8cncpt3.gif

Types versus tables

When a REF column is defined, it identifies an object type -- not a particular object table. It is possible for a database to contain more than one object table based on a single object type definition. Thus, the REF column definition itself does not identify the target table(s).

Pointer resolution

When the table holding the REF column is initialized, the values placed in the REF column will be pointers to rows in the specific table or tables that have the desired data. Later at runtime, when users look at the REF column, they will see the pointed-to data (not the pointers).

Figure 2-16 Relational table with resolved reference column

Text description of the illustration o8cncpt4.gif

How Oracle9i Reports displays REFs at design time

At design time, when you ask to see a table that contains a REF column, the REF column displays in its expanded form. That is, it shows the attributes in the object type pointed to by the original REF definition.

In the above example, the empdept REF column would show as holding two components or attribute columns: deptname and deptnumber. These were the two attributes in the object type (dept_type) identified in the REF definition.

Just as with a regular column object, you can select any or all of the referred-to components/attribute columns to become data items in the application. However, in contrast to regular column objects, you can also select the REF name itself to become a manipulatable item within your application. That is, you can use the REF in your PL/SQL coding. By default, the REF becomes part of the null canvas.

How you can display REF data to your users

As an application designer, you have a choice in how the runtime user will see the referenced data (the resolved REF):

• data displayed in the data block in which the REF exists (sometimes called the look-up method)

• data displayed in the data block pointed to by the REF (the master-detail method)

The first choice (displaying the data as part of the data block in which the REF exists) is the simplest. You select the desired REF components as items into your data block, and at runtime Oracle9i Reports and Oracle9i will resolve the references and place the actual referred-to data in the display items. The user need see only the one block.

The other choice (displaying the data in the pointed-to data block) requires setting up a master-detail link. This is very similar to setting up a master-detail link for two blocks having a primary-key foreign-key relationship. In this case, the REF creates the link between the two blocks. The user will see both blocks, but will see the REF data only in the referred-to block.

2.9.3.6 Oracle9i object support in the Object Navigator

Oracle9i functionality in the Object Navigator provides access to object datatypes, enabling you to program against Oracle9i data more easily. The added Oracle9i object support allows you to view user-defined datatypes in a hierarchical format, and to find and display tables, views, columns, triggers, attributes and methods associated with the user-defined datatypes.

The Database Objects node contains the schemas and their objects that you can access in the currently connected database. When connected to an Oracle9i database, the Object Navigator displays available user-defined datatypes (with their attributes and methods) in the Types node within each schema. Tables, views, columns and triggers associated with the user-defined datatypes are displayed in the Tables and Views nodes.

Specifically, Oracle9i functionality in the Object Navigator enables you to display and find:

• object tables and column objects in the Tables node

• object views in the Views node

• Instead Of triggers for views in the Views node

• object types, table types and varying array types in the Types node

• attributes and methods of object types in the Types node

Icons are used to differentiate relational objects and Oracle9i objects in the Object Navigator. For example, separate icons are used to represent normal, relational tables and object tables, and normal views and object views.

For information about Oracle9i user-defined datatypes, see your Oracle9i documentation.

2.9.3.7 Oracle9i scalability and performance

Oracle9i Server gives you a number of scalability and performance improvements. For the most part, you obtain these advantages automatically.

For example, you can access Oracle9i's:

• Larger sizes. The database itself, the number of concurrent users, and its table column limits (now up to 1000 columns) have all increased.

• Table and index partitioning

• Client-side object caching

You can also make direct use of several other Oracle9i functions that provide it with improved performance:

• Prefetch. Oracle9i Reports uses this Oracle9i feature (along with the existing Array Size mechanism) to reduce both database round-trips and memory usage. This is done automatically, without any need for user involvement.

• Returning values clause. Oracle9i Reports uses this new DML feature when doing inserts and updates. The Returning clause eliminates the need for a second database query (to obtain any server-side trigger changes to the data), thus speeding up processing. Users can control it with the DML Returning Value property.

• INSTEAD-OF trigger for updating views. This server-side trigger provides a mechanism for updating both relational and object views that would not otherwise be updatable.

2.9.3.8 Oracle9i support for PL/SQL

Oracle9i uses PL/SQL on the client and the server. Client-side program units currently cannot support Oracle9i object-related functionality. However, stored program units can use all the PL/SQL features, as described in Appendix A in the PL/SQL User's Guide and Reference.

2.9.3.9 Oracle9i compatibility with earlier releases of PL/SQL

If you have client-side program units written in PL/SQL 1 or PL/SQL 2, you must convert the code to the new level. The PL/SQL 1 conversion utility is provided to help automate the upgrading process.

Stored program units created with previous releases will run against the Oracle9i version of PL/SQL engine on the database server, but may compile with errors. These errors arise in some situations where there is syntax incompatibility between PL/SQL8 and PL/SQL 2. A compatibility flag in the Oracle9i version of PL/SQL allows you to choose a PL/SQL 2 compatibility mode, so that the the Oracle9i version of PL/SQL engine does not issue such errors when it compiles PL/SQL 2 code in the server. Chapter 6 in the Oracle9i Server Migration manual describes the situations that are covered by this compatibility flag.

2.9.3.10 Oracle9i features not yet supported

Not all Oracle9i features are currently supported across this release. The major unsupported features are:

• Collection types (nested tables and varying arrays, or varrays)

• Stored procedures that return object values

• Scalar datatypes NCHAR and NVARCHAR2.

2.10 Debugging Tools

The topics in this section discuss debugging reports in Reports Builder.

• About the debugging process

• About the PL/SQL Interpreter

• About the Source pane

• About debug commands in the PL/SQL Interpreter

• About debug actions

• About the current execution location

• About the current scope location

• About debug levels

• About modifying code at runtime

2.10.1 About the debugging process

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.

See also

Section 3.14.1, "Debugging a report"

Section 3.14.2, "Running a report in debug mode"

2.10.2 About the PL/SQL Interpreter

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:

• Direct manipulation debugging: insert debug actions and inspect program data by directly manipulating displayed source text.

• Dynamic execution feedback: Reports Builder automatically displays and tracks the current PL/SQL source location as program execution is interrupted by debug actions or incrementally advanced during program stepping.

• Browsing of interrupted program state: once execution has been interrupted, it is possible to browse the current stack, browse and modify variable state, and execute arbitrary PL/SQL statements. All information is accessed symbolically (i.e., by name as opposed to by address or number).

2.10.3 About the Source pane

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.

Table 2-4 Symbols in the margin of the Source pane

2.10.4 About debug commands in the PL/SQL Interpreter

The following commands are available when using the PL/SQL Interpreter:

Table 2-5 PL/SQL Interpreter Commands

2.10.5 About debug actions

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. There are two types of debug actions: breakpoints and debug triggers.

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.

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:

1. when program execution reaches a single line in a program unit (e.g., the current source location, line 5, line 23, etc.)

2. every time the PL/SQL Interpreter takes control (i.e., whenever it suspends program execution due to a breakpoint, program stepping, etc.)

3. at every PL/SQL source line being run

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;

2.10.5.1 About creating a debug action

You can create debug actions (breakpoints and debug triggers) in the PL/SQL Interpreter in three ways:

• using the Break and Trigger menu commands

• inserting debug actions in the Object Navigator pane

• entering commands in the Interpreter pane

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.

See also

Section 3.14.3, "Setting a breakpoint"

Section 3.14.4, "Setting a debug trigger"

2.10.6 About the current execution location

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.

2.10.7 About the current scope location

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.

See also

Section 3.14.15, "Displaying the current scope location"

2.10.8 About debug levels

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>

2.10.9 About modifying code at runtime

At runtime, you can modify and compile any program unit, menu item command, or trigger that is not on the current stack.

Note: To modify an item on the current stack, first clear the stack by issuing the RESET command.

Although runtime code modification is not communicated back to Reports Builder, you can interactively test possible fixes, before returning to implement the eventual fix.

See also

Section 3.14.14, "Modifying code at runtime"