pl sql & java - creating dynamic query

Question

Hi I have a dilemma, I'm using java and oracle and trying to keep querys on plsql side. Everything is OK, until I have these complex querys which may and may not have conditions. Its not hard in Java to put together WHERE clause with conditions, but its not nice. And in pl sql side I also found out that the only possibility for 'dynamic querys' is string manipulations like

IF inputname IS NOT NULL THEN    
    query := query ||' and NAME=' || inputname; 
END IF;

Now I'm thinking, I'm leaving query in pl sql and sending WHERE clause with function parameter. Any good recommentations or examples please, its been hard day :)

Answer 1

SQLBuilder might be useful to you from the Java side. It allows you to write compile-time checked Java code that dynamically builds sql:

String selectQuery =
  (new SelectQuery())
  .addColumns(t1Col1, t1Col2, t2Col1)
  .addJoin(SelectQuery.JoinType.INNER_JOIN, joinOfT1AndT2)
  .addOrderings(t1Col1)
  .validate().toString();

Answer 2

In PL/SQL use:

EXECUTE IMMEDIATE lString;

This lets you build the lString (a VARCHAR2) into most bits of SQL that you'll want to use. e.g.

  EXECUTE IMMEDIATE 'SELECT  value
                     FROM    TABLE
                     WHERE   '||pWhereClause
  INTO    lValue;

You can also return multiple rows and perform DDL statements in EXECUTE IMMEDIATE.

Answer 3

PL/SQL is not pleasant for creating dynamic SQL as you have discovered, its string manipulation is painful. You can send the where clause from the client, but you must make sure to check for SQL injection, i.e. make sure the phrase starts with "where", has no semi-colon or only at the end (if it could occur in the middle you need to look from string delimiter and only allow it within them), etc. Another option would be a stored procedure that takes a predefined parameter list of field filters, applying a "like" for each column against the parameter field.

Answer 4

Yea, EXECUTE IMMEDIATE is my friend also. Thanks for suggestions. I think this time I try to send just WHERE clause with parameter

Answer 5

I think its better to have the whole logic of the query creation in one place, Java or Oracle. I asume that you know how to do it in Java. In Oracle if the query only retrieves a row you can use the EXECUTE IMMEDIATE ... INTO clause.

If the query return multiple rows and has single parameters (no use the IN operator ) you can use the REF CURSOR strategy to loop the query results or return the cursor itself to the Java program (you must import Oracle java clases if you use it). First Ref Cursor answer in Google

If you must use the IN parameter ( or in another rare cases) you must parse the query with the DBMS_SQL package, which is TOO verbose and a little tricky to use, but it's VERY flexible. DBMS_SQL doc (watch the flow diagram BEFORE read the methods)