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?

If you provide a little more detail on your queries, I can do you a more suitable example. — cagcowboy
– cagcowboy, Commented Sep 23, 2008 at 18:26
Why add the complexity? PL/SQL works well for some things, but not for lots of small queries. — Joshua
– Joshua, Commented Sep 23, 2008 at 19:23
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 — marko
– marko, Commented Sep 23, 2008 at 20:00

Kyle Burton · Accepted Answer · 2008-09-23 18:18:23Z

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

answered Sep 23, 2008 at 18:18

Kyle Burton

27.9k9 gold badges53 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe Skora · Accepted Answer · 2008-09-23 18:25:16Z

1

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.

answered Sep 23, 2008 at 18:25

Joe Skora

14.9k5 gold badges39 silver badges39 bronze badges

Comments

cagcowboy · Accepted Answer · 2008-09-23 18:21:38Z

0

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.

answered Sep 23, 2008 at 18:21

cagcowboy

31.1k11 gold badges75 silver badges95 bronze badges

1 Comment

Telcontar Over a year ago

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

marko · Accepted Answer · 2008-09-23 18:55:29Z

0

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

answered Sep 23, 2008 at 18:55

marko

612 silver badges9 bronze badges

Comments

Telcontar · Accepted Answer · 2008-09-23 19:12:58Z

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)

Collectives™ on Stack Overflow

pl sql & java - creating dynamic query

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related