BIK Information Services, Inc.

Oracle PL/SQL snippets
Constructing SQL that uses bind variables only once to simplify JDBC calls
Problem at hand:

We have a relatively complex SQL that uses several bind variables in more than one place.
When making a JDBC call a programmer then need to set the same variable more than once.
For instance: 

stmt.setString(1,"123-45-6789");
stmt.setString(7,"123-45-6789");

In this case parameter that corresponds to SSN happened to be 1st and 7th in the SQL statement.
In more complex cases one may end up with several bind variables that require setting more than once.
This makes maintenance of this SQL difficult.

Example
Let's consider SQL like this: 
SELECT atab.owner, atab.table_name, atab.tablespace_name, atab.num_rows 
FROM ALL_TABLES atab
WHERE OWNER='SYS'
    AND TABLESPACE_NAME='SYSTEM'
    AND num_rows IN ( 
        SELECT max(num_rows)
        FROM ALL_TABLES 
        WHERE OWNER='SYS'
        AND TABLESPACE_NAME='SYSTEM'
    )
Java programmer is supposed to write it as follows: 
SELECT atab.owner, atab.table_name, atab.tablespace_name, atab.num_rows 
FROM ALL_TABLES atab
WHERE OWNER=?
    AND TABLESPACE_NAME=?
    AND num_rows IN ( 
        SELECT max(num_rows)
        FROM ALL_TABLES 
        WHERE OWNER=?
        AND TABLESPACE_NAME=?
    )
and then set bind variables like this: 
stmt.setString(1,"SYS");
stmt.setString(3,"SYS");
stmt.setString(2,"SYSTEM");
stmt.setString(4,"SYSTEM");
As you can see this is not the most elegant way.

Solution. (Oracle 7.3, 8, and 8i).
Let's rewrite this statement as follows: 
SELECT atab.owner, atab.table_name, atab.tablespace_name, atab.num_rows 
FROM ALL_TABLES atab
, (SELECT ? owner, ? tablespace_name FROM DUAL) params
WHERE atab.OWNER=params.owner
    AND atab.TABLESPACE_NAME=params.tablespace_name
    AND atab.num_rows IN ( 
        SELECT max(atab.num_rows)
        FROM ALL_TABLES atab
        WHERE atab.OWNER=params.owner
        AND atab.TABLESPACE_NAME=params.tablespace_name
    )
then we can set bind variables like this: 
stmt.setString(1,"SYS");
stmt.setString(2,"SYSTEM");
Notice - we are setting bind variables only once and as a bonus we effectively gave them names.
Deficiency of this solution:
If your SQL statement is too complex Oracle may not remember the 'params' table and will give you an error: 
SELECT * FROM
 (SELECT 'SYS' owner, 'SYSTEM'  tablespace_name FROM DUAL) params
, (
SELECT atab.owner, atab.table_name, atab.tablespace_name, atab.num_rows 
FROM ALL_TABLES atab
WHERE atab.OWNER=params.owner
    AND atab.TABLESPACE_NAME=params.tablespace_name
    AND atab.num_rows IN ( 
        SELECT max(atab.num_rows)
        FROM ALL_TABLES atab
        WHERE atab.OWNER=params.owner
        AND atab.TABLESPACE_NAME=params.tablespace_name
    ) 
)

ORA-00904: "PARAMS"."TABLESPACE_NAME": invalid identifier

Solution. (Oracle 9i +). 
WITH params as (  
SELECT ? owner, ? tablespace_name FROM DUAL) 
SELECT atab.owner, atab.table_name, atab.tablespace_name, atab.num_rows 
FROM ALL_TABLES atab,params
WHERE atab.OWNER=params.owner
    AND atab.TABLESPACE_NAME=params.tablespace_name
    AND atab.num_rows IN ( 
        SELECT max(atab.num_rows)
        FROM ALL_TABLES atab
        WHERE atab.OWNER=params.owner
        AND atab.TABLESPACE_NAME=params.tablespace_name
    )
This solution works for any SQL as long as Oracle version is 9.02+, but you have to use Oracle 9i JDBC driver in Java, instead of the one that is usually supplied with Java packages.
Then we can set bind variables like this: 
stmt.setString(1,"SYS");
stmt.setString(2,"SYSTEM");
Notice - again we are setting each bind variable only once.

We hope you found this snippet useful.
If so drop us a note to Boris Knizhnik  borisk@bikinfo.com.
We appreciate any links to this site as they show your appreciation and work as free ads.


Last modified on


Back to BIK Information Services Home page Back to BIK Information Services home page