sinistral.sourcery

SQL Server supports the execution of SQL not within the context of a stored procedure or trigger. This is quite a handy feature when debugging as it allows one to write thowaway code without the overhead of declaring a new stored procedure that later has to be dropped.

Although it is possible to do this in DB2 too, it isn’t as transparent as it is in SQL Server (because – I expect – DB2 is more ANSI SQL compliant than SQL Server). The trick is to declare the code that one wants executed in an ATOMIC block:

   begin atomic
   declare var_a char(1);
   set var_a = (select ibmreqd from sysibm.sysdummy1);
   end
   @

This may be executed from a Command Center session, or (having connected to a database, and the above dumped into a file called "script.sql") via the CLP:

   $ db2 -td@ -vf script.sql

Thanks to DHRUV for a detailed post on the subject.

Since v7.1 DB2 LUW (or UDB, if you prefer) has supported session-specific global temporary tables. Once declared, the table may be referenced until the session ends, at which point the rows are deleted and the description is dropped. (See the IBM Infocenter pages for details). The syntax for declaring these tables is, however, a little arcane, and I found using them in conjuction with cursor declarations somewhat tricky.

Specifically, if the cursor is opened over the temporary table, the cursor declaration must follow the table declaration. However, it seems that DB2’s parser expects only variable declarations to follow the table declaration. For example, the following code:

   connect to testdb@
   set current schema='testschema'@
   
   DROP PROCEDURE temp_proc@
   CREATE PROCEDURE temp_proc(
         IN input_var CHAR(1))
      LANGUAGE SQL
      DYNAMIC RESULT SETS 1
      CALLED ON NULL INPUT
   BEGIN
   
      DECLARE SQLCODE INTEGER DEFAULT 0;
      DECLARE GLOBAL TEMPORARY TABLE t_y_or_n (y_or_n char(1))
         ON COMMIT PRESERVE ROWS NOT LOGGED;
   
      DECLARE local_var CHAR(1);
      DECLARE crsr CURSOR WITH RETURN TO CLIENT FOR
         SELECT y_or_n FROM session.t_y_or_n WHERE y_or_n=local_var;
      INSERT INTO session.t_y_or_n
         SELECT ibmreqd FROM sysibm.sysdummy1;
      SET local_var = input_var;
   
      OPEN crsr;
   
   END@
   
   CALL temp_proc('Y')@
   terminate@

yields the error:

   DB21034E The command was processed as an SQL statement because it
   was not a valid Command Line Processor command. During SQL
   processing it returned:
   SQL0104N An unexpected token "<variable declaration>" was
   found following "". Expected tokens may include: "<SQL statement>".
   LINE NUMBER=12.
   SQLSTATE=42601

The solution seems to be to put the table declaration and the the cursor declaration in different scope. This is achieved using labels, i.e.:

   connect to testdb@
   set current schema='testschema'@
   
   DROP PROCEDURE temp_proc@
   CREATE PROCEDURE temp_proc(
         IN input_var CHAR(1))
      LANGUAGE SQL
      DYNAMIC RESULT SETS 1
      CALLED ON NULL INPUT
   P1 : BEGIN
      DECLARE SQLCODE INTEGER DEFAULT 0;
      DECLARE GLOBAL TEMPORARY TABLE t_y_or_n (y_or_n char(1))
         ON COMMIT PRESERVE ROWS NOT LOGGED;
      P2 : BEGIN
         DECLARE local_var CHAR(1);
         DECLARE crsr CURSOR WITH RETURN TO CLIENT FOR
            SELECT y_or_n FROM session.t_y_or_n WHERE y_or_n=local_var;
         INSERT INTO session.t_y_or_n
            SELECT ibmreqd FROM sysibm.sysdummy1;
         SET local_var = input_var;
         OPEN crsr;
      END P2;
   END P1@
   
   CALL temp_proc('Y')@
   terminate@

The above code can be run by copying it into a file and executing it with:

   db2 -td@ -v -f <script_name>

Thanks to nemali on the Dev Shed forums for his/her post on the subject.

The system that I’ve been working on uses as a standard stored procedures that declare an OUT parameter through which a result cursor is “returned” to the caller. While it is easy enough to call the procedure and process the result set in Java, I occasionally want to call such a procedure from SQL*Plus. To do this, one must bind a suitably typed variable to the procedure’s output parameter, for example:

   SQL> var proc_res refcursor;
   SQL> exec foo.get_stuff (:proc_res);
   
   PL/SQL procedure successfully completed.
   
   SQL> print proc_res;
   
   DUM
   ---
   X


In this example package foo and procedure get_stuff are declared as:

   SQL> create or replace package foo is
    2 type cursor_out is ref cursor;
    3 procedure get_stuff (results out cursor_out);
    4 end foo;
    5 /
   
   Package created.
   
   SQL>
   SQL> create or replace package body foo as
    2 procedure get_stuff (results out cursor_out) is
    3 c1 cursor_out;
    4 begin
    5 open results for select * from dual;
    6 end get_stuff;
    7
    8 end foo;
    9 /
   
   Package body created.


I am currently empoyed to develop/maintain commercial software that is deployed on Oracle Database Enterprise Edition. However, to streamline my day-to-day activities, I’m using Oracle Database Express Edition (XE) in development.

Being somewhat lighter than its peer editions, XE is better suited to a desktop environment and I’ve managed to get it running in a memory constrained VMware Virtual Machine running Ubuntu GNU/Linux 8.04 (Hardy Heron) with 768mb RAM. It is not, however, what one would call a light-weight database and does require a few tweaks to get it to compensate for an environment where resources are scarce.

I’ve started a wiki page that details the various problems that I have encountered, and the solutions that I’ve found. As I encounter issues I’ll post updates in the form of comments.