4.13 Use PL/SQL in a Report or Template

This section provides procedures for the following tasks that you may perform as you use PL/SQL in a report or template:

4.13.1 Using a built-in package

To use a built-in package:

  1. Choose Tools > PL/SQL Editor to display the PL/SQL Editor.

  2. In the Object Navigator, expand the Built-in Packages node, then the package you want to use.

  3. Right-click a procedure, function, or exception and choose Paste Name or Paste Arguments to copy a call to the package into your PL/SQL code.

See also

Section 2.6.12, "About built-in packages"

4.13.2 Working with the PL/SQL Editor

This section provides procedures for the following tasks that you may perform as you work with the PL/SQL Editor:

4.13.2.1 Defining PL/SQL

To define PL/SQL:

  1. In the PL/SQL Editor, type or edit the PL/SQL code for the program unit.

  2. Click Compile.

  3. If necessary, click an error to navigate to its location in the source code.

    Tip:

    Check for missing semicolons at the end of statements, or misspelled syntax.
  4. When the code compiles successfully, click Close.

See also

Section 2.6.4, "About program units"

4.13.2.2 Searching and replacing text in a program unit

To search and replace text in a program unit:

  1. In the Object Navigator, expand the Program Units node.

  2. Double-click the PL/SQL Editor view icon next to the program unit you want to edit.

  3. In the PL/SQL Editor, place your cursor where you wish to begin the search.

  4. Choose Edit > Find and Replace.

  5. In the dialog box, type your search criteria, and, optionally, the replace string. You can supply either a text string or a regular expression for your search text.

  6. Click Search.

  7. Upon locating an instance of the search criteria, click Replace to replace a single instance, Replace All to replace all instances, or edit the text directly in the PL/SQL Editor.

  8. Click Search to proceed to the next instance, or click Cancel to close the dialog box.

  9. When you have finished replacing all text in the program unit, click Compile to recompile the program unit.

    Tip:

    If you replace text in multiple program units, you can recompile them all at once when you are finished.
  10. When the code compiles successfully, click Close.

    Tip:

    If your edits are extensive, you may want to click Apply to save your changes incrementally, without having to compile.

See also

Section 2.6.4, "About program units"

4.13.2.3 Editing a program unit

To edit a program unit:

  1. In the Object Navigator, expand the Program Units node.

  2. Double-click the PL/SQL Editor view icon for the program unit you want to edit.

  3. In the PL/SQL Editor, edit the PL/SQL for the program unit.

See also

Section 2.6.4, "About program units"

4.13.2.3.1 Editing features in the PL/SQL Editor

The editing features in the PL/SQL Editor (and Stored PL/SQL Editor) include:

Table 4-7 Editing features of PL/SQL Editor

Editing Feature Description

Automatic indent

When you press the Enter key at the end of a line, the next line is automatically indented.

Color syntax highlighting

Keywords, comments, strings, and symbols such as := and || are colored differently.

Column and line selection

You can select columns of text as well as lines of source code.

To select a column, press the ALT key, then click and drag horizontally.

To select a line, click the extreme left margin of the line in the Source pane.

Drag and drop text manipulation

Selected text may be copied or moved by dragging and dropping.

To copy text, press the CTRL key and drag the selected text.

To move text, simply drag the selected text.

Indent/Outdent lines

The Indent/Outdent commands on the Edit menu enable you to indent or outdent selected source lines.

Multiple split views

You can create up to four separate views of the current program unit.

To create horizontal views, place the cursor on the split bar at the top of the vertical scroll bar. Then click and drag the split bar down.

To create vertical views, place the cursor on the split bar at the far left of the horizontal scroll bar. Then click and drag the split bar to the right.

To remove a horizontal or vertical view, double-click the split bar that separates the views.

Printing

Choose File > Print to print the current program unit.

Unlimited undo/redo

The Undo/Redo commands on the Edit menu enable you to undo or redo changes as far back as the last save operation.


Note:

These features are available in Microsoft Windows only. On UNIX, you can print the current program unit, and you can use TAB/Shift-TAB to indent/outdent selected lines.

4.13.2.3.2 Using the keyboard in the PL/SQL Editor

The following keyboard actions are supported when using the PL/SQL Editor (and Stored PL/SQL Editor) in Microsoft Windows and UNIX:

Table 4-8 Using the keyboard in the PL/SQL Editor

To do the following Use Keystroke On Platform

Move cursor left one character

Left arrow key

Windows and UNIX

Select character as cursor moves left

Shift + Left arrow key

Windows and UNIX

Move cursor right one character

Right arrow key

Windows and UNIX

Select character as cursor moves right

Shift + Right arrow key

Windows and UNIX

Copy selected characters or words

Ctrl + C

Windows and UNIX

Cut selected characters or words

Ctrl + X

Windows and UNIX

Paste from the clipboard

Ctrl + V

Windows and UNIX

Delete character on the right of current cursor position, or delete selected characters or words

Delete key

Windows and UNIX

Delete character on the left of current cursor position

Backspace

Windows and UNIX

Move cursor to the end of the program unit

Ctrl + End

Windows

Move cursor to the start of the program unit

Ctrl + Home

Windows

Select source lines from the current cursor position to the end of the program unit

Ctrl + Shift + End

Windows

Select source lines from the current cursor position to the start of the program unit

Ctrl + Shift + Home

Windows

Move cursor to the end of current line

End

Windows

Move cursor to the start of current line

Home

Windows

Select characters from the current cursor position to the end of the current line

Shift + End

Windows

Select characters from the current cursor position to the start of the current line

Shift + Home

Windows

Indent selected line, or indent characters on the right of current cursor position

Tab Key

Windows and UNIX

Outdent selected line

Shift + Tab key

Windows and UNIX

Move cursor down one line

Down arrow key

Windows and UNIX

Select line as cursor moves down

Shift + Down arrow key

Windows and UNIX

Move cursor up one line

Up arrow key

Windows and UNIX

Select line as cursor moves up

Shift + Up arrow key

Windows and UNIX

Scroll down the program unit by the number of lines that are shown in the Source pane

PageDown key

Windows and UNIX

Scroll down the program unit by the number of lines that are shown in the Source pane and select the lines at the same time

Shift + PageDown key

Windows and UNIX

Scroll up the program unit by the number of lines that are shown in the Source pane

PageUp key

Windows and UNIX

Scroll up the program unit by the number of lines that are shown in the Source pane and select the lines at the same time

Shift + PageUp key

Windows and UNIX

Undo most recent action

Ctrl + Z

Windows

Revert most recent undo action

Ctrl + Y

Windows


4.13.2.3.3 Using the mouse in the PL/SQL Editor

The following table describes the mouse actions that are supported when using the PL/SQL Editor (and Stored PL/SQL Editor) in Microsoft Windows:

Table 4-9 Using the mouse in the PL/SQL Editor

To do the following Use mouse action

Select characters in a range

Click and drag the cursor from the first character to the last character in the range you wish to select. (also supported in UNIX)

or...

Click the first character, then press the Shift key, and click the last character in the range you wish to select.

Select word under cursor

Double-click the word. (also supported in UNIX)

or...

Press the Ctrl key, then click the word you wish to select.

Select words in a range

Press the Ctrl key, then click and drag the cursor from the first word to the last word in the range you wish to select.

Select a line

Place the cursor on the left margin of the line you wish to select. Click when the cursor changes to an arrow.

Select multiple lines

Place the cursor on the left margin of the first line you wish to select. When the cursor changes to an arrow, click and drag the cursor to the last line you wish to select.

Select columns of text

Press the Alt key, then click and drag the cursor from the first column to the last column in the range you wish to select.

Move selected text

Select the text first. Then click and drag the selected text to its new position.

Copy selected text

Select the text first. Then press the Ctrl key, and drag the selected text to the location where you want to place a copy and release the mouse.

Split window into two horizontal views

Double-click the split bar at the top of the vertical scroll bar.

or...

Click and drag the split bar at the top of the vertical scroll bar.

Split window into two vertical views

Double-click the split bar at the extreme left of the horizontal scroll bar.

or...

Click and drag the split bar at the extreme left of the horizontal scroll bar.

Adjust relative size of split views

Click and drag the split bar that separates the views.

Remove split views

Double-click the split bar that separates the views.

or...

Click and drag the split bar to the edge of the window.

To remove all four views at once, double-click the intersection where the split bars meet or drag it to any corner of the window.


4.13.2.4 Inserting syntax into the PL/SQL Editor

To copy syntax into the PL/SQL Editor:

  1. Make sure the PL/SQL Editor or Stored PL/SQL Editor is the current (most recently selected) window.

  2. Place the cursor in the editor where you want to insert the syntax, then choose Tools > Syntax Palette.

  3. In the Syntax Palette, click the PL/SQL tab or the Built-ins tab.

  4. Choose a PL/SQL category or a built-in package from the list.

    When you click a category or a package, the PL/SQL language elements or PL/SQL constructs that are available for selection appear in the list area below.

  5. Choose a PL/SQL language element or construct in the list area.

    When you click a PL/SQL language element or construct, the syntax appears in the display area that is below the list area.

  6. Click Insert to copy the selected syntax.

    The selected PL/SQL language element or construct is inserted into the active editor at the current cursor position.

  7. Replace all lowercase items that are not comments with the appropriate values. Items within comments are optional. Reserved words are in uppercase.

    Note:

    You can also double-click a PL/SQL language element or construct in the list area to insert the syntax into an editor.

See also

Section 2.6.3, "About the Syntax Palette"

4.13.3 Creating or editing report-level or template-level PL/SQL

This section provides procedures for the following tasks that you may perform as you create or edit report-level or template-level PL/SQL:

4.13.3.1 Creating a local program unit

To create a local (client-side) program unit:

  1. In the Object Navigator, click the Program Units node.

  2. Click the Create button in the toolbar.

  3. In the dialog box, type a name for the program unit in the Name text box.

  4. If your program unit is not a procedure (a PL/SQL subprogram that performs a specified sequence of actions), click one of the following:

    • Function (a PL/SQL subprogram that performs a specified sequence of actions, and then returns a value)

    • Package Spec (datatypes and subprograms that can be referenced by other program units)

    • Package Body (implementation of the package, which may include private subprograms and datatypes; optional if the package consists only of declarations)

    Note:

    If the radio button is not selected when you click, try clicking a different radio button, then click the desired Type again.
  5. Click OK.

  6. In the PL/SQL Editor, define the PL/SQL for the program unit.

See also

Section 2.6.4, "About program units"

4.13.3.2 Creating a stored program unit

To create a stored (server-side) program unit:

  1. In the Object Navigator, double-click the Database Objects node. If this node is disabled, the Connect dialog box displays so you can establish a database connection.

  2. Expand the subnode that corresponds to the database user name you used to log in to the database to show the PL/SQL Stored Program Units node.

  3. Click the PL/SQL Stored Program Units node, then click the Create button in the toolbar.

  4. In the dialog box, type a name for the program unit in the Name text box.

  5. If your program unit is not a procedure (a PL/SQL subprogram that performs a specified sequence of actions), click one of the following:

    • Function (a PL/SQL subprogram that performs a specified sequence of actions, and then returns a value)

    • Package Spec (datatypes and subprograms that can be referenced by other program units)

    • Package Body (implementation of the package, which may include private subprograms and datatypes; optional if the package consists only of declarations)

    • Type Spec (declares the name, variables (attributes) and member subprograms (methods) for an object type or collection type)

    • Type Body (implementation of the member methods (functions and procedures) defined in the type specification for an object type. For each method specified in an object type, there must be a corresponding method body)

    Note:

    If the radio button is not selected when you click, try clicking a different radio button, then click the desired Type again.
  6. Click OK.

  7. In the Stored PL/SQL Editor, select a database owner name from the Owner list to indicate where the program unit will be stored in the database, then define the PL/SQL for the stored program unit.

See also

Section 2.6.5, "About stored program units"

4.13.3.3 Deleting a program unit

Note:

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.

To delete a program unit:

  1. In the Object Navigator, expand the Program Units node.

  2. Click the program unit you want to delete.

  3. Click the Delete button in the toolbar.

  4. In the message dialog box, click Yes to confirm the deletion.

See also

Section 2.6.4, "About program units"

4.13.3.4 Moving a program unit between client and database server

To move a program unit from the client to the database server:

  1. In the Object Navigator, double-click the Database Objects node. If this node is disabled, the Connect dialog displays so you can establish a database connection.

  2. Expand the subnode that corresponds to the database user name you used to log in to the database to show the PL/SQL Stored Program Units node.

  3. In the Reports section of the Object Navigator, expand the Program Units node.

  4. Click the program unit you want to store in the database, and drag it from the Program Units node to the Stored Program Units subnode.

  5. Release the mouse button to insert a copy of the program unit on the server.

To move a program unit from the database server to the client:

  1. In the Object Navigator, double-click the Database Objects node. If this node is disabled, the Connect dialog box displays so you can establish a database connection.

  2. Expand the subnode that corresponds to the database user name you used to log in to the database.

  3. Expand the Stored Program Units node.

  4. Click the stored program unit you wish to move to the client and drag it from the Stored Program Units node to the Program Units node in the Reports section of the Object Navigator.

  5. Release the mouse button to insert a copy of the program unit on the client.

See also

Section 2.6.5, "About stored program units"

Section 2.6.4, "About program units"

4.13.3.5 Creating a report trigger

To create a report trigger:

  1. In the Object Navigator, expand the Report Triggers node.

  2. Double-click the PL/SQL icon for the trigger you want to create.

  3. In the PL/SQL Editor, define the PL/SQL for the report trigger.

See also

Section 2.6.13.1, "About report triggers"

4.13.3.6 Deleting a report trigger

To delete a report trigger:

  1. In the Object Navigator, expand the Report Triggers node.

  2. Double-click the PL/SQL icon for the trigger you want to delete.

  3. In the PL/SQL Editor, drag to select the PL/SQL code.

  4. Choose Edit > Delete.

See also

Section 2.6.13.1, "About report triggers"

4.13.3.7 Creating a database trigger

To create a database trigger:

  1. Choose Tools > Database Trigger Editor.

  2. In the Database Trigger Editor, choose a user name (schema) from the Table Owner list.

  3. Choose a table name from the Table list.

    The Table list shows a list of table names owned by the user shown in the Table Owner field. If you select a user name other than your own in the Table Owner field, the Table list shows only the tables to which you have been granted access.

  4. (For views only) To define an INSTEAD OF trigger for an object view, click the arrow next to Table to display a list and choose View. Then choose a view name from the View list.

  5. Click New to create a new database trigger.

    A unique trigger name (with respect to other triggers in the same schema) is automatically assigned to the new trigger in the Name list. You can modify the trigger name.

    The Name list displays a list of trigger names associated with the table (or view) shown in the Table (or View) field. The Name list displays only the names of the database triggers associated with the tables to which you have access.

  6. After specifying the trigger options and action, click Save to compile the trigger.

See also

Section 2.6.13.4, "About database triggers"

4.13.4 Creating or editing object-level PL/SQL

This section provides procedures for the following tasks that you may perform as you create or edit object-level PL/SQL:

4.13.4.1 Creating or editing a format trigger

To create or edit a format trigger using the Property Inspector:

  1. In the Paper Design view, double-click the object for which you want to create or edit a format trigger to display the Property Inspector.

  2. Under Advanced Layout, set the Format Trigger property by clicking the ... button to display the PL/SQL Editor.

  3. Define the PL/SQL for the format trigger.

To create or edit a format trigger using the Object Navigator:

  1. In the Object Navigator, expand the Paper Layout node, then expand the node that contains the object for which you want to create or edit a format trigger.

  2. Double-click the PL/SQL icon next to the object for which you want to create or edit a format trigger to display the PL/SQL Editor.

  3. Define the PL/SQL for the format trigger.

4.13.4.2 Creating or editing a group filter

To create or edit a group filter:

  1. In the Data Model view, double-click the title bar of the group to display the Property Inspector.

  2. Scroll to the Group node.

  3. To display the first n records for the group, set the Action Type property to First and set the Number of Records property to n.

  4. To display the last n records for the group, set the Action Type property to Last and set the Number of Records property to n.

  5. To create your own filter using PL/SQL, set the Action Type property to PL/SQL and set the PL/SQL Filter property by clicking ... to display the PL/SQL Editor to define the PL/SQL for the filter.

  6. Set other properties as desired.

See also

Section 2.6.9, "About group filters"

4.13.4.3 Creating or editing a formula column

See Section 4.8.10, "Creating or editing a formula column".

4.13.4.4 Creating a placeholder column

See Section 4.8.12, "Creating or editing a placeholder column".

4.13.4.5 Changing colors and patterns using PL/SQL

See Section 4.9.6.4, "Changing colors and patterns using PL/SQL".

4.13.5 Creating or editing an external PL/SQL library

This section provides procedures for the following tasks that you may perform as you create or edit an external PL/SQL library:

See also

Section 2.6.6, "About external PL/SQL libraries"

4.13.5.1 Creating an external PL/SQL library

To create an external PL/SQL library:

  1. In the Object Navigator, click the PL/SQL Libraries node.

  2. Click the Create button in the toolbar.

The newly created library (initially named LIB_xxx) is automatically opened. Once a library has been created, its contents can be modified by inserting or removing program units, or attaching other libraries.

4.13.5.2 Adding a program unit to an open library

To add a program unit to an open library:

  1. Perform one of the following steps in the Object Navigator, depending on your current state:

    • if the library is open, expand the library node

    • if the library is not currently open, click the PL/SQL Libraries node and choose File > Open to open the library

  2. Under the Reports node, expand the Program Units node.

  3. Drag the program unit you want to add to the library below the library's Program Units node.

  4. Release the mouse button to insert a copy of the program unit in the library.

4.13.5.3 Editing a program unit in a PL/SQL library

To edit a program unit in a PL/SQL library:

  1. In the Object Navigator, expand the PL/SQL Libraries node, then the library node for the program unit.

  2. Under the library's Program Units node, double-click the PL/SQL Editor view icon for the program unit you want to edit.

  3. In the PL/SQL Editor, edit the PL/SQL for the program unit.

4.13.5.4 Removing a program unit from a PL/SQL library

To remove a program unit from a PL/SQL library:

  1. In the Object Navigator, expand the PL/SQL Libraries node, then the library node for the program unit.

  2. Under the open library's Program Units node, click the library program unit you want to delete.

  3. Click the Delete button in the toolbar.

  4. In the message box, click Yes.

4.13.5.5 Attaching a PL/SQL library

To attach a PL/SQL library:

  1. In the Object Navigator, click the Attached Libraries node.

  2. Click the Create button in the toolbar.

  3. In the Attach Library dialog box, type the name of the external PL/SQL library in the Library text box, or click Browse to search for the external PL/SQL library you want to reference.

  4. Click Attach.

Restrictions

  • If Oracle Reports Builder cannot find the specified library, 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.

4.13.5.6 Converting external PL/SQL libraries

To convert one or more PL/SQL libraries from one storage format to another on Windows:

  • On the command line, type %ORACLE_HOME%\BIN\rwconverter, followed by the report name and desired arguments. See the Reference section of the Oracle Reports online Help for more information about rwconverter.

4.13.6 Compiling and running program units

This section provides procedures for the following tasks that you may perform as you compile and run program units:

See also

Section 2.6.4, "About program units"

4.13.6.1 Compiling a single program unit

To compile a single program unit:

  1. In the Object Navigator, under the Program Units node, click the program unit you want to compile (PL/SQL subprogram, report trigger, formula, group filter, format trigger, or validation trigger).

  2. Choose Program > Compile > Selection to compile the selected program unit, regardless of its current compilation status.

    Note:

    An uncompiled program unit is indicated by an asterisk (*) after its name under the Program Units node in the Object Navigator. When you make changes to a program unit, dependent program units lose their compiled status.
  3. In the Compile dialog box, click any error to navigate to the source of the error in the program unit.

    Tip:

    Check for missing semicolons at the end of statements, or misspelled syntax.

4.13.6.2 Compiling all program units

To compile all program units:

  1. In the Object Navigator, click the report or library for which you want to compile all program units (including PL/SQL subprograms, report triggers, formulas, group filters, format triggers, and validation triggers).

  2. Choose Program > Compile > All to compile all program units, regardless of their current compilation status.

    Note:

    An uncompiled program unit is indicated by an asterisk (*) after its name under the Program Units node in the Object Navigator. When you make changes to a program unit, dependent program units lose their compiled status.
  3. In the Compile dialog box, click any error to navigate to the source of the error in the program unit.

    Tip:

    Check for missing semicolons at the end of statements, or misspelled syntax.

4.13.6.3 Compiling all uncompiled program units

To compile all uncompiled program units:

  1. In the Object Navigator, click the report or library for which you want to compile all uncompiled program units (including PL/SQL subprograms, report triggers, formulas, group filters, format triggers, and validation triggers).

  2. Choose Program > Compile > Incremental to compile all uncompiled program units.

    Note:

    An uncompiled program unit is indicated by an asterisk (*) after its name under the Program Units node in the Object Navigator. When you make changes to a program unit, dependent program units lose their compiled status.
  3. In the Compile dialog box, click any error to navigate to the source of the error in the program unit.

    Tip:

    Check for missing semicolons at the end of statements, or misspelled syntax.