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:

• Using a built-in package
• Working with the PL/SQL Editor
• Creating or editing report-level or template-level PL/SQL
• Creating or editing an external PL/SQL library
• Compiling and running program units

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.

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:

• Defining PL/SQL
• Searching and replacing text in a program unit
• Editing a program unit
• Inserting syntax into 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.

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.

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

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.

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

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.

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.

Choose File > Print to print the current program unit.

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

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

Move cursor left one character

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

Windows and UNIX

Cut selected characters or words

Windows and UNIX

Paste from the clipboard

Windows and UNIX

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

Windows and UNIX

Delete character on the left of current cursor position

Windows and UNIX

Move cursor to the end of the program unit

Move cursor to the start of the program unit

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

Ctrl + Shift + End

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

Ctrl + Shift + Home

Move cursor to the end of current line

Move cursor to the start of current line

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

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

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

Windows and UNIX

Outdent selected line

Windows and UNIX

Move cursor down one line

Windows and UNIX

Select line as cursor moves down

Shift + Down arrow key

Windows and UNIX

Move cursor up one line

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

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

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

Revert most recent undo action

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

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)

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)

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.

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.

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.

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.

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.

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:

• Creating a local program unit
• Creating a stored program unit
• Deleting a program unit
• Moving a program unit between client and database server
• Creating a report trigger
• Deleting a report trigger
• Creating a database trigger

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)

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

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)

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

4.13.3.3 Deleting a program unit

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.

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.

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.

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 .

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.

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:

• Creating or editing a format trigger
• Creating or editing a group filter
• Creating or editing a formula column
• Creating a placeholder column
• Changing colors and patterns using 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.