2

using the following query in SQLDeveloper to test merge everything goes fine:

merge into proj.person_registry pr 
using ( 
select null as id, 
'69696696553' as code, 
'TESTYMC' as name, 
'WHATEVER' as firstname, 
'M' as cl_gender, 
'E' as cl_status, 
null as birth_date, 
null as death_date, 
null as citizen_country_code, 
null as country_code, 
null as location_code, 
null as zip, 
'SOMETOWN' as aadress, 
null as date_updated, 
null as date_created, 
null as aadress_date 
from dual) t on (pr.code = t.code) 
when matched then update set 
pr.name                 = t.name, 
pr.firstname            = t.firstname, 
pr.cl_gender            = t.cl_gender, 
pr.cl_status            = t.cl_status, 
pr.birth_date           = t.birth_date, 
pr.death_date           = t.death_date, 
pr.citizen_country_code = t.citizen_country_code, 
pr.country_code         = t.country_code, 
pr.location_code        = t.location_code, 
pr.zip                  = t.zip, 
pr.aadress              = t.aadress, 
pr.aadress_date         = t.aadress_date 
when not matched then 
insert values (t.id, t.code, t.name, t.firstname, t.cl_gender, t.cl_status, t.birth_date, t.death_date, t.citizen_country_code, t.country_code, t.location_code, t.zip, t.aadress, t.date_created, t.date_updated, t.aadress_date);

however trying to execute it in my code using jdbc throws BadSqlGrammarException with cause as: java.sql.SQLException: ORA-00900: invalid SQL statement

ollo
  • 926
  • 1
  • 14
  • 33
  • 1
    BadSqlGrammarException is thrown by the Spring framework. Spring is probably expecting a standard SELECT statement. – Adrian Pronk Apr 20 '12 at 07:35
  • 1
    You should show us the code that executes that Statement –  Apr 20 '12 at 09:58
  • thanks, i cant till monday. i was using JdbcTemplate. queryForLong(String sql, Object[] args) , where sql was the merge statement. Didn't even set any args for testing purposes. cant remember anything else atm, sorry :( – ollo Apr 21 '12 at 00:16

1 Answers1

1

You've probably catered for this already but you may need to escape the single quotes in the query.

I have also found that when passing a query to Oracle, in my case from SQL Server using OPENQUERY, the semicolon makes the query fail with exactly that Oracle error code.

It might be as simple as removing the semicolon from the end of the statement

Davos
  • 5,066
  • 42
  • 66