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.
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.