PL SQL

The goal of this chapter is to introduce PL/SQL to those unfamiliar with it and to highlight how PL/SQL may be used in a Crystal Reports environment.

This chapter contains an overview of PL/SQL, which is the Oracle embedded programming language. It describes PL/SQL code blocks and their structure as well as the creation of functions and procedures, with an emphasis on stored procedures that return reference cursors. These stored procedures can be used as data sources in Crystal Reports. This chapter should be considered an introduction only; further information about developing in PL/SQL to optimize Crystal Reports should be sought in Oracle documentation and other third-party books.

Overview

PL/SQL is a fourth-generation programming language embedded in the Oracle database. Whereas SQL is set oriented, PL/SQL has procedural elements like any modern programming language. A major difference between PL/SQL and a language such as C is that PL/SQL contains few graphical user interface capabilities. The other major difference is that PL/SQL has tight integration with the database, and SQL commands can be executed and manipulated very easily with it. Though PL/SQL can be used for the client tier of an application, this chapter concerns only PL/SQL running on the database server.

PL/SQL fully supports the Oracle SQL implementation, including all datatypes, functions, operators, and pseudo columns. PL/SQL code can be run dynamically or stored in client-side libraries, but I will be discussing PL/SQL functions, stored procedures, and triggers that are compiled and stored in the database. These executables are cached and shared among users. PL/SQL supports object-oriented programming, though that topic is not covered in depth in this book. Oracle supplies numerous prewritten PL/SQL packages which provide many different capabilities that may be used by the programmer if the DBA loads and compiles them.

Prior to Oracle 9i, the parser that was used to parse SQL statements sent directly to the database differed from the parser used to parse SQL statements that were embedded in PL/SQL blocks. Because of these differences, some SQL statements that executed perfectly in a native form failed when called from PL/SQL. The following are some SQL elements that are not supported from PL/SQL in Oracle 8i and earlier. If the desired statement can be rewritten to use dynamic SQL, it should succeed.

• ORDER BY in a subquery
• A SELECT statement as an expression in another SELECT statement field list
• Any analytical functions
• ROLLUP or CUBE
• Cursors in a SELECT statement

Report writers can benefit from using PL/SQL to create stored procedures. Crystal Reports can use the result set of a particular type of stored procedure as a report dataset. In addition, PL/SQL can be used to create functions that can be referenced in Crystal SQL Expressions or SQL Commands.

PL SQL Structures

PL/SQL code is structured in blocks. There are two types of blocks: anonymous blocks are run interactively from an Oracle tool, and Stored Program units are named and stored in the database. There are three types of Stored Program units: procedures, functions, and packages. Procedures and functions are similar to their like-named counterparts in other languages. Packages are collections of procedures and/or functions that allow modularization of code. Triggers are special PL/SQL blocks that fire based on system, table, or view events. Stored Program units belong to specific schemas, just like table objects.

Blocks

PL/SQL blocks have three sections: a declaration section, an executable section, and an exception handling section. The declaration section is where variables are declared and is not required. The executable section contains the statements that will be executed when the block is run; this section is required. The exception section gives the developer a place to handle errors that occur during execution, and is not required. Blocks may be nested in the execution section or exception section, and entire subprograms may be defined in the declaration section.

A PL/SQL block looks like this:

[DECLARE ...] BEGIN ... [EXCEPTION ...] END;

An anonymous block would look just like the preceding and could be run dynamically from an Oracle tool such as SQL*Plus. Extremely limited anonymous blocks can be run from Crystal SQL Commands, as demonstrated in the next chapter. A Stored Program unit would have additional syntax for naming purposes and be stored in the database.

Types

Built-in Oracle SQL types were discussed in previous chapters. PL/SQL has additional types and allows the user to create new types using the TYPE keyword. A type declaration must precede its use in any variable declarations.

Variables

Variables and constants must be declared before they are used in the execution section. Any SQL datatype may be used, as well as additional datatypes that are only available for use with PL/SQL, such as the Boolean type. Complex datatypes such as records and collections are also allowed. PL/SQL variables are declared with the following syntax:

DECLARE VariableName1 datatype [[NOT NULL] DEFAULT expression]; VariableName2 datatype [[NOT NULL] := expression]; ...

Variables may be declared with a default value using either the DEFAULT keyword or the assignment operator as just illustrated. Any variables declared with a default value may also be declared as NOT NULL. A variable cannot be declared as NOT NULL if no default value is supplied.

Entire subprograms may also be defined in the declaration section for use in the execution or exception sections. Subprograms defined in the declaration section of a block are considered local to the block and cannot be called from other blocks.

%TYPE

Two special attributes exist that can be used to define PL/SQL variable datatypes. When creating PL/SQL programs, it is often necessary to create variable types that correspond to existing database column types. The %TYPE attribute can be used to create a variable that has the same type as a database column:

VariableName Table.Column%TYPE

If the type of Table.Column changes, the PL/SQL block will need to be revalidated and may possibly need modifications to account for the changed type. However, the use of %TYPE is recommended because it ensures matching types and can help insulate the PL/SQL code from changes in the database.

%ROWTYPE

%ROWTYPE is similar to %TYPE, but defines an entire row:

VariableName Table%ROWTYPE

%ROWTYPE is often used when declaring the return type for cursors.

Statements

All PL/SQL statements must end with a semicolon. There are two categories of statements: simple assignment statements and more complex control structures.

Assignment

Assignment statements assign a value to a variable. Assignment is done with the assignment operator (:=) via an INTO statement, or with a subprogram parameter. Here is an example using the assignment operator:

VariableName2 := VariableName1+10;

Here is an example using a SELECT INTO statement:

SELECT salary INTO VariableName1 FROM employee WHERE employee_id = VariableName2;

Control Structures

There are several statement types that control program flow within PL/SQL blocks, such as IF-THEN-ELSE, CASE, LOOP, WHILE-LOOP, and FOR-LOOP. The syntax for each control structure is shown.

IF-THEN-ELSE

IF condition THEN statement(s) [ELSIF condition THEN statement(s)] [ELSE statement(s)] END IF;

The ELSE and ELSIF clauses are optional and multiple ELSIF clauses are allowed. IF statements may be nested.

CASE

CASE selector WHEN expression1 THEN statement(s) WHEN expression2 THEN statement(s) ... WHEN expressionN THEN statement(s) [ELSE statement(s)] END CASE;

Searched CASE

CASE WHEN condition1 THEN statement(s) WHEN condition2 THEN statement(s) ... WHEN conditionN THEN statement(s) [ELSE statement(s)] END CASE;

LOOP

LOOP Statement(s) END LOOP;

A LOOP statement must contain an EXIT or RETURN statement somewhere in its statement block or it will execute indefinitely. EXIT can be unconditional or contain a condition as in an EXIT WHEN statement.

WHILE-LOOP

WHILE condition LOOP Statement(s) END LOOP;

FOR-LOOP

FOR counter IN [REVERSE] start_number ... end_number LOOP Statement(s) END LOOP;

Cursors

When any data manipulation statement is executed within PL/SQL (SELECT, INSERT, DELETE, UPDATE), a construct called a cursor is created. The cursor allows the PL/SQL code to interact with the results of the SQL statement. Cursors can be implicitly or explicitly declared. A cursor will be created implicitly for any SQL statement except those statements that return more than one row of data. For multirow result sets, a cursor must be explicitly declared.

Declaring Cursors

Cursors are defined in the declaration section like other PL/SQL variables, but they cannot be used in assignment statements or other expressions like normal variables. The syntax for declaring a cursor is as follows:

CURSOR cursor_name [(cursor_parameter1[, cursor_parameter2, ...])] [RETURN return_type] IS select_statement;

All cursor parameters are considered IN parameters and must state the datatype. Cursor parameters can be given default values.

Opening Cursors

Opening a cursor executes the SELECT statement but does not return the result set. Any parameters that do not have default values must be specified in the open statement:

OPEN cursor_name [(param1_val[, param2_val, ...])]

Fetching from Cursors

Each call to FETCH returns one row from the cursor result set. The cursor attribute %NOTFOUND will return TRUE when no more rows are available. Fetching is commonly done in a LOOP, as shown here:

LOOP FETCH cursor_name INTO variable EXIT WHEN cursor_name%NOTFOUND –Do stuff END LOOP;

The cursor attribute %FOUND will return TRUE as long as there are still unprocessed rows. A cursor can be closed before the end of the result set is reached if desired. If fetching is done past the end of the result set, no exception will be raised.

The cursor attribute %ISOPEN will return TRUE if the cursor is open. The cursor attribute %ROWCOUNT returns the number of rows successfully fetched at the time of the call.

Bulk fetching rather than row-by-row fetching can be done using the BULK COLLECT clause. To fetch using BULK COLLECT, one or more collection variables must be defined to contain the data returned. BULK COLLECT is useful to limit the number of network trips required to return the entire dataset; see Oracle documentation for more information.

Closing Cursors

Once a cursor is closed, it can be reopened with different parameter values. Rows cannot be fetched from a closed cursor. A cursor is closed using the following syntax:

CLOSE Cursor_Name;

Cursor FOR LOOPs

A cursor FOR LOOP is a shortened method of opening, fetching, and closing a cursor. The cursor_rec variable is not declared in the declaration section of the PL/SQL block, but is used only by the cursor loop and is only accessible inside the cursor loop. The syntax is shown next:

FOR cursor_rec IN cursor_name LOOP –do stuff –reference fields as cursor_rec.field_name END LOOP;

To use this syntax, the cursor_name must have been declared previously. Another version of the cursor FOR LOOP that does not require that a cursor be declared explicitly is shown next. In this case, the SELECT statement is defined directly in the FOR LOOP.

FOR cursor_rec IN (select_statement) LOOP –do stuff –reference fields as cursor_rec.field_name END LOOP;

REF CURSORs (Cursor Variables)

A cursor corresponds to the work area that Oracle creates to process the particular SELECT statement defined by the cursor declaration. A cursor variable is a pointer that points to or references a work area. A cursor variable contains the memory location of a cursor work area, much like pointers in other languages. A cursor cannot be passed as a parameter, but a cursor variable or REF CURSOR can.

To use cursor variables, you first create a type of REF CURSOR and then create variables or parameters of that type:

TYPE ref_cursor_type_name REF CURSOR [RETURN return_type];

Cursor variables declared without a RETURN clause are called weakly typed reference cursors; cursor variables declared with a RETURN clause are strongly typed reference cursors. The compiler will return an error if a strongly typed reference cursor is used and the return types do not match.

Opening REF CURSORs

When opening a reference cursor, the SELECT statement to be executed must be supplied.

OPEN cursor_var FOR select_statement;

No parameters can be passed when opening a reference cursor, but the SELECT statement can contain references to PL/SQL variables. A cursor variable can be reopened without first closing it.

Cursor variables can be declared as formal parameters for procedures in a package specification and/or package body, or they can be declared in standalone procedures using reference cursor types declared in other package specifications. They cannot be declared in packages directly.

Transaction Control

Transaction control statements such as COMMIT and ROLLBACK can be executed within a PL/SQL block. COMMIT ends a transaction and commits any database changes. ROLLBACK will undo any changes made since the previous COMMIT. Details about transaction control are outside the scope of this book and are typically not required for creating datasets for use by Crystal Reports. If a stored procedure needs to modify the database in any way, transaction control statements may be needed.

Subprograms

Subprograms are PL/SQL blocks that have been given names and stored in the database as database objects. They can take parameters and be executed if the user has the EXECUTE privilege for them. Subprograms may be declared as standalone routines or as a part of other packages. Subprograms must be the last objects declared in the declaration section of a block; they can be declared with definer rights or invoker rights. There are two types of subprograms: functions, and procedures.

Procedures

A procedure typically performs some action. The syntax for creating a standalone procedure is as follows:

CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter[, parameter]...)] [AUTHID {DEFINER | CURRENT_USER}] {IS | AS} [PRAGMA AUTONOMOUS_TRANSACTION;] [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];

The parameter definition section has the following syntax:

parameter_name [IN | OUT [NOCOPY] | IN OUT [NOCOPY]] datatype [{:= | DEFAULT} expression]

The CREATE PROCEDURE syntax is similar to the syntax for any PL/SQL block. The OR REPLACE clause is used to update a procedure of the same name that already exists. The AUTHID clause defines whether the procedure should execute with the privileges of the owner (or definer) or the user who is calling it. The DECLARE keyword is not used, but declarations are placed between the IS or AS and the BEGIN keyword. Exception handling is done in the EXCEPTION section.

A procedure is called by listing its name, along with any supplied parameter values enclosed in parentheses, followed by a semicolon. If there are no parameters, the parentheses are not used.

Crystal Reports can use a particular type of PL/SQL procedure (one that returns a reference cursor) directly. Additionally, other types of procedures can be called from REF CURSOR stored procedures or by functions that can be used in SQL Expressions, SQL Commands, or stored procedures.

Functions

A function is similar to a procedure and can have parameters. However, a function returns only one value and can be used in expressions similar to variables. Some functions can be used in SQL statements.

Syntax for function creation includes declaring the datatype of the return value:

CREATE [OR REPLACE ] FUNCTION function_name [( parameter [ , parameter ]... ) ] RETURN datatype ...

The RETURN statement must be used somewhere within the body of the function to indicate the value that will be returned from the function. RETURN can be used more than once within the body of the function, but the first RETURN that is encountered during execution will cause the function to end processing and return the value.

For a function to be callable from a Crystal SQL command or SQL Expression, several conditions must be met. Two of the basic conditions are that the function must be callable from a SELECT statement, and it must not modify the database or use any transaction control statements. For other restrictions refer to Oracle documentation.

An Oracle function to convert a timestamp to a date is demonstrated in Chapter 2.

Subprogram Parameters

Declared subprogram parameters cannot have a size constraint. For example, Param1 VARCHAR2 is allowed, but Param1 VARCHAR2(32) is not. If a size constraint is needed, a datatype of the desired size should be created and then used as the type for the parameter instead of using a built-in datatype.

Procedure parameters can be passed using positional notation or named notation, or a mixture of the two. Positional notation assumes that the first value in the call to the procedure should be matched to the first declared parameter for the procedure, and the second to the second, and so forth. Parameters can only be omitted from the call if they are IN parameters, have default values, and are the last parameters in the procedure declaration. Named notation uses the following syntax, where the parameters can be passed in any order. Using named notation, any IN parameters with default values can be omitted from the call.

Procedure_name(parameter_name_a=>parameter_value_a, parameter_name_b=>parameter_value_b, ...)

To use mixed notation, all parameters for which you want to use positional must come first, followed by any named notation parameters.

Functions referenced in Crystal SQL Expressions or SQL Commands must use positional notation. Stored procedure calls can use named notation in situations using pass-through SQL, if the returning REF CURSOR is defined first in the parameter list or before any parameters that will be called with named notation.

When a stored procedure dataset is chosen from the Database Expert, Crystal will generate report parameters and associated prompts for each parameter that exists, in the order they are defined, and it will include a host variable for the REF CURSOR. See the following illustration to see the Show SQL Query dialog box to call the stored procedure using the native driver.

In situations where the developer wishes to hide certain parameter prompts from the user, SQL Command can be used to call the procedure, embedding default values where appropriate. The user will be prompted only for the parameters that the developer specifically defines in the SQL Command.

Suppose you want to develop a report that displays customers in a particular U.S. state and you know that you have a stored procedure that will return customer data based on the country and region. Create a report based on the stored procedure XTREME.REPORTS.CUSTOMERS_IN_REGION_REPORT. You will be prompted to enter two parameters, one for the country and one for the region (see Figure 5-1). For this particular report, the country will always be USA. You could write another procedure specifically for this report that would only have the Region parameter, but that would be a lot of work simply to avoid being prompted for the Country parameter. Another solution, at least with the Crystal Reports ODBC driver, is to put the procedure call in a SQL Command. Either of the following commands will accomplish the goal of eliminating the parameter prompt for Country:

{Call "XTREME"."REPORTS"."CUSTOMERS_IN_REGION_REPORT" ('{?State}', 'USA' )}

Figure 5-1: Parameter prompts for stored procedure

The second call demonstrates the use of named notation and would not be possible if the REF CURSOR were not listed as the first parameter for the procedure:

{Call "XTREME"."REPORTS"."CUSTOMERS_IN_REGION_REPORT" ("IN_COUNTRY" => 'USA', "IN_REGION" => '{?State}') }

Parameters have a mode defined for them. The default mode is IN, meaning that the value is passed into the procedure and inside the procedure the parameter is treated like a constant. The OUT mode is used to pass values back to the calling routine. The value passed into an OUT parameter must be a variable. It cannot be a constant or an expression. If the OUT parameter variable had a value before the subprogram call, it will be overwritten by the subprogram. An OUT parameter is treated like a variable within the subprogram. The third available mode is IN OUT. It is similar to an OUT parameter, but its initial value inside the subprogram is whatever value was passed in the subprogram call.

Subprogram Privileges

To call a standalone subprogram, the current user must either own the subprogram, or have the EXECUTE privilege for that specific subprogram. In addition, the user may require privileges on any objects that are selected from or modified by the subprogram. The AUTHID clause of the subprogram specification determines whether the subprogram runs under the privileges of the owner or the privileges of the current user.

The default value for AUTHID is DEFINER. If a procedure is using definer rights, the schema owner’s privileges are used, no matter which user is calling the subprogram. In this situation, granting the EXECUTE privilege on the subprogram is sufficient to allow the subprogram to execute, assuming a couple of things: first, the owner of the subprogram must have the necessary rights required by the statements in the subprogram granted to him directly (that is, not via any role). Therefore, if the subprogram selects from a table, the owner must have the SELECT privilege on that table granted to his user ID and not given him via a role. Secondly, the owner’s rights must be in effect when the procedure is called. They must not have been revoked at any time since the subprogram was compiled. In addition, under definer rights, any object references that are not fully qualified will be resolved as if the owner of the subprogram was making the references rather than the caller of the subprogram.

The other possible value for AUTHID is CURRENT_USER. The CURRENT_USER is usually the session owner. However, if the user calls a subprogram that uses definer rights, the CURRENT_USER is the owner of that subprogram until that subprogram is finished. Therefore, if that subprogram called another subprogram that used invoker rights, the invoker rights subprogram would consider that the owner of the calling subprogram was the CURRENT_USER, rather than the session owner. It can be very confusing.

When AUTHID is set to CURRENT_USER, the current user, as just defined, needs not only the EXECUTE privilege on the subprogram, but also any other privileges required to execute the statements contained in the subprogram. In this case, the required privileges can be granted via roles and need not be granted directly. In addition, any object references that are not fully qualified will be resolved as if CURRENT_USER was making the reference, not the owner of the subprogram.

Invoker rights can be very powerful in situations where each schema has copies of the application objects. Using subprograms declared with invoker rights enables the use of one set of code for all schemas, doing away with the need to copy code as well as tables to each schema. The choice for AUTHID should be made depending on the desired security scheme for the database and the application structure.

Packages

A PL/SQL package is a collection of variable and type declarations and subprogram definitions. A package has a specification and a body. The specification is the public part of a package and contains declarations of objects that can be shared with other packages, standalone subprograms, or SQL statements. The package body contains declarations for any objects that are private to the package, along with the bodies of all cursors and subprograms, and an initialization section.

You create a package specification using the following syntax:

CREATE [OR REPLACE] PACKAGE package_name [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} [type declarations] [variable declarations] [cursor or subprogram specifications] END;

A package body is created using this syntax.

CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS} [type declarations] [variable declarations] [cursor or subprogram bodies] BEGIN [initialization statements] END;

To call a subprogram that is stored in a package, you must prefix the subprogram name with the package name and possibly the schema name.

Cursors, procedures, and functions are the only object types that require any code in the package body. If no cursors, procedures, or functions are declared in the package specification, and no initialization is required, no package body needs to be created. Cursors and subprograms that need to be public must have their specifications in the package specification and their bodies in the package body. Cursors and subprograms that can be private to the package do not need a specification in the package specification.

Cursor Specifications

Cursors are declared in the specification part of a package without their defining SELECT statement. The entire cursor declaration, including the SELECT statement, must be given in the package body. This allows for changes to the SELECT statement without requiring any changes to the package specification. However, the return types must match between the package specification and the package body. Another advantage of declaring cursors in a package specification is that they can be used by other program units.

Subprogram Specifications

Subprograms are declared in the package specification by stating their name and giving the parameters and return datatypes. The subprogram specification will be identical to the subprogram body except that it is truncated before the IS or AS. The specification gives the information necessary to call the subprogram, but none of the actual implementation steps. If a subprogram does not need to be public, it does not need a specification in the package specification. It can just be defined in the declaration section of the package body.

SQL in PL SQL

Most SQL data manipulation statements can be used with little or no modification in PL/SQL. INSERT, UPDATE, and DELETE statements can be executed directly in PL/SQL blocks. To use SELECT statements in PL/SQL, a provision must be made to store or process the returned rows. The use of cursors and cursor variables for this purpose is discussed in the preceding sections. Another method is to use the INTO clause of the SELECT statement to store the returned values in another structure.

Other types of statements cannot be executed directly in PL/SQL blocks. These statements include data definition statements such as CREATE, data control statements such as GRANT, and session control statements such as ALTER SESSION.

Native Dynamic SQL

PL/SQL has two methods for allowing dynamic execution of SQL statements. The older method relies on a built-in PL/SQL package called DBMS_SQL, and we will not be discussing it. The newer method is called Native Dynamic SQL and will be covered briefly. There are two general situations where Native Dynamic SQL is useful, for execution of data definition, data control, session control statements and programmatically created SQL statements.

PL/SQL lets you execute data manipulation SQL statements, such as SELECT, INSERT, UPDATE, and DELETE, but you cannot execute data definition statements such as CREATE or DROP, grant privileges, or execute session control statements. Native Dynamic SQL is the solution. In some situations, it is desirable to construct a SQL statement at runtime. EXECUTE IMMEDIATE can be used to run any SQL string, whether given as a constant, or constructed programmatically.

A call to the Native Dynamic SQL command EXECUTE IMMEDIATE uses the following syntax:

EXECUTE IMMEDIATE dynamic_string [INTO {define_variable[, define_variable]... | record}] [USING [IN | OUT | IN OUT] bind_argument [, [IN | OUT | IN OUT] bind_argument]...] [{RETURNING | RETURN} INTO bind_argument[, bind_argument]...];

Dynamic_string must be a valid SQL statement or PL/SQL block. If it is a SQL statement, omit the ending semicolon. If it is a PL/SQL block, include the ending semicolon. The INTO clause is optional and allows you to output results of the query into variables or PL/SQL records. Dynamic_string can be written to include bind variables. To populate the bind variable, use the USING clause. The RETURNING INTO clause is used when the SQL statement contains a RETURNING clause.

EXECUTE IMMEDIATE cannot return more than one row. If you need to execute dynamic SQL that has a multirow result set, you must use the dynamic form of the OPEN-FOR statement.

Native Dynamic SQL statements can be used in stored procedures that return datasets to Crystal Reports.

REF CURSOR Stored Procedures

PL/SQL cursors are objects that are used to process the rows returned by a query. A REF CURSOR is a pointer to a cursor. When a cursor is opened, the associated query is executed. Subsequent fetch commands will retrieve each of the individual rows of the query result set. Closing a cursor disables it and frees the associated resources. Stored procedures that return cursor variables or REF CURSORs can be used as Crystal Reports datasets and will appear in the Database Expert when the Stored Procedures database option is checked for a database.

When using Oracle stored procedures as Crystal Reports datasets, several conditions must be met. The following list was compiled using the Crystal Decisions paper entitled “Oracle Stored Procedures and Crystal Reports” as a starting point. Additional conditions and other clarifications have been added.

Stored Procedure Requirements

1. The REF CURSOR type to be used in the stored procedure must be declared in a package prior to the declaration of the stored procedure. For Crystal Reports 9, this can be in the same package where the stored procedure is declared or in a separate package and can be either a weakly or a strongly typed REF CURSOR. Stored procedures declared inside of packages will appear under the “Qualifiers” folder of the Database Expert for the native driver and under the “Stored Procedures” folder for the ODBC drivers.
2. The stored procedure must contain a parameter of type REF CURSOR.
3. The REF CURSOR must be defined as an IN OUT parameter.
4. Any other parameters must be defined as IN only.
5. The body of the stored procedure must open the REF CURSOR. The REF CURSOR must open successfully. Crystal Reports can then fetch the records.
6. The stored procedure can have only one REF CURSOR parameter. The structure of the cursor record cannot change based on an IN parameter, even if the REF CURSOR is weakly typed.
7. The REF CURSOR must not be fetched from or closed after it is opened in the body of the stored procedure.
8. If using ODBC or OLE DB, only some drivers will work under certain conditions. Refer to Table 1-1 for details.
9. When using an ODBC driver, you must check the Procedure Returns Results checkbox on the ODBC Advanced tab for the driver.
10. Avoid using variable names or parameter names that are identical to database objects. Oracle 9i handles the distinction better than Oracle 8i, but it is best to avoid any possibility of confusion.
11. Use simple parameter types such as strings, numbers, or dates. Crystal cannot understand complex types and will not pass them properly.

A REF CURSOR type must be created before it can be used in a stored procedure. A REF CURSOR can be weakly typed or strongly typed. A weakly typed REF CURSOR is one that does not declare the record type of the cursor it references and hence can be used to reference cursors of different types at different times. A strongly typed REF CURSOR declares the record type of the cursor it references, and exceptions will be raised if it is used with an inconsistent cursor type.

When using the Crystal ODBC or Crystal ODBC WP driver, the Procedure Returns Results checkbox on the Advanced tab must be checked to enable the fetching of the dataset rows (see Figure 5-2).

Figure 5-2: ODBC Advanced tab for Crystal Oracle ODBC

Crystal Reports 9 supports access to Oracle stored procedures inside of packages. In earlier versions, the stored procedures had to be declared outside of Oracle packages as standalone procedures. When using the native driver, standalone stored procedures appear under the Stored Procedures folder, but packaged stored procedures appear under the Qualifiers folder under a folder representing the package name, as shown in Figure 5-3.

Figure 5-3: Database Expert list for packaged procedures with native driver

When using ODBC drivers, the Database Expert lists the stored procedures differently than it does with the native driver. There is no Qualifiers folder. Stored procedures defined in packages are listed under the Stored Procedures folder with the package name appended to the procedure name (see Figure 5-4).

Figure 5-4: Database Expert list for packaged procedures with ODBC driver

Simple Stored Procedure Example

The demonstration that follows uses a strongly typed REF CURSOR and places the stored procedure in a package. The package specification is as follows:

CREATE OR REPLACE PACKAGE XTREME.SUPPLIER_RPTS AS TYPE Supp_Rpt_Rec_Type IS RECORD (Supplier_ID Supplier.Supplier_ID%TYPE, Supplier_Name Supplier.Supplier_Name%Type, Country Supplier.Country%Type, Phone Supplier.Phone%Type, AUSID number); TYPE Supp_Rpt_Type IS REF CURSOR RETURN Supp_Rpt_Rec_Type; PROCEDURE Supplier_Report (Supp_Rpt_Cur IN OUT Supp_Rpt_Type, Supplier_Par IN Supplier.Supplier_Id%TYPE); END SUPPLIER_RPTS;

The package body is as follows:

CREATE OR REPLACE PACKAGE BODY XTREME.SUPPLIER_RPTS AS PROCEDURE Supplier_Report (Supp_Rpt_Cur IN OUT Supp_Rpt_Type, Supplier_Par IN Supplier.Supplier_Id%TYPE) IS BEGIN OPEN Supp_Rpt_Cur for SELECT Supplier_ID, Supplier_Name, Country, Phone, userenv('sessionid') AUSID FROM Supplier WHERE Supplier_ID=Supplier_Par; END Supplier_Report; END SUPPLIER_RPTS;

Execute the preceding code from SQL*Plus to create the stored procedure if it is not already created in the XTREME schema.

To create a report using this stored procedure, start a new report and connect to the database as the XTREME user. For other users to be allowed to use this stored procedure, they must be granted the EXECUTE privilege for the procedure and use the native driver or call the procedure from a SQL Command. Under the XTREME schema, open the Qualifiers folder. You will see the SUPPLIER_REPORT stored procedure listed under Qualifiers, then under SUPPLIER_RPTS. Select Supplier_Report.

Because this stored procedure has an input parameter, you will be prompted for a discrete value for the Supplier_Par, as shown in Figure 5-5. Enter 2 and click OK. Finish the report as usual.

Figure 5-5: Parameter prompts for stored procedure parameters

Much more complex stored procedures, capable of almost any processing need, can also be created. Stored procedures are particularly useful where procedural processing is required or where parsing must be kept to a minimum.

Privileges for REF CURSOR Stored Procedures

The owner of a stored procedure automatically has EXECUTE privileges on it. Any other user who needs to execute the stored procedure must be granted the EXECUTE privilege for it, assuming that the procedure uses definer rights. See “Subprogram Privileges” earlier in this chapter for more information about required privileges and definer versus invoker rights. The ODBC drivers do not currently display stored procedures defined in schemas other than the schema of the logged in user, so they are not directly accessible as datasets. However, stored procedures owned by other users can be called using a SQL Command.

Stored Procedure Parameters and Crystal

Stored procedures that are intended to be used with Crystal Reports should contain only simple parameter types as Crystal will not properly interpret any complex types. String, number, and date types work well. If you choose a stored procedure as a data source for a report, Crystal will create parameters matching the procedure parameters. In the report designer, you can select those parameters and fine-tune their properties in simple ways: you can add a prompting string and set a default value, but you cannot make the parameter be a range or allow multiple values. You have similar options if you are calling the stored procedure from a SQL Command and creating your own parameters.

Multivalued Parameters

It is often desirable to allow the user to choose multiple values for a parameter. In a Crystal Report created by picking tables and letting Crystal generate the SQL query, this is possible. Crystal will allow you to create a parameter field that can accept multiple values. It then adds a list to the WHERE clause similar to the following:

WHERE datafield = parameter value 1 OR datafield = parameter value 2 OR datafield = parameter value 3 ...

When using stored procedures, multivalued Crystal parameter fields cannot be used.

If you programmatically call reports, you may be able to work around this problem using your development language. You could, for instance, populate a table with the values and base your report on a join between that table and the actual data tables. In this situation, you would have to maintain the parameter table via your application, and the report would be useless if run outside of the application.

Another option is to pass a comma-delimited string as one parameter and parse it for use in the stored procedure. The following examples demonstrate two different methods using this general idea. The drawback to this solution is that a very long string may be required, and the report user must input a comma-delimited list of values.

The first option uses a temporary table. This table is in the XTREME schema and is defined as shown:

CREATE GLOBAL TEMPORARY TABLE NUMBER_PARAM_TEMP (NUMBER_PARAM NUMBER(10) NOT NULL, CONSTRAINT NUMBER_PARAM_TEMP_PK PRIMARY KEY(NUMBER_PARAM) USING INDEX) ON COMMIT PRESERVE ROWS;

This table will hold the parameter values. A function has been created in the TOOLS package that will parse a comma-delimited string and insert the values into the temporary table. The function is called PARAM_TO_NUMBER_PARAM_TEMP. A stored procedure using this approach has been created called REPORTS.SUPPLIER_MULTI_REPORT_1. The body of the procedure is as shown:

PROCEDURE Supplier_Multi_Report_1 (Supp_Rpt_Cur IN OUT Supp_Rpt_Type, SupplierVar IN VARCHAR2) IS BEGIN TOOLS.PARAM_TO_NUMBER_PARAM_TEMP(SupplierVar); OPEN Supp_Rpt_Cur FOR SELECT Supplier_ID, Supplier_Name, Country, Phone, userenv('sessionid') AUSID FROM Supplier, Number_Param_Temp WHERE Supplier.Supplier_id=Number_Param_Temp.Number_Param; END Supplier_Multi_Report_1;

Create a report using this procedure and enter a comma-delimited string of values, such as “1, 2, 4” at the parameter prompt. You should see rows in the report for the Supplier_IDs of 1, 2, and 4.

The second option uses a table type. The table type has been defined in the XTREME schema as follows:

CREATE TYPE "XTREME"."NUMBER_PARAM_TABLE_TYPE" AS TABLE OF NUMBER;

A function has been created in the TOOLS package called PARAM_TO_NUM_PARAM_TABLE_TYPE that will accept a comma-delimited string and return a variable of the Number_Param_Table_Type type. A stored procedure using this approach has been created called REPORTS.SUPPLIER_MULTI_REPORT_2. The body of the procedure for Oracle 9i is as shown:

PROCEDURE Supplier_Multi_Report_2 (Supp_Rpt_Cur IN OUT Supp_Rpt_Type, SupplierVar IN VARCHAR2) IS BEGIN OPEN Supp_Rpt_Cur for SELECT Supplier_ID, Supplier_Name, Country, Phone, userenv('sessionid') AUSID FROM Supplier WHERE Supplier_id IN (Select * FROM TABLE(TOOLS.PARAM_TO_NUM_PARAM_TABLE_TYPE(SupplierVar))); END Supplier_Multi_Report_2;

For Oracle 8i, the WHERE clause should be changed to

WHERE Supplier_id IN (SELECT * from THE (SELECT CAST(TOOLS.PARAM_TO_NUM_PARAM_TABLE_TYPE(SupplierVar) AS NUMBER_PARAM_TABLE_TYPE) FROM DUAL));

Create a report using Supplier_Multi_Report_2 and enter a comma-delimited string at the parameter prompt. The results should be identical to option 1.

Using Temporary Tables

Temporary tables are often used in stored procedures to store intermediate results, or even the final result of complex processing. Temporary tables are created much like regular tables, except that the keywords GLOBAL TEMPORARY are inserted between CREATE and TABLE in the data definition statement, as shown:

CREATE GLOBAL TEMPORARY TABLE table_name ... [ON COMMIT {PRESERVE|DELETE} ROWS]

Once a temporary table is created, its definition persists until it is dropped, just as with an ordinary table. A temporary table is different from an ordinary table in the treatment of its data. Rows in a temporary table are visible only to the session that inserted them and only until the end of the current transaction or session depending on the ON COMMIT clause. If the temporary table is defined with the ON COMMIT PRESERVE directive, the data will persist for the session. Otherwise, the data will persist for the duration of the current transaction. Multiple users can use the same temporary table and will not see each other’s data.

The example in the previous section for multivalued parameters also demonstrates the use of a temporary table.

Calling Stored Procedures from SQL Commands

Stored procedures may be called from Crystal Reports SQL Commands, except when using the native driver. For some of the database drivers, stored procedures do not show up in the Database Expert or cannot be used successfully when picked from the Database Expert, but they do succeed when called from a SQL Command. Refer back to Table 1-1 (in Chapter 1) for details. Enter a statement similar to one of the following to call a procedure from a SQL Command:

{CALL ProcSchema.ProcPackage.ProcName(Param1, Param2,...)}

or

BEGIN ProcSchema.ProcPagckage.ProcName(Param1, Param2, ...); END;

The REF CURSOR parameter should not be listed. For procedures, which have parameters other than the REF CURSOR parameter, create the parameters using the Create button. Positional or named notation can be used for the parameter calls. If using named notation, the REF CURSOR parameter must be listed before any parameters using named notation.

For your stored procedure, connect to XTREME using the Crystal Oracle ODBC driver or the Crystal WP Oracle ODBC driver and choose the Add Command option. You need a number parameter for the SUPPLIER_ID. Click Create, enter Supplier as the parameter name, and choose Number for the value type, as shown next.

Enter the following call as the SQL Command:

{Call Supplier_Rpts.Supplier_Report({?Supplier})}

To embed the parameter name, put the cursor where the parameter needs to be inserted and double-click the parameter name. It will be inserted for you; click OK. You will be prompted to input a value for the parameter and then you will be returned to the Database Expert with the Command added as a data source to your report.

Procedures called by using an anonymous block (BEGIN … END) can execute multiple PL/SQL statements. This functionality is not documented and hence not necessarily reliable. Experimentation shows that statements can be added after the call to the REF CURSOR stored procedure, but not before. For example, if you wanted to insert a record into an audit table when the report is run, you could add an INSERT statement after the stored procedure call, as shown:

BEGIN Reports.Supplier_Report({?Supplier}); INSERT INTO RPT_AUDIT Values('Supplier_Report', SYSDATE); END;

Because the stored procedure merely opens a cursor, any statements inserted here will happen after the cursor is opened but before any records are fetched from the cursor.

It’s advisable to add any necessary processing to the stored procedure itself and not rely on this feature, especially because this type of call is not allowed when using the native driver and report users may not have the necessary privileges.

Stored Procedure Myths

There are many misconceptions about what can and cannot be done in a stored procedure that will be used by Crystal Reports. Many conditions can affect the capabilities of a stored procedure, such as driver and executable versions, so what may work in one situation may not work in another. This section clarifies some of those issues for the native and Crystal ODBC drivers as configured in Chapter 1.

The Crystal Decisions document, “Oracle Stored Procedures and Crystal Reports,” states that the stored procedure cannot call another stored procedure. However, this restriction is not strictly true. Another procedure can be called either before or after the opening of the REF CURSOR, but any statements after the opening of the REF CURSOR, whether they call other stored procedures or not, must not access the records in the REF CURSOR, unless they reopen the REF CURSOR later. An OPEN statement for the REF CURSOR must be the last statement that manipulates the REF CURSOR so that Crystal Reports can start fetching the first record.

Anything can be done in the stored procedure, including any data manipulation, even updating and deleting, as long as the REF CURSOR is treated as just discussed.

The position of the REF CURSOR parameter in the parameter list does not matter. It does not need to be the first parameter or the last parameter; it can be anywhere in the list. There is an exception to this, however, when calling stored procedures from SQL Commands using named notation. If you use named notation, the REF CURSOR parameter is still referenced with positional notation and so must come before the parameters using named notation.

The EXECUTE IMMEDIATE PL/SQL directive can be used in stored procedures for Crystal Reports. EXECUTE IMMEDIATE is used to accomplish actions that are otherwise not allowed from PL/SQL, such as Data Definition Language statements (DDL), or to run a statement that is created programmatically.

Temporary tables can be created in stored procedures. They can be manipulated in stored procedures whether or not they are created in the procedure. If you are reporting from a REF CURSOR stored procedure, you must still create a REF CURSOR, even if it does nothing but SELECT * from the temporary table. Note that regular tables can also be created and/or dropped in stored procedures, but using true temporary tables is more efficient, and there is no need to drop them.

Both weakly and strongly bound REF CURSORS are usable with the native or Crystal Oracle ODBC drivers, both regular and Wire Protocol. There may be problems with other drivers (see Table 1-1 in Chapter 1) or with earlier versions of Crystal (check version numbers in Table 1-1), but if you are using Crystal 9 and are up-to-date with hot fixes and/or service packs, you should have no problems.

Issues with Stored Procedures

Database Driver Behavior

Access to stored procedures varies depending on the database driver you are using. See Table 1-1 in Chapter 1 for details. Direct access to stored procedures (defined as picking a stored procedure as a data source in the Database Expert) whether in packages or standalone, currently works for the native driver, the Crystal Reports ODBC driver, and the Crystal Reports Wire Protocol ODBC driver. However, stored procedures defined in schemas other than the schema of the logged-in user are only accessible using the native driver and the Microsoft OLE DB driver for Oracle.

Access to stored procedures via SQL Commands currently works for the Crystal Reports ODBC driver, the Crystal Reports Wire Protocol ODBC driver, the Oracle ODBC driver, and the Microsoft OLE DB driver. This access is available regardless of who owns the stored procedure, as long as the proper EXECUTE privileges have been assigned.

Columns Without Aliases

Some drivers (MS OLE DB) require that any expressions in the SELECT list of the REF CURSOR have aliases. This is good coding practice anyway, so make sure that every column in the SELECT list either is a simple database field or has an alias defined for it.

REF CURSOR Not Opened

If the REF CURSOR is not opened in the stored procedure, or if it is closed after it has been opened, an ORA-24338 statement handle not executed will result.

Linking

When using stored procedures, it is advisable to return all required fields, rather than attempting to link the REF CURSOR stored procedure to another table, view, or stored procedure. Oracle does not directly support linking stored procedure results to other objects, and Crystal Reports’ behavior in this situation could be problematic. If you link the results of a stored procedure to another table or view, all JOIN processing will occur locally, and you will receive the warning shown here.

When using REF CURSOR stored procedures as datasets, you should return everything that the report needs in the stored procedure, exclusive of data required by any subreports.

Stored Procedures in Subreports

Be wary of basing subreports on stored procedures, particularly if the subreport will be placed in the detail section. If the subreport is standalone, meaning it is not linked to the main report, there should be no issue. If the subreport will be linked to the main report, be sure that each field used in the linking is also a parameter for the stored procedure. This will ensure that each execution of the subreport returns only the data needed for that instance and is not relying on the linking to do the record filtering.

For example, assume that a stored procedure exists that returns all employee IDs and their photos and has no input parameters. You have a main report that lists employees, and you use this stored procedure to create a subreport to display their photos, placing it in the detail section and linking it on the employee ID. In this case, for each employee, the stored procedure will execute, return ALL employee photos, and Crystal will filter the subreport dataset to only the current employee. This will result in much repetitive data being returned and discarded for each employee.

The solution is to modify the stored procedure to accept the employee ID as a parameter. When you define the links for the subreport, be sure to select the actual stored procedure parameter in the Subreport Parameter Field to Use box, not a Crystal generated parameter starting with “?PM.”

Moving Procedures

Stored procedures are often developed in one schema and then moved to a different schema for production use. If a report has been created that references a stored procedure and that stored procedure is subsequently moved, it is simple to modify the report. Verify that the report users have been granted EXECUTE privileges on the new procedure and use the Crystal Database/Set Datasource Location to point to the new location.

Procedure/Package Invalidation

Be aware that Oracle procedures, functions, and packages can become invalid. This can happen because of changes in database structure or changes to the PL/SQL code. In many cases, the objects just need to be recompiled and will then be usable. If you have a procedure that remains invalid after recompilation, it may indicate a true syntax error in the code or a significant change to another database object used in the procedure.

Stored procedures can be extremely powerful tools in the report writer’s toolbox. This chapter has served as an overview of PL/SQL and REF CURSOR stored procedures and their use in Crystal Reports. Chapter 6 will demonstrate further optimization techniques that can be employed using combinations of Crystal Reports functionality and Oracle features.