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

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 Spring ’19

Watch the recorded webinar!

Watch Now

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch