Oracle: ORA-00911: invalid character

Question

In my code I have the following query string:

   private static final String QUERY = format(
                    "  SELECT t2.address " +
                    "  FROM schema.table1 t1    ," +
                    "  schema.table2 t2 ," +
                    "  schema.table3 t3            ,"+
                    "  schema.table4 t4 " +
                    "  WHERE t2.uniqueIdentifier =:%s " +
                    "  AND  t1.parent_id = t2.parent_alias " +
                    "  AND t3.company_id  = t1.company_id " +
                    "  AND t3.record_age  = t2.recordAge " +
                    "  AND t2.name = 'stubName' " +
                    "  AND t4.pln_foi_id = t2.recordAge ",uniqueIdentifier);

Which is called in the native query as below:

 public String getAddress(String uniqueIdentifier){

        String result = null;

        try {
            Query query = persistence.entityManager().createNativeQuery(QUERY);
            query.setParameter("uniqueIdentifier", uniqueIdentifier);
            result = query.getSingleResult();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

When I test this query I get the following:

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

Caused by: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

What could be causing this error? I cannot see any issues within my query string or code that could cause it.

what values suppose to come in variable uniqueIdentifier ? may be that is causing issue — shreyansh jogi, Jan 25 '17 at 16:19
The result of your format returns a String starting with `SELECT t2.address , FROM `, seems to me the `,` should not be there. — wjans, Jan 25 '17 at 16:20

score 2 · Accepted Answer · answered Jan 25 '17 at 16:27

2

The query should be

...
"  WHERE t2.uniqueIdentifier = :uniqueIdentifier "
...

and remove the call to String.format(); depending on the value of the first uniqueIdentifier variable, you will either be subject to SQL injection or setParameter() won't work.

Explanation: When you have a native query with parameters, you need to specify the name of the parameter in the query with a : (colon) prefix. To use the parameter foo, put :foo in the query and call setParameter("foo", value); to specify which value should be used in place of the parameter.

answered Jan 25 '17 at 16:27

Aaron Digulla

321,842
108
597
820

I have tried this and I am getting the same error. I think there must be something else wrong with the query syntax – java123999 Jan 25 '17 at 16:44
Can you log the query and the argument and try to run it in an SQL tool manually? They can often highlight syntax errors. If that fails, try to debug the code and look inside the SQLException. It will contain the offset of the offending character in the query. – Aaron Digulla Jan 25 '17 at 17:06

Oracle: ORA-00911: invalid character

1 Answers1