Indicator variables and stored procedures

variables and stored procedures

Indicator variables can be used with the CALL statement, provided host variables are used for the parameters, to pass additional information to and from the procedure. Indicator variables are the SQL standard means of denoting that the associated host variable should be interpreted as containing the null value, and this is their primary use.

To indicate that an associated host variable contains the null value, the indicator variable, which is a two-byte integer, is set to a negative value. A CALL statement with indicator variables is processed as follows:

* If the indicator variable is negative, this denotes the null value. A default value is passed for the associated host variable on the CALL and the indicator variable is passed unchanged.
* If the indicator variable is not negative, this denotes that the host variable contains a non-null value. In this case, the host variable and the indicator variable are passed unchanged.

These rules of processing are the same for input parameters to the procedure as well as output parameters returned from the procedure. When indicator variables are used with stored procedures, the correct method of coding their handling is to check the value of the indicator variable first before using the associated host variable.

The following example illustrates the handling of indicator variables in CALL statements. Notice that the logic checks the value of the indicator variable before using the associated variable. Also note the method that the indicator variables are passed into procedure PROC1 (as a third argument consisting of an array of two-byte values).

Assume a procedure was defined as follows:

CREATE PROCEDURE PROC1
(INOUT DECIMALOUT DECIMAL(7,2), INOUT DECOUT2 DECIMAL(7,2))
EXTERNAL NAME LIB1.PROC1 LANGUAGE RPGLE
GENERAL WITH NULLS)


Figure 5. Handling of Indicator Variables in CALL Statements

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Program CRPG
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
D INOUT1 S 7P 2
D INOUT1IND S 4B 0
D INOUT2 S 7P 2
D INOUT2IND S 4B 0
C EVAL INOUT1 = 1
C EVAL INOUT1IND = 0
C EVAL INOUT2 = 1
C EVAL INOUT2IND = -2
C/EXEC SQL CALL PROC1 (:INOUT1 :INOUT1IND , :INOUT2
C+ :INOUT2IND)
C/END-EXEC
C EVAL INOUT1 = 1
C EVAL INOUT1IND = 0
C EVAL INOUT2 = 1
C EVAL INOUT2IND = -2
C/EXEC SQL CALL PROC1 (:INOUT1 :INOUT1IND , :INOUT2
C+ :INOUT2IND)
C/END-EXEC
C INOUT1IND IFLT 0
C* :
C* HANDLE NULL INDICATOR
C* :
C ELSE
C* :
C* INOUT1 CONTAINS VALID DATA
C* :
C ENDIF
C* :
C* HANDLE ALL OTHER PARAMETERS
C* IN A SIMILAR FASHION
C* :
C RETURN
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
End of PROGRAM CRPG
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Program PROC1
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
D INOUTP S 7P 2
D INOUTP2 S 7P 2
D NULLARRAY S 4B 0 DIM(2)
C *ENTRY PLIST
C PARM INOUTP
C PARM INOUTP2
C PARM NULLARRAY
C NULLARRAY(1) IFLT 0
C* :
C* INOUTP DOES NOT CONTAIN MEANINGFUL DATA
C*
C ELSE
C* :
C* INOUTP CONTAINS MEANINGFUL DATA
C* :
C ENDIF
C* PROCESS ALL REMAINING VARIABLES
C*
C* BEFORE RETURNING, SET OUTPUT VALUE FOR FIRST
C* PARAMETER AND SET THE INDICATOR TO A NON-NEGATIV
C* VALUE SO THAT THE DATA IS RETURNED TO THE CALLING
C* PROGRAM
C*
C EVAL INOUTP2 = 20.5
C EVAL NULLARRAY(2) = 0
C*
C* INDICATE THAT THE SECOND PARAMETER IS TO CONTAIN
C* THE NULL VALUE UPON RETURN. THERE IS NO POINT
C* IN SETTING THE VALUE IN INOUTP SINCE IT WON'T BE
C* PASSED BACK TO THE CALLER.
C EVAL NULLARRAY(1) = -5
C RETURN
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
End of PROGRAM PROC1
+++++++++++++++++++++++++++++++++++++++++++++++++++

Returning Result Sets from Stored Procedures

Result Sets from Stored Procedures

In addition to returning output parameters, stored procedures have a feature by which a result table associated with a cursor opened in the stored procedure (called a result set) can be returned to the application issuing the CALL statement. That application can then issue fetch requests to read the rows of the result set cursor. Whether a result set gets returned depends on the returnability attribute of the cursor. The cursor's returnability attribute can be explicitly given in the DECLARE CURSOR statement or it can be defaulted. The SET RESULT SETS statement also allows for an indication of where the result sets should be returned (see Example 2: Call a stored procedure which returns a result set from a nested procedure). By default, cursors which are opened in a stored procedure are defined to have a returnability attribute of RETURN TO CALLER. To return the result set associated with the cursor to the application which called the outermost procedure in the call stack, the returnability attribute of RETURN TO CLIENT is specified on the DECLARE CURSOR statement. This will allow inner procedures to return result sets when the application calls nested procedures. For cursors whose result sets are never to be returned to caller or client, the returnability attribute of WITHOUT RETURN is specified on the DECLARE CURSOR statement.

There are many cases where opening the cursor in a stored procedure and returning its result set provides advantages over opening the cursor directly in the application. For instance, security to the tables referenced in the query can be adopted from the stored procedure so that users of the application do not need to be granted direct authority to the tables. Instead, they are given authority to call the stored procedure, which is compiled with adequate authority to access the tables. Another advantage to opening the cursors in the stored procedure is that multiple result sets can be returned from a single call to the stored procedure, which can be more efficient that opening the cursors separately from the calling application. Additionally, each call to the same stored procedure may return a different number of result sets, providing some application versatility.

The interfaces that can work with stored procedure result sets include JDBC, CLI, and ODBC. An example on how to use these API interfaces for working with stored procedure result sets is included in the following examples:

* Example 1: Call a stored procedure which returns a single result set
* Example 2: Call a stored procedure which returns a result set from a nested procedure

Note:
See Code disclaimer information for information pertaining to code examples.

Invoking a Stored Procedure

Invoking a stored procedure

The SQL CALL statement calls a stored procedure. On the CALL statement, the name of the stored procedure and any arguments are specified. Arguments may be constants, special registers, or host variables. The external stored procedure specified in the CALL statement does not need to have a corresponding CREATE PROCEDURE statement. Programs created by SQL procedures can only be called by invoking the procedure name specified on the CREATE PROCEDURE statement.

Although procedures are system program objects, using the CALL CL command will not typically work to call a procedure. The CALL CL command does not use the procedure definition to map the input and output parameters, nor does it pass parameters to the program using the procedure's parameter style.

There are three types of CALL statements which need to be addressed since DB2® SQL for iSeries has different rules for each type. They are:

* Embedded or dynamic CALL statement where a procedure definition exists
* Embedded CALL statement where no procedure definition exists
* Dynamic CALL statement where no CREATE PROCEDURE exists

Note:
Dynamic here refers to:

* A dynamically prepared and executed CALL statement
* A CALL statement issued in an interactive environment (for example, through STRSQL or Query Manager)
* A CALL statement executed in an EXECUTE IMMEDIATE statement.

Following is a discussion of each type.

* Using CALL statement where procedure definition exists
* Using embedded CALL statement where no procedure definition exists
* Using Embedded CALL statement with an SQLDA
* Using dynamic CALL statement where no CREATE PROCEDURE exists

Additionally, you can find more examples at Examples of CALL statements.

Defining an External Procedure

External Procedure

The CREATE PROCEDURE statement for an external procedure:

* Names the procedure
* Defines the parameters and their attributes
* Gives other information about the procedure which the system uses when it calls the procedure.

Consider the following example:

    CREATE PROCEDURE P1
     (INOUT PARM1 CHAR(10))
     EXTERNAL NAME MYLIB.PROC1
     LANGUAGE C
     GENERAL WITH NULLS

This CREATE PROCEDURE statement:

* Names the procedure P1
* Defines one parameter which is used both as an input parameter and an output parameter. The parameter is a character field of length ten. Parameters can be defined to be type IN, OUT, or INOUT. The parameter type determines when the values for the parameters get passed to and from the procedure.
* Defines the name of the program which corresponds to the procedure, which is PROC1 in MYLIB. MYLIB.PROC1 is the program which is called when the procedure is called on a CALL statement.
* Indicates that the procedure P1 (program MYLIB.PROC1) is written in C. The language is important since it impacts the types of parameters that can be passed. It also affects how the parameters are passed to the procedure (for example, for ILE C procedures, a NUL-terminator is passed on character, graphic, date, time, and timestamp parameters).
* Defines the CALL type to be GENERAL WITH NULLS. This indicates that the parameter for the procedure can possibly contain the NULL value, and therefore will like an additional argument passed to the procedure on the CALL statement. The additional argument is an array of N short integers, where N is the number of parameters that are declared in the CREATE PROCEDURE statement. In this example, the array contains only one element since there is only parameter.

It is important to note that it is not necessary to define a procedure in order to call it. However, if no procedure definition is found, either from a prior CREATE PROCEDURE or from a DECLARE PROCEDURE in this program, certain restrictions and assumptions are made when the procedure is called on the CALL statement. For example, the NULL indicator argument cannot be passed. See Using embedded CALL statement where no procedure definition exists for an example of a CALL statement without a corresponding procedure definition.

A cursor is a work area for a SQL statement that returns one or more rows. It allows you to fetch rows from the result set one by one. Cursors aren't particularly difficult to use, but to return a result set from a PL/SQL stored procedure, you must use a cursor variable. Cursor variables are basically pointers to cursors, and you use them to pass around references to cursors, such as a parameter to a stored procedure. The PL/SQL type of a cursor variable is REF CURSOR.

To use cursor variables, you must define a REF CURSOR type. This type can be strong or weak. Strong types are tied to a specific set of a table's columns. This means a strong REF CURSOR type can only be used with queries (i.e. cursors) returning those columns. Weak types can refer to any cursor, and so are much more flexible. They do, however, sacrifice type safety.

The easiest way forward is to define a weakly typed REF CURSOR in a PL/SQL package. Packages are used in PL/SQL for partitioning functionality. Below, a weak REF CURSOR type called REF_CURSOR is defined in a PL/SQL package called types.


CREATE OR REPLACE PACKAGE types
AS
TYPE ref_cursor IS REF CURSOR;
END;


This definition can now be used in all stored procedures to declare a variable of type REF CURSOR.

We can now write a stored procedure that returns a REF CURSOR, that in JDBC we can process as a result set.

Assume we start from a table defined as below.

     CREATE TABLE STOCK_PRICES(
     RIC VARCHAR(6) PRIMARY KEY,
     PRICE NUMBER(7,2),
     UPDATED DATE )

Here we have a table of stock prices, with the RIC (Reuters Instrument Code) as the primary key. We define a PL/SQL function that simply declares a cursor that returns all columns for stocks below a certain price.

    CREATE OR REPLACE FUNCTION sp_get_stocks(v_price IN NUMBER)
     RETURN types.ref_cursor
     AS
     stock_cursor types.ref_cursor;
     BEGIN
     OPEN stock_cursor FOR
     SELECT ric,price,updated FROM stock_prices
     WHERE price < v_price;

     RETURN stock_cursor;
    END;

These can all be created via the sqlplus command-line tool. Download the SQL script, and start up sqlplus.

C:\>sqlplus username/password

Once in sqlplus, run the script. If you start up sqlplus in the directory the SQL script is in, you can run it using the @ command:

SQL>@stock_prices

You should get an encouraging list of messages such as "1 row created", "Package created", "Function created". If instead you get error messages such as "insufficient privileges", your user must be given additional database privileges. The predefined Oracle roles CONNECT and RESOURCE should be what you need. Login as the system user and run the below:

SQL>GRANT CONNECT, RESOURCE TO username;

Testing the stored procedure

To test the stored procedure, we can either execute it from within an environment that allows you to execute stored procedures and supply their parameters, such as SQL Navigator or Toad, or we can run it from within sqlplus.

Executing a stored procedure from within sqlplus is straightforward once you know how.

Firstly, start up sqlplus and declare a sqlplus variable of type refcursor. This declares a sqlplus variable of type refcursor (a sqlplus type), called "results":

SQL> var results refcursor

Next, execute the procedure, supplying a number as a parameter, and assigning the result into our variable, "results". Note the unusual syntax.

SQL> exec :results := sp_get_stocks(20.0)

PL/SQL procedure successfully completed.

Finally, use the sqlplus print statement to print out the result set

SQL> print results

RIC PRICE UPDATED
------ --------- ---------
AMZN 15.5 21-OCT-01
SUNW 16.25 21-OCT-01
ORCL 14.5 21-OCT-01

If this works successfully, similar to the above, the stored procedure (well, function) is working correctly.
Calling the stored procedure from Java

There are some Oracle-specific tricks to calling the above stored procedure from Java.

The query string can be in the Oracle format or the standard JDBC callable statement syntax. If you are using the Oracle syntax, the query string is:

begin ? := sp_get_stocks(?); end;

If you are using the JDBC syntax, the query string is:

{ call ? := sp_get_stocks(?) }

In each case the spacing is important - the query will not work otherwise.

Secondly, the out parameter must be registered to be of type OracleTypes.CURSOR.

And finally, use getObject() to retrieve the result set.

The code (without error handling) is illustrated below. A more complete code sample can be downloaded from here.

     String query = "begin ? := sp_get_stocks(?); end;";

     CallableStatement stmt = conn.prepareCall(query);

   // register the type of the out param - an Oracle specific type
     stmt.registerOutParameter(1, OracleTypes.CURSOR);

     // set the in param
     stmt.setFloat(2, price);

     // execute and retrieve the result set
     stmt.execute();
     ResultSet rs = (ResultSet)stmt.getObject(1);

     // print the results
     while (rs.next()) {
     System.out.println(rs.getString(1) + "\t" +
     rs.getFloat(2) + "\t" +
     rs.getDate(3).toString());
     }

Hopefully, this code will be all you need to get started on calling your own Oracle stored procedures via JDBC.

Defining an SQL Procedure

SQL Procedure

The CREATE PROCEDURE statement for SQL procedures:

* Names the procedure
* Defines the parameters and their attributes
* Provides other information about the procedure which will be used when the procedure is called
* Defines the procedure body. The procedure body is the executable part of the procedure and is a single SQL statement.

Consider the following simple example that takes as input an employee number and a rate and updates the employee's salary:

    CREATE PROCEDURE UPDATE_SALARY_1
    (IN EMPLOYEE_NUMBER CHAR(10),
    IN RATE DECIMAL(6,2))
    LANGUAGE SQL MODIFIES SQL DATA
    UPDATE CORPDATA.EMPLOYEE
    SET SALARY = SALARY * RATE
    WHERE EMPNO = EMPLOYEE_NUMBER

This CREATE PROCEDURE statement:

* Names the procedure UPDATE_SALARY_1.
* Defines parameter EMPLOYEE_NUMBER which is an input parameter and is a character data type of length 6 and parameter RATE which is an input parameter and is a decimal data type.
* Indicates the procedure is an SQL procedure that modifies SQL data.
* Defines the procedure body as a single UPDATE statement. When the procedure is called, the UPDATE statement is executed using the values passed for EMPLOYEE_NUMBER and RATE.

Instead of a single UPDATE statement, logic can be added to the SQL procedure using SQL control statements. SQL control statements consist of the following:

* an assignment statement
* a CALL statement
* a CASE statement
* a compound statement
* a FOR statement
* a GET DIAGNOSTICS statement
* a GOTO statement
* an IF statement
* an ITERATE statement
* a LEAVE statement
* a LOOP statement
* a REPEAT statement
* a RESIGNAL statement
* a RETURN statement
* a SIGNAL statement
* a WHILE statement

The following example takes as input the employee number and a rating that was received on the last evaluation. The procedure uses a CASE statement to determine the appropriate increase and bonus for the update:

     CREATE PROCEDURE UPDATE_SALARY_2
     (IN EMPLOYEE_NUMBER CHAR(6),
     IN RATING INT)
     LANGUAGE SQL MODIFIES SQL DATA
     CASE RATING
     WHEN 1 THEN
     UPDATE CORPDATA.EMPLOYEE
     SET SALARY = SALARY * 1.10,
     BONUS = 1000
     WHERE EMPNO = EMPLOYEE_NUMBER;
     WHEN 2 THEN
     UPDATE CORPDATA.EMPLOYEE
     SET SALARY = SALARY * 1.05,
     BONUS = 500
     WHERE EMPNO = EMPLOYEE_NUMBER;
     ELSE
     UPDATE CORPDATA.EMPLOYEE
     SET SALARY = SALARY * 1.03,
     BONUS = 0
     WHERE EMPNO = EMPLOYEE_NUMBER;
     END CASE

This CREATE PROCEDURE statement:

* Names the procedure UPDATE_SALARY_2.
* Defines parameter EMPLOYEE_NUMBER which is an input parameter and is a character data type of length 6 and parameter RATING which is an input parameter and is an integer data type.
* Indicates the procedure is an SQL procedure that modifies SQL data.
* Defines the procedure body. When the procedure is called, input parameter RATING is checked and the appropriate update statement is executed.

Multiple statements can be added to a procedure body by adding a compound statement. Within a compound statement, any number of SQL statements can be specified. In addition, SQL variables, cursors, and handlers can be declared.

The following example takes as input the department number. It returns the total salary of all the employees in that department and the number of employees in that department who get a bonus.

    CREATE PROCEDURE RETURN_DEPT_SALARY
     (IN DEPT_NUMBER CHAR(3),
     OUT DEPT_SALARY DECIMAL(15,2),
     OUT DEPT_BONUS_CNT INT)
     LANGUAGE SQL READS SQL DATA
     P1: BEGIN
     DECLARE EMPLOYEE_SALARY DECIMAL(9,2);
     DECLARE EMPLOYEE_BONUS DECIMAL(9,2);
     DECLARE TOTAL_SALARY DECIMAL(15,2)DEFAULT 0;
     DECLARE BONUS_CNT INT DEFAULT 0;
     DECLARE END_TABLE INT DEFAULT 0;
     DECLARE C1 CURSOR FOR
     SELECT SALARY, BONUS FROM CORPDATA.EMPLOYEE
     WHERE WORKDEPT = DEPT_NUMBER;
     DECLARE CONTINUE HANDLER FOR NOT FOUND
     SET END_TABLE = 1;
     DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SET DEPT_SALARY = NULL;
    OPEN C1;
     FETCH C1 INTO EMPLOYEE_SALARY, EMPLOYEE_BONUS;
     WHILE END_TABLE = 0 DO
     SET TOTAL_SALARY = TOTAL_SALARY + EMPLOYEE_SALARY + EMPLOYEE_BONUS;
     IF EMPLOYEE_BONUS > 0 THEN
     SET BONUS_CNT = BONUS_CNT + 1;
     END IF;
     FETCH C1 INTO EMPLOYEE_SALARY, EMPLOYEE_BONUS;
     END WHILE;
     CLOSE C1;
     SET DEPT_SALARY = TOTAL_SALARY;
     SET DEPT_BONUS_CNT = BONUS_CNT;
     END P1

This CREATE PROCEDURE statement:

* Names the procedure RETURN_DEPT_SALARY.
* Defines parameter DEPT_NUMBER which is an input parameter and is a character data type of length 3, parameter DEPT_SALARY which is an output parameter and is a decimal data type, and parameter DEPT_BONUS_CNT which is an output parameter and is an integer data type.
* Indicates the procedure is an SQL procedure that reads SQL data
* Defines the procedure body.
o Declares SQL variables EMPLOYEE_SALARY and TOTAL_SALARY as decimal fields.
o Declares SQL variables BONUS_CNT and END_TABLE which are integers and are initialized to 0.
o Declares cursor C1 that selects the columns from the employee table.
o Declares a continue handler for NOT FOUND, which, when called sets variable END_TABLE to 1. This handler is called when the FETCH has no more rows to return. When the handler is called, SQLCODE and SQLSTATE are reinitialized to 0.
o Declares an exit handler for SQLEXCEPTION. If called, DEPT_SALARY is set to NULL and the processing of the compound statement is terminated. This handler is called if any errors occur, that is, the SQLSTATE class is not '00', '01' or '02'. Since indicators are always passed to SQL procedures, the indicator value for DEPT_SALARY is -1 when the procedure returns. If this handler is called, SQLCODE and SQLSTATE are reinitialized to 0.

If the handler for SQLEXCEPTION is not specified and an error occurs that is not handled in another handler, execution of the compound statement is terminated and the error is returned in the SQLCA. Similar to indicators, the SQLCA is always returned from SQL procedures.
o Includes an OPEN, FETCH, and CLOSE of cursor C1. If a CLOSE of the cursor is not specified, the cursor is closed at the end of the compound statement since SET RESULT SETS is not specified in the CREATE PROCEDURE statement.
o Includes a WHILE statement which loops until the last record is fetched. For each row retrieved, the TOTAL_SALARY is incremented and, if the employee's bonus is more than 0, the BONUS_CNT is incremented.
o Returns DEPT_SALARY and DEPT_BONUS_CNT as output parameters.

Compound statements can be made atomic so if an error occurs that is not expected, the statements within the atomic statement are rolled back. The atomic compound statements are implemented using SAVEPOINTS. If the compound statement is successful, the transaction is committed. For more information about using SAVEPOINTS, see Savepoints.

The following example takes as input the department number. It ensures the EMPLOYEE_BONUS table exists, and inserts the name of all employees in the department who get a bonus. The procedure returns the total count of all employees who get a bonus.

    CREATE PROCEDURE CREATE_BONUS_TABLE
     (IN DEPT_NUMBER CHAR(3),
     INOUT CNT INT)
     LANGUAGE SQL MODIFIES SQL DATA
     CS1: BEGIN ATOMIC
     DECLARE NAME VARCHAR(30) DEFAULT NULL;
     DECLARE CONTINUE HANDLER FOR SQLSTATE '42710'
     SELECT COUNT(*) INTO CNT
     FROM DATALIB.EMPLOYEE_BONUS;
     DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
     SET CNT = CNT - 1;
     DECLARE UNDO HANDLER FOR SQLEXCEPTION
     SET CNT = NULL;
     IF DEPT_NUMBER IS NOT NULL THEN
     CREATE TABLE DATALIB.EMPLOYEE_BONUS
     (FULLNAME VARCHAR(30),
     BONUS DECIMAL(10,2),
     PRIMARY KEY (FULLNAME));
     FOR_1:FOR V1 AS C1 CURSOR FOR
     SELECT FIRSTNME, MIDINIT, LASTNAME, BONUS
     FROM CORPDATA.EMPLOYEE
     WHERE WORKDEPT = CREATE_BONUS_TABLE.DEPT_NUMBER
     DO
     IF BONUS > 0 THEN
     SET NAME = FIRSTNME CONCAT ' ' CONCAT
     MIDINIT CONCAT ' 'CONCAT LASTNAME;
     INSERT INTO DATALIB.EMPLOYEE_BONUS
     VALUES(CS1.NAME, FOR_1.BONUS);
     SET CNT = CNT + 1;
     END IF;
     END FOR FOR_1;
     END IF;
     END CS1

This CREATE PROCEDURE statement:

* Names the procedure CREATE_BONUS_TABLE.
* Defines parameter DEPT_NUMBER which is an input parameter and is a character data type of length 3 and parameter CNT which is an input/output parameter and is an integer data type.
* Indicates the procedure is an SQL procedure that modifies SQL data
* Defines the procedure body.
o Declares SQL variable NAME as varying character.
o Declares a continue handler for SQLSTATE 42710, table already exists. If the EMPLOYEE_BONUS table already exists, the handler is called and retrieves the number of records in the table. The SQLCODE and SQLSTATE are reset to 0 and processing continues with the FOR statement.
o Declares a continue handler for SQLSTATE 23505, duplicate key. If the procedure attempts to insert a name that already exists in the table, the handler is called and decrements CNT. Processing continues on the SET statement following the INSERT statement.
o Declares an UNDO handler for SQLEXCEPTION. If called, the previous statements are rolled back, CNT is set to 0, and processing continues after the compound statement. In this case, since there is no statement following the compound statement, the procedure returns.
o Uses the FOR statement to declare cursor C1 to read the records from the EMPLOYEE table. Within the FOR statement, the column names from the select list are used as SQL variables that contain the data from the row fetched. For each row, data from columns FIRSTNME, MIDINIT, and LASTNAME are concatenated together with a blank in between and the result is put in SQL variable NAME. SQL variables NAME and BONUS are inserted into the EMPLOYEE_BONUS table. Because the data type of the select list items must be known when the procedure is created, the table specified in the FOR statement must exist when the procedure is created.

An SQL variable name can be qualified with the label name of the FOR statement or compound statement in which it is defined. In the example, FOR_1.BONUS refers to the SQL variable that contains the value of column BONUS for each row selected. CS1.NAME is the variable NAME defined in the compound statement with the beginning label CS1. Parameter names can also be qualified with the procedure name. CREATE_BONUS_TABLE.DEPT_NUMBER is the DEPT_NUMBER parameter for the procedure CREATE_BONUS_TABLE. If unqualified SQL variable names are used in SQL statements where column names are also allowed, and the variable name is the same as a column name, the name will be used to refer to the column.

You can also use dynamic SQL in an SQL procedure. The following example creates a table that contains all employees in a specific department. The department number is passed as input to the procedure and is concatenated to the table name.

    CREATE PROCEDURE CREATE_DEPT_TABLE (IN P_DEPT CHAR(3))
     LANGUAGE SQL
     BEGIN
     DECLARE STMT CHAR(1000);
     DECLARE MESSAGE CHAR(20);
     DECLARE TABLE_NAME CHAR(30);
     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
     SET MESSAGE = 'ok';
     SET TABLE_NAME = 'CORPDATA.DEPT_' CONCAT P_DEPT CONCAT '_T';
     SET STMT = 'DROP TABLE ' CONCAT TABLE_NAME;
     PREPARE S1 FROM STMT;
     EXECUTE S1;
     SET STMT = 'CREATE TABLE ' CONCAT TABLE_NAME CONCAT
     '(EMPNO CHAR(6) NOT NULL,
     FIRSTNME VARCHAR(12) NOT NULL,
     MIDINIT CHAR(1) NOT NULL,
     LASTNAME CHAR(15) NOT NULL,
     SALARY DECIMAL(9,2))';
     PREPARE S2 FROM STMT;
     EXECUTE S2;
     SET STMT = 'INSERT INTO ' CONCAT TABLE_NAME CONCAT
     'SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY
     FROM CORPDATA.EMPLOYEE
     WHERE WORKDEPT = ?';
     PREPARE S3 FROM STMT;
     EXECUTE S3 USING P_DEPT;
     END

This CREATE PROCEDURE statement:

* Names the procedure CREATE_DEPT_TABLE
* Defines parameter P_DEPT which is an input parameter and is a character data type of length 3.
* Indicates the procedure is an SQL procedure.
* Defines the procedure body.
o Declares SQL variable STMT and an SQL variable TABLE_NAME as character.
o Declares a CONTINUE handler. The procedure attempts to DROP the table in case it already exists. If the table does not exist, the first EXECUTE fails. With the handler, processing will continue.
o Sets variable TABLE_NAME to 'DEPT_' followed by the characters passed in parameter P_DEPT, followed by '_T'.
o Sets variable STMT to the DROP statement, and prepares and executes the statement.
o Sets variable STMT to the CREATE statement, and prepares and executes the statement.
o Sets variable STMT to the INSERT statement, and prepares and executes the statement. A parameter marker is specified in the where clause. When the statement is executed, the variable P_DEPT is passed on the USING clause.

If the procedure is called passing value 'D21' for the department, table DEPT_D21_T is created and the table is initialized with all the employees that are in department 'D21'.

Define Stored Procedure

Introduction

Stored procedures are precompiled database queries that improve the security, efficiency and usability of database client/server applications. Developers specify a stored procedure in terms of input and output variables. They then compile the code on the database platform and make it available to aplication developers for use in other environments, such as web applications.

All of the major database platforms, including Oracle, SQL Server and MySQL support stored procedures. The major benefits of this technology are the substantial performance gains from precompiled execution, the reduction of client/server traffic, development efficiency gains from code reuse and abstraction and the security controls inherent in granting users permissions on specific stored procedures instead of the underlying database tables.

You would think that returning a JDBC result set from an Oracle PL/SQL stored procedure would be quite straightforward. Unfortunately, it's not as easy as it sounds. But reading this article should tell you all you need to know. Any contributions or corrections welcome - please post on our discussion forum. Sorry, but we can't reply to individual emails.

There are three basic steps. First, you have to write the stored procedure - which in Oracle is a bit tricky if you're not familiar with PL/SQL. Testing it using the Oracle command-line tool sqlplus is also quirky. And calling it via JDBC isn't exactly standard either.
Writing the stored procedure

If you are used to Sybase or SQLServer, returning a result set from a stored procedure is easy. Just finish the procedure with a "select x,y,z from my_table", selecting whatever columns you wish to return.

JVM (Java Virtual Machine)

Acronym for Java Virtual Machine. An abstract computing machine, or virtual machine, JVM is a platform-independent execution environment that converts Java bytecode into machine language and executes it. Most programming languages compile source code directly into machine code that is designed to run on a specific microprocessor architecture or operating system, such as Windows or UNIX. A JVM -- a machine within a machine -- mimics a real Java processor, enabling Java bytecode to be executed as actions or operating system calls on any processor regardless of the operating system. For example, establishing a socket connection from a workstation to a remote machine involves an operating system call. Since different operating systems handle sockets in different ways, the JVM translates the programming code so that the two machines that may be on different platforms are able to connect.

JVMs are not the only virtual machines being used today.

Defining JVM(Java Virtual Machine)

NAME

Java::JVM::Classfile - Parse JVM Classfiles

SYNOPSIS

use Java::JVM::Classfile;

my $c = Java::JVM::Classfile->new("HelloWorld.class");
print "Class: " . $c->class . "\n";
print "Methods: " . scalar(@{$c->methods}) . "\n";

DESCRIPTION

The Java Virtual Machine (JVM) is an abstract machine which processes JVM classfiles. Such classfiles contain, broadly speaking, representations of the Java methods and member fields forming the definition of a single class, information to support the exception mechanism and a system for representing additional class attributes. The JVM itself exists primarily to load and link classfiles into the running machine on demand (performed by the Class Loader), represent those classes internally by means of a number of runtime data structures and facilitate execution (a role shared between the Execution Engine (which is responsible for execution of JVM instructions) and the Native Method Interface which allows a Java program to execute non-Java code, generally ANSI C/C++.

This Perl module reveals the information in a highly-compressed JVM classfile by representing the information as a series of objects. It is hoped that this module will eventually lead to a JVM implementation in Perl (or Parrot), or possibly a way-ahead-of-time (WAT) to Perl (or Parrot) compiler for Java.

It is important to remember that the Java classfile is highly-compressed. Classfiles are intended to be as small as possible as they are often sent across the network. This may explain the slightly odd object tree. One of the most important things to consider is the idea of a constant pool. All constants (constant strings, method names and signatures etc.) are clustered in the constant pool at the start of the classfile, and sprinkled throughout the file are references to the constant pool. The module attempts to hide this optimisation as much as possible from the user, however.

It is probably important to at least have briefly read "The JavaTM Virtual Machine Specification", http://java.sun.com/docs/books/vmspec/

METHODS


new
This is the constructor, it takes the filename of the classfile to parse and returns an object:

my $c = Java::JVM::Classfile->new("HelloWorld.class");

magic

This method returns the magic number for the classfile. All valid classfiles should have the magic number 0xCAFEBABE:

my $magic = $c->magic;

version

This method returns the version of the classfile. The version consists of a major number and a minor number. For example, "45.3" has major number 45 and minor number 3:

my $version = $c->version;

class

This method returns the name of the class that this classfile corresponds to:

my $class = $c->class;

superclass

This method returns the name of the superclass of the class that this classfile corresponds to:

my $superclass = $c->superclass;

constant_pool

This method returns the constant pool entries as an array reference. Each entry is an object. Currently undocumented.

my $constant_pool = $c->constant_pool;

access_flags

This method returns the access flags for the class as an array reference. Possible flags are:

abstract

Declared abstract; may not be instantiated
final

Declared final; no subclasses allowed
interface

Is an interface, not a class
public

Declared public; may be accessed from outside its package
super

Treat superclass methods specially when invoked by the invokespecial instruction

print "Flags: " . join(", ", @{$c->access_flags}) . "\n";

interfaces

This method returns an array reference of the interfaces defined in the classfile. Currently unimplemented:

my $interfaces = $c->interfaces;

fields

This method returns an array reference of the fields defined in the classfile. Currently unimplemented:

my $fields = $c->fields;


methods

This method returns an array reference of the methods defined in the classfile:

my $methods = $c->methods;

Each Java method is represented by an object which has the following methods: name, descriptor, access_flags and attributes. name and descriptor return the method name and descriptor. Possible access flags are:

abstract

Declared abstract; no implementation is provided
final

Declared final; may not be overridden
native

Declared native; implemented in a language other than Java
private

Declared private; accessible only within the defining class
protected

Declared protected; may be accessed within subclasses
public

Declared public; may be accessed from outside its package
static

Declared static
strict

Declared strictfp; floating-point mode is FP-strict
synchronized

Declared synchronized; invocation is wrapped in a monitor lock

Various attributes are possible, the most common being the Code attribute, where the value holds information about the Java bytecode for the method:

foreach my $method (@{$c->methods}) {
print " " . $method->name . " " . $method->descriptor;
print "\n ";
print "is " . join(", ", @{$method->access_flags});
print "\n ";
print "has attributes: ";
foreach my $att (@{$method->attributes}) {
my $name = $att->name;
my $value = $att->value;
if ($att->name eq 'Code') {
print " $name: ";
print "stack(" . $value->max_stack . ")";
print ", locals(" . $value->max_locals . ")\n";
foreach my $instruction (@{$value->code}) {
print $instruction->label . ':' if defined $instruction->label;
print "\t" . $instruction->op . "\t" . (join ", ", @{$instruction->args}) . "\n";
}
print "\n";
foreach my $att2 (@{$value->attributes}) {
my $name2 = $att2->name;
my $value2 = $att2->value;
if ($name2 eq 'LineNumberTable') {
print "\tLineNumberTable (offset, line)\n";
print "\t" . $_->offset . ", " . $_->line . "\n" foreach (@$value2);
} else {
print "!\t$name2 = $value2\n";
}
}
} else {
print "!\t$name $value\n";
}
}
print "\n";
}

Note that in the case of the Code attribute, the value contains an object which has three main methods: max_stack (the maximum depth of stack needed by the method), max_locals (the number of local variables used by the method), code (returns an arrayref of instruction objects which have op, args and label methods), and attributes. One attribute that Code can have is the LineNumberTable attributes, which has an arrayref of objects as a value. These have offset and line methods, representing a link between bytecode offset and sourcecode line.
attributes

This method returns an array reference of the attributes defined in the classfile. Attributes are common in many places in the classfile - here in particular we have the classfile attributes.

my $attributes = $c->attributes;

Attributes are represented by an object that has name and value methods:

foreach my $attribute (@{$c->attributes}) {
print " " . $attribute->name . " = " . $attribute->value . "\n";
}

Possible attributes include the SourceFile attribute, the value of which is the source file that was compiled into this classfile.

Java theory and practice: Dealing with InterruptedException

Many Java™ language methods, such as Thread.sleep() and Object.wait(), throw InterruptedException. You can't ignore it because it's a checked exception, but what should you do with it? In this month's Java theory and practice, concurrency expert Brian Goetz explains what InterruptedException means, why it is thrown, and what you should do when you catch one.

This story is probably familiar: You're writing a test program and you need to pause for some amount of time, so you call Thread.sleep(). But then the compiler or IDE balks that you haven't dealt with the checked InterruptedException. What is InterruptedException, and why do you have to deal with it?

The most common response to InterruptedException is to swallow it -- catch it and do nothing (or perhaps log it, which isn't any better) -- as we'll see later in Listing 4. Unfortunately, this approach throws away important information about the fact that an interrupt occurred, which could compromise the application's ability to cancel activities or shut down in a timely manner.


Blocking methods

When a method throws InterruptedException, it is telling you several things in addition to the fact that it can throw a particular checked exception. It is telling you that it is a blocking method and that it will make an attempt to unblock and return early -- if you ask nicely.
A blocking method is different from an ordinary method that just takes a long time to run. The completion of an ordinary method is dependent only on how much work you've asked it to do and whether adequate computing resources (CPU cycles and memory) are available. The completion of a blocking method, on the other hand, is also dependent on some external event, such as timer expiration, I/O completion, or the action of another thread (releasing a lock, setting a flag, or placing a task on a work queue). Ordinary methods complete as soon as their work can be done, but blocking methods are less predictable because they depend on external events. Blocking methods can compromise responsiveness because it can be hard to predict when they will complete.

Because blocking methods can potentially take forever if the event they are waiting for never occurs, it is often useful for blocking operations to be cancelable. (It is often useful for long-running non-blocking methods to be cancelable as well.) A cancelable operation is one that can be externally moved to completion in advance of when it would ordinarily complete on its own. The interruption mechanism provided by Thread and supported by Thread.sleep() and Object.wait() is a cancellation mechanism; it allows one thread to request that another thread stop what it is doing early. When a method throws InterruptedException, it is telling you that if the thread executing the method is interrupted, it will make an attempt to stop what it is doing and return early and indicate its early return by throwing InterruptedException. Well-behaved blocking library methods should be responsive to interruption and throw InterruptedException so they can be used within cancelable activities without compromising responsiveness.

Thread interruption

Every thread has a Boolean property associated with it that represents its interrupted status. The interrupted status is initially false; when a thread is interrupted by some other thread through a call to Thread.interrupt(), one of two things happens. If that thread is executing a low-level interruptible blocking method like Thread.sleep(), Thread.join(), or Object.wait(), it unblocks and throws InterruptedException. Otherwise, interrupt() merely sets the thread's interruption status. Code running in the interrupted thread can later poll the interrupted status to see if it has been requested to stop what it is doing; the interrupted status can be read with Thread.isInterrupted() and can be read and cleared in a single operation with the poorly named Thread.interrupted().

Interruption is a cooperative mechanism. When one thread interrupts another, the interrupted thread does not necessarily stop what it is doing immediately. Instead, interruption is a way of politely asking another thread to stop what it is doing if it wants to, at its convenience. Some methods, like Thread.sleep(), take this request seriously, but methods are not required to pay attention to interruption. Methods that do not block but that still may take a long time to execute can respect requests for interruption by polling the interrupted status and return early if interrupted. You are free to ignore an interruption request, but doing so may compromise responsiveness.

One of the benefits of the cooperative nature of interruption is that it provides more flexibility for safely constructing cancelable activities. We rarely want an activity to stop immediately; program data structures could be left in an inconsistent state if the activity were canceled mid-update. Interruption allows a cancelable activity to clean up any work in progress, restore invariants, notify other activities of the cancellation, and then terminate.

Dealing with InterruptedException

If throwing InterruptedException means that a method is a blocking method, then calling a blocking method means that your method is a blocking method too, and you should have a strategy for dealing with InterruptedException. Often the easiest strategy is to throw InterruptedException yourself, as shown in the putTask() and getTask() methods in Listing 1. Doing so makes your method responsive to interruption as well and often requires nothing more than adding InterruptedException to your throws clause.

Listing 1. Propagating InterruptedException to callers by not catching it

public class TaskQueue {
private static final int MAX_TASKS = 1000;

private BlockingQueue queue
= new LinkedBlockingQueue(MAX_TASKS);

public void putTask(Task r) throws InterruptedException {
queue.put(r);
}

public Task getTask() throws InterruptedException {
return queue.take();
}
}


Sometimes it is necessary to do some amount of cleanup before propagating the exception. In this case, you can catch InterruptedException, perform the cleanup, and then rethrow the exception. Listing 2, a mechanism for matching players in an online game service, illustrates this technique. The matchPlayers() method waits for two players to arrive and then starts a new game. If it is interrupted after one player has arrived but before the second player arrives, it puts that player back on the queue before rethrowing the InterruptedException, so that the player's request to play is not lost.

Listing 2. Performing task-specific cleanup before rethrowing InterruptedException

public class PlayerMatcher {
private PlayerSource players;

public PlayerMatcher(PlayerSource players) {
this.players = players;
}

public void matchPlayers() throws InterruptedException {
Player playerOne, playerTwo;
try {
while (true) {
playerOne = playerTwo = null;
// Wait for two players to arrive and start a new game
playerOne = players.waitForPlayer(); // could throw IE
playerTwo = players.waitForPlayer(); // could throw IE
startNewGame(playerOne, playerTwo);
}
}
catch (InterruptedException e) {
// If we got one player and were interrupted, put that player back
if (playerOne != null)
players.addFirst(playerOne);
// Then propagate the exception
throw e;
}
}
}

Don't swallow interrupts

Sometimes throwing InterruptedException is not an option, such as when a task defined by Runnable calls an interruptible method. In this case, you can't rethrow InterruptedException, but you also do not want to do nothing. When a blocking method detects interruption and throws InterruptedException, it clears the interrupted status. If you catch InterruptedException but cannot rethrow it, you should preserve evidence that the interruption occurred so that code higher up on the call stack can learn of the interruption and respond to it if it wants to. This task is accomplished by calling interrupt() to "reinterrupt" the current thread, as shown in Listing 3. At the very least, whenever you catch InterruptedException and don't rethrow it, reinterrupt the current thread before returning.

Listing 3. Restoring the interrupted status after catching InterruptedException

public class TaskRunner implements Runnable {
private BlockingQueue queue;

public TaskRunner(BlockingQueue queue) {
this.queue = queue;
}

public void run() {
try {
while (true) {
Task task = queue.take(10, TimeUnit.SECONDS);
task.execute();
}
}
catch (InterruptedException e) {
// Restore the interrupted status
Thread.currentThread().interrupt();
}
}
}


The worst thing you can do with InterruptedException is swallow it -- catch it and neither rethrow it nor reassert the thread's interrupted status. The standard approach to dealing with an exception you didn't plan for -- catch it and log it -- also counts as swallowing the interruption because code higher up on the call stack won't be able to find out about it. (Logging InterruptedException is also just silly because by the time a human reads the log, it is too late to do anything about it.) Listing 4 shows the all-too-common pattern of swallowing an interrupt:

Listing 4. Swallowing an interrupt -- don't do this

// Don't do this
public class TaskRunner implements Runnable {
private BlockingQueue queue;

public TaskRunner(BlockingQueue queue) {
this.queue = queue;
}

public void run() {
try {
while (true) {
Task task = queue.take(10, TimeUnit.SECONDS);
task.execute();
}
}
catch (InterruptedException swallowed) {
/* DON'T DO THIS - RESTORE THE INTERRUPTED STATUS INSTEAD */
}
}
}


If you cannot rethrow InterruptedException, whether or not you plan to act on the interrupt request, you still want to reinterrupt the current thread because a single interruption request may have multiple "recipients." The standard thread pool (ThreadPoolExecutor) worker thread implementation is responsive to interruption, so interrupting a task running in a thread pool may have the effect of both canceling the task and notifying the execution thread that the thread pool is shutting down. If the task were to swallow the interrupt request, the worker thread might not learn that an interrupt was requested, which could delay the application or service shutdown.


Back to top


Implementing cancelable tasks

Nothing in the language specification gives interruption any specific semantics, but in larger programs, it is difficult to maintain any semantics for interruption other than cancellation. Depending on the activity, a user could request cancellation through a GUI or through a network mechanism such as JMX or Web Services. It could also be requested by program logic. For example, a Web crawler might automatically shut itself down if it detects that the disk is full, or a parallel algorithm might start multiple threads to search different regions of the solution space and cancel them once one of them finds a solution.

Just because a task is cancelable does not mean it needs to respond to an interrupt request immediately. For tasks that execute code in a loop, it is common to check for interruption only once per loop iteration. Depending on how long the loop takes to execute, it could take some time before the task code notices the thread has been interrupted (either by polling the interrupted status with Thread.isInterrupted() or by calling a blocking method). If the task needs to be more responsive, it can poll the interrupted status more frequently. Blocking methods usually poll the interrupted status immediately on entry, throwing InterruptedException if it is set to improve responsiveness.

The one time it is acceptable to swallow an interrupt is when you know the thread is about to exit. This scenario only occurs when the class calling the interruptible method is part of a Thread, not a Runnable or general-purpose library code, as illustrated in Listing 5. It creates a thread that enumerates prime numbers until it is interrupted and allows the thread to exit upon interruption. The prime-seeking loop checks for interruption in two places: once by polling the isInterrupted() method in the header of the while loop and once when it calls the blocking BlockingQueue.put() method.

Listing 5. Interrupts can be swallowed if you know the thread is about to exit

public class PrimeProducer extends Thread {
private final BlockingQueue queue;

PrimeProducer(BlockingQueue queue) {
this.queue = queue;
}

public void run() {
try {
BigInteger p = BigInteger.ONE;
while (!Thread.currentThread().isInterrupted())
queue.put(p = p.nextProbablePrime());
} catch (InterruptedException consumed) {
/* Allow thread to exit */
}
}

public void cancel() { interrupt(); }
}


Noninterruptible blocking

Not all blocking methods throw InterruptedException. The input and output stream classes may block waiting for an I/O to complete, but they do not throw InterruptedException, and they do not return early if they are interrupted. However, in the case of socket I/O, if a thread closes the socket, blocking I/O operations on that socket in other threads will complete early with a SocketException. The nonblocking I/O classes in java.nio also do not support interruptible I/O, but blocking operations can similarly be canceled by closing the channel or requesting a wakeup on the Selector. Similarly, attempting to acquire an intrinsic lock (enter a synchronized block) cannot be interrupted, but ReentrantLock supports an interruptible acquisition mode.

Noncancelable tasks

Some tasks simply refuse to be interrupted, making them noncancelable. However, even noncancelable tasks should attempt to preserve the interrupted status in case code higher up on the call stack wants to act on the interruption after the noncancelable task completes. Listing 6 shows a method that waits on a blocking queue until an item is available, regardless of whether it is interrupted. To be a good citizen, it restores the interrupted status in a finally block after it is finished, so as not to deprive callers of the interruption request. (It can't restore the interrupted status earlier, as it would cause an infinite loop -- BlockingQueue.take() could poll the interrupted status immediately on entry and throws InterruptedException if it finds the interrupted status set.)

Listing 6. Noncancelable task that restores interrupted status before returning

public Task getNextTask(BlockingQueue queue) {
boolean interrupted = false;
try {
while (true) {
try {
return queue.take();
} catch (InterruptedException e) {
interrupted = true;
// fall through and retry
}
}
} finally {
if (interrupted)
Thread.currentThread().interrupt();
}
}