CODEWITHSUNDEEP
javastringoracle-databasehibernatenativequery
java123999
java123999

Reputation: 7394

Oracle: ORA-00911: invalid character

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.

Upvotes: 0

Views: 1987

Answers (1)

Aaron Digulla
Aaron Digulla

Reputation: 328604

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.

Upvotes: 2

Related Questions