ORA-00907: missing right parenthesis with PreparedStatement

Question

I'm trying to run this query, but I've got an error

ORA-00907: missing right parenthesis

I reviewed the statement but the parenthesis are right. I removed the last line of query and it works:

AND TN.LAST_UPDATEDATE > (CURRENT_TIMESTAMP - INTERVAL ? MINUTE)

maybe can be something in the '>' oparator, but I can't do this whithout it.

Code:

String sql= "SELECT DID.ORDER_ID "
            + "FROM TEST_ORDER DID, TEST_ORDER_SG DOS, TEST_HRD_SHIPS_GRP DHSG, TEST_INFO CSI "
            + "WHERE DID.ORDER_ID = DOS.ORDER_ID "
            + "AND DOS.SHIPPING_GROUPS = DHSG.SHIPPING_ID "
            + "AND DOS.SHIPPING_GROUPS = CSI.SHIPPING_ID "
            + "AND DHSG.TRACKING_CODE IS NULL AND CSI.SHIPPING_CARRIER IN "
            + "(?) "
            + "AND DID.STATE IN (?) "
            + "AND NOT EXISTS "
            + "("
            + "SELECT 1 FROM "
            + "CLIENT_INTEGRATION.TEST_LOG_ORDERS_STATUS TN "
            + "WHERE TN.ORDER_ID = DID.ORDER_ID AND TN.STATE = DID.STATE "
            + "AND TN.LAST_UPDATEDATE > (CURRENT_TIMESTAMP - INTERVAL ? MINUTE)"
            + ")";

Code to bind the parameters:

int actualIndex = 0;
for (int i = 0; i < params.length; i++) {
    actualIndex = sql.indexOf('?', actualIndex+1);
    Class classe = params[i].getClass();

    if(classe.isArray()) {
        StringBuffer newPlaceHolders = new StringBuffer();

        int arrayLength = java.lang.reflect.Array.getLength(params[i]);

        for (int j = 0; j < arrayLength; j++) {

            if (j>0) {
                newPlaceHolders.append(",?");                        
            } else {
                newPlaceHolders.append("?");                        
            }
        }

        sql = sql.substring(0, actualIndex) + newPlaceHolders + sql.substring(actualIndex+1);
        actualIndex += newPlaceHolders.length();
    }

    if (actualIndex == -1) {
           break;
    }
}

So in another method I fill with some for loops, cause in some cases, the parameters (who are in a properties file) can be more than one.

int i=1;
    for(String carriers : getCarrier()) {
        ps.setString(i, carriers);              
        i++;
    }

    for(String state : getAllStates()) {
        ps.setString(i, state);
        i++;
    }

score 3 · Accepted Answer · edited May 23 '17 at 12:16

INTERVAL ? MINUTE is not valid.

The syntax is INTERVAL '1' MINUTE (and you cannot specify a variable for the value).

If you want to specify a variable you need to use TO_DSINTERVAL()

Something like:

TO_DSINTERVAL( '0 00:' || TO_CHAR( ?, 'FM00' ) || ':00' )

An aside

You appear to be passing lists into the query by modifying the query to take multiple bind variables; you don't need to do this. Instead you can pass in an Oracle collection as a single bind variable. An example of this can be found here (slightly more complicated as that example is for multi-dimensional arrays) but there is a simpler example here.

Tim Biegeleisen · Answer 2 · 2016-06-27T14:31:39.443

0

The final condition of the WHERE clause in the subquery should look something like this:

AND TN.LAST_UPDATEDATE > (CURRENT_TIMESTAMP - INTERVAL '10' MINUTE)

You need to place the time amount in single quotes.

edited Jun 27 '16 at 14:31

answered Jun 27 '16 at 14:25

Tim Biegeleisen

502,043
27
286
360

score 0 · Answer 3 · answered Jun 27 '16 at 15:11

A simpler solution that using interval or to_dsinterval() is to use direct date arithmetic. You can add or subtract numbers to/from a datetime or timestamp; the number is expressed in days, so if your input (shown as ?) is in minutes, you must divide by 24*60. Like so:

... and tn.last_update > current_timestamp - ? / 24 * 60

Please note that this will change the right-hand side into a date/time (no fractional seconds), so it won't work if you need fractions of a second.

ORA-00907: missing right parenthesis with PreparedStatement

3 Answers3

Linked