tDBRow ora 00911 Invalid character but query works well in Sql Developer

[Anonymous]

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?

[dgreenx · Accepted solution]

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