pl sql & java - creating dynamic query

Question

I have a dilemma, I'm using Java and Oracle and trying to keep queries on PL/SQL side. Everything is OK, until I have these complex queries which may and may not have conditions.

It's not hard in Java to put together WHERE clause with conditions, but it's not nice. And on PL/SQL side I also found out that the only possibility for dynamic queries 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 recommendations or examples please?

• cagcowboy: If you provide a little more detail on your queries, I can do you a more suitable example.
• Joshua: Why add the complexity? PL/SQL works well for some things, but not for lots of small queries.
• marko: This is part of query where I'm adding my conditions, before that is many joins and after, grouping by and order, tricky part is to get the right rows

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

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 3

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.

• Telcontar: I don't think that EXECUTE IMMEDIATE can retrieve multiples rows

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)