Static SQL with Dynamic Parameters

David Clement

28 June 2007; revised 12 March 2008

Oracle version 10gR2


1. Introduction

Have you ever needed to run the same SQL statement with different, dynamically sized argument lists? That problem has come up a number of times in my work. Neither JDBC nor SQL supports an intuitive solution. I've worked out an Oracle solution that can be invoked from a Java method and a PHP script as well as from within a PL/SQL block. I thought it might be useful to other people who need to work in a multilingual programming environment with an Oracle back-end.

Here is a made-up example. Your mission is to answer the same question over and over but with arbitrarily long lists of some parameter. For instance, you want to show the customer name and sales YTD sometimes for one customer, sometimes for several customers, sometimes for thousands of customers:


SELECT c.name_doing_business_as, ytd.current_sales_total
  FROM customers c, year_to_date_aggregation ytd
 WHERE c.customer_id = ytd.customer_id 
   AND ytd.YEAR = TO_CHAR (SYSDATE, 'YYYY') 
   AND c.customer_id = :1;

SELECT c.name_doing_business_as, ytd.current_sales_total
  FROM customers c, year_to_date_aggregation ytd
 WHERE c.customer_id = ytd.customer_id
   AND ytd.YEAR = TO_CHAR (SYSDATE, 'YYYY')
   AND c.customer_id IN (:1, :2, :3, :4);

SELECT c.name_doing_business_as, ytd.current_sales_total
  FROM customers c, year_to_date_aggregation ytd
 WHERE c.customer_id = ytd.customer_id
   AND ytd.YEAR = TO_CHAR (SYSDATE, 'YYYY')
   AND c.customer_id IN (:1, :2, :3, :4... :15998, :15999, :16000);

In your app, whose users are all aggressive salesmen, variants of this statement run dozens of times a minute. You cannot simply code

   AND c.customer_id IN (?)

because you cannot bind an array to a JDBC placeholder. (There is an interesting discussion of the issue in Sun's Java forum here.) You could solve the problem with dynamic SQL, but that is open to serious objections.
  • You don't want to write the Java to generate all these similar SQL statements. That would involve hundreds of lines of code to manipulate strings behind the scenes, possibly with serious performance problems.
  • You don't want the app server to burn resources churning out dozens of SQL statements and filling the JRE statement cache over and over again, losing any chance of statement caching. If you have to do that, there's no point in using the prepared statement interface at all.
  • You don't want the RDBMS to burn CPU compiling the SQL over and over again. Semantically, this is only one query; why compile a semantically stable query during run time?
You want exactly one SQL statement. How do you write it?

2. The RDBMS Infrastructure

First, define table types at the schema level. The types must exist at the schema level because they will be used in a PL/SQL package, and PL/SQL will call their constructor methods during package initialization. For example, for two types, number and varchar, you could define them like this.


CREATE OR REPLACE TYPE typ_ntbl AS TABLE OF NUMBER
/
CREATE OR REPLACE TYPE typ_vctbl AS TABLE OF VARCHAR2 (32)
/

Next, define a PL/SQL package that overloads a function name for each table type that you have declared, as follows.

CREATE OR REPLACE PACKAGE pkg_dynamic_list
AS
   FUNCTION get_table (p_char IN typ_varchar)
      RETURN typ_vctbl PIPELINED;
   FUNCTION get_table (p_num IN typ_ntbl)
      RETURN typ_ntbl PIPELINED;
END pkg_dynamic_list;
/

These pipelined functions will convert incoming arrays into tables.

Step three is to enter the package body, like this.


CREATE OR REPLACE PACKAGE BODY pkg_dynamic_list
AS
   FUNCTION get_table (p_char IN typ_varchar)
      RETURN typ_vctbl PIPELINED
   IS
   BEGIN
      FOR i IN 1 .. p_char.COUNT
      LOOP
         PIPE ROW (p_char (i));
      END LOOP;

      RETURN;
   END get_table;

   FUNCTION get_table (p_num IN typ_ntbl)
      RETURN typ_ntbl PIPELINED
   IS
   BEGIN
      FOR i IN 1 .. p_num.COUNT
      LOOP
         PIPE ROW (p_num (i));
      END LOOP;

      RETURN;
   END get_table;
END pkg_dynamic_list;
/

As you see, the code is very simple. What you have now is a package of overloaded functions that can accept arbitrarily long lists either of numbers or of strings and return them as objects that SQL statements can treat as tables.

3. Accessing the Package from PL/SQL

You can test the package in PL/SQL with a trivial test driver, as follows.


DECLARE
   test_vctbl  typ_varchar := typ_vctbl ('A', 'B', 'C');
   test_ntbl   typ_ntbl    := typ_ntbl (1, 2, 3);
BEGIN
   FOR i IN (SELECT COLUMN_VALUE
               FROM TABLE (pkg_dynamic_list.get_table (test_vctbl)))
   LOOP
      DBMS_OUTPUT.put_line (i.COLUMN_VALUE);
   END LOOP;

   FOR i IN (SELECT COLUMN_VALUE
               FROM TABLE (pkg_dynamic_list.get_table (test_ntbl)))
   LOOP
      DBMS_OUTPUT.put_line (TO_CHAR (i.COLUMN_VALUE));
   END LOOP;
END;

From within a PL/SQL context, you can declare arrays as shown above. This makes the pipelined functions somewhat redundant, because you could simply write a SQL query with this condition:
WHERE some_column IN  
    (SELECT COLUMN_VALUE  
    FROM TABLE (test_ntbl))
Dan Morgan posted in his blog for 23 September 2007 that he keeps getting asked questions of this type, and he gave code much like the above as a PL/SQL solution. He is right, of course, but you can't do that within a Java method.

4. Accessing the Package from Java

For Java, you first create an array of arguments that you will pass to your SQL statement. You might do this as follows, where ps is a preparedStatement.


    Connection conn = ps.getConnection();            

    //is this a string or number array?
    ArrayDescriptor numberArrayDesc =  null;
    //string ORA table: notice you are using the RDBMS type!
    if( value.get(0) instanceof String )             
        numberArrayDesc = ArrayDescriptor.createDescriptor
            ("TYP_VCTBL", conn);
    //number ORA table: notice you are using the RDBMS type!
    else 
        numberArrayDesc = ArrayDescriptor.createDescriptor
            ("TYP_NTBL", conn);
            
    Array valArray = new ARRAY(numberArrayDesc, conn, 
        ((Collection)value).toArray());

You've created a local array using the data type defined in the RDBMS.

You bind the array using the method that you would expect. Here is a bind by position.


    ps.setArray(1, valArray);

This would not work within a SQL 'in' list. It works because the PL/SQL function expects an array argument.

You can now call the SQL statement, almost exactly as you would call any other SQL statement. For example:


try {
    PreparedStatement pst = connection.prepareStatement(
        "SELECT c.name_doing_business_as, ytd.current_sales_total " +
        "  FROM customers c, year_to_date_aggregation ytd " +
        " WHERE c.customer_id = ytd.customer_id " +
        "   AND ytd.YEAR = TO_CHAR (SYSDATE, 'YYYY') " +
        "   AND c.customer_id IN " +
        "     (SELECT COLUMN_VALUE " +
        "     FROM TABLE(pkg_dynamic_list.get_table (?)))");
    pst.setArray(1, valArray);
    ResultSet rs = pst.executeQuery( );
    while (rs.next( ))
        ...; // do what you need to do with the result
    rs.close( );
    pst.close( );
} catch( SQLException ex ) { //Trap SQL errors
    ...; // handle the error as you normally would 
} finally  {        
    try {          
        if( rs != null ) rs.close( );
        if( pst != null ) pst.close( );
        if( con != null ) con.close( );
    } catch( SQLException ex ) {
        ...; // handle the error as you normally would 
    }
}

The SQL above uses a subquery, c.customer_id IN (SELECT...). However, you do not have to use a subquery; you could use the table function in a join, if you like, with SQL as follows:

SELECT c.name_doing_business_as, ytd.current_sales_total
  FROM customers c, year_to_date_aggregation ytd, 
 TABLE (pkg_dynamic_list.get_table (?)) dynamic
 WHERE c.customer_id = ytd.customer_id
   AND c.customer_id = dynamic.customer_id
   AND ytd.YEAR = TO_CHAR (SYSDATE, 'YYYY');

In the SQL, the TABLE ( ) function can be used wherever a table can be used.

5. Accessing the Package from PHP

Invoking the pipelined functions from PHP is even easier than invoking them from Java.

Connect to the database as normal and define the query:


<?php
$connection = OCILogon (USER, PWD, CONNECTION);
$query = 'SELECT c.name_doing_business_as, ytd.current_sales_total 
FROM customers c, year_to_date_aggregation ytd, 
TABLE (pkg_dynamic_list.get_table (:a)) dynamic 
WHERE c.customer_id = ytd.customer_id 
AND c.customer_id = dynamic.customer_id';

For testing, we used the join version, as you see. Next, define a PHP collection, using the types that you have defined in the schema, and populate it (the example below uses toy values).

$collection = oci_new_collection($connection, "TYP_NTBL");
$i = 1;
while ($i < 26000) {
    $collection->append($i);
    $i=$i+1;
}

Parsing and binding work without any fuss.

$stid = OCIParse($connection, $query);
oci_bind_by_name($stid, ':a', $collection, -1, OCI_B_NTY);

To bring the output into your Web page, you can execute and fetch as follows.

OCIExecute($stid, OCI_DEFAULT);
while ($success = OCIFetchInto($stid, $row)) {
    foreach ($row as $item) {
        echo $item." "."<br>\n";
    }
}
?>

I owe the PHP example to my work colleague Hank Geerling.

6. Conclusion

Whether you use Java, PHP, or another language, you can use this technique to avoid dynamic generation of SQL. A stable SQL statement can be precompiled, or hinted, or optimized with a stored outline. You don't have to generate the SQL statement dynamically from within Java and compile it over and over at run time, with the certainty of compilation overhead and with the risk of the SQL optimizer generating a costly execution plan. If you use the capabilities of SQL and PL/SQL, you can minimize the code you write in Java, avoid clobbering the app server cache and the RDBMS optimizer from inside Java, and still get dynamic results.