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

Highlighted
Six Stars

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
Five Stars

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

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


All Replies
Five Stars

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

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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now