CODEWITHSUNDEEP
javaplsql
marko
marko

Reputation: 61

pl sql & java - creating dynamic query

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?

Upvotes: 2

Views: 4571

Answers (5)

Telcontar
Telcontar

Reputation: 4870

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)

Upvotes: 0

marko
marko

Reputation: 61

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

Upvotes: 0

Joe Skora
Joe Skora

Reputation: 14911

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.

Upvotes: 1

cagcowboy
cagcowboy

Reputation: 30828

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.

Upvotes: 0

Kyle Burton
Kyle Burton

Reputation: 27528

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();

Upvotes: 1

Related Questions