
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
tDBRow ora 00911 Invalid character but query works well in Sql Developer
I have this query inside a tDBRow:
insert into test.WK1_SF_L_SRV_CASEHISTORY (SOURCE_SYSTEM_ID ,INCIDENT_ID ,SERIAL_NUMBER ,CUST_ACCOUNT_ID ,OLD_STATUS_ID ,ACT_STATUS_ID ,OLD_GROUP_ID ,ACT_GROUP_ID ,SUBPROCESS_ID ,CREATION_DATE ,CREATION_DATE_FDATE ,END_DATE ,END_DATE_FDATE ,CLOSE_DATE_SR_FDATE ,CLOSE_DATE_SR_LAV_FDATE ,DURATION_TIME ,DURATION_TIME_LAV , RANGE ,LAST_UPDATE_DATE ) SELECT 'SALESFORCE' AS SOURCE_SYSTEM_ID , a.CASEID AS INCIDENT_ID , NVL(b.COD_MATRICOLA,-1) AS SERIAL_NUMBER , NVL(b.CUST_ACCOUNT_ID,-1) AS CUST_ACCOUNT_ID , a.OLDVALUE AS OLD_STATUS_ID , a.NEWVALUE AS ACT_STATUS_ID , a.OLDVALUE AS OLD_GROUP_ID , a.NEWVALUE AS ACT_GROUP_ID ,d.SUBPROCESS_ID as SUBPROCESS_ID ,A.CREATEDDATE AS CREATION_DATE ,b.CREATION_DATE_FDATE as CREATION_DATE_FDATE ,nvl(lead(A.createddate) over (PARTITION BY A.caseid order by A.CREATEDDATE), DATE '2099-12-31') as END_DATE ,to_char(nvl(lead(a.createddate) over (PARTITION BY a.caseid order by a.CREATEDDATE), DATE '2099-12-31')) as END_DATE_FDATE ,b.CLOSE_DATE_FDATE AS CLOSE_DATE_SR_FDATE ,b.CLOSE_DATE_LAV_FDATE AS CLOSE_DATE_SR_LAV_FDATE ,ROUND((lead(a.createddate) OVER(PARTITION BY a.caseid ORDER BY a.CREATEDDATE) - a.CREATEDDATE) * 24,5) as DURATION_TIME ,ROUND( MP_PRC_BI_ETL_SERVICE.DIFF_BUSINESS_HOURS( a.CREATEDDATE, lead(A.createddate) over (PARTITION BY A.caseid order by A.CREATEDDATE) ,c.OWNER_GROUP_CALENDAR_CODE) ,5) AS DURATION_TIME_LAV ,1 as RANGE ,A.CREATEDDATE AS LAST_UPDATE_DATE FROM test.STG_SF_L_SRV_CASEHISTORY a LEFT join test.EDW_SF_F_SRV_SR_TIME b on a.CASEID = b.INCIDENT_ID LEFT join test.EDW_SF_L_SRV_SR c on a.caseid = c.INCIDENT_ID LEFT JOIN test.V_SF_R_SRV_SUBPROCESS d on a.OLDVALUE = d.STATUS_ID and c.SF_GROUP_MACROGROUP_ID = d.MACROGROUP_ID where upper(a.field) = 'STATUS';
When I launch the job, the error ORA - 00911 Invalid character pops out, but if I launch the exact same query in SQL Developer, it works perfectly. Where is the problem?
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mary,
I do not know what the JDBC parameters are for Oracle db, but you may have to take the semicolon off the end of the query or add a parameter to the JDBC that says you are sending multiple SQL statements delimited by semicolon. I ran into this with our database at some point, only I think my error was a little clearer.
Don't know if this will work, but give it a try.
Thanks,
dg

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mary,
I do not know what the JDBC parameters are for Oracle db, but you may have to take the semicolon off the end of the query or add a parameter to the JDBC that says you are sending multiple SQL statements delimited by semicolon. I ran into this with our database at some point, only I think my error was a little clearer.
Don't know if this will work, but give it a try.
Thanks,
dg
