java.sql.SQLSyntaxErrorException: ORA-00936: missing expression using tELTOracleOutput

js_support — Thu, 31 May 2018 22:41:29 GMT

Hello,

I am trying to set up a basic Oracle ELT job with a MERGE "Action on data" for update/insert. The specific error when I run my ELT job is "Exception in component tELTOracleOutput_1 (D_Lead_Inc_ELT) java.sql.SQLSyntaxErrorException: ORA-00936: missing expression"

Attached is my job. Any pointers here are greatly appreciated.

Doug

Re: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression using tELTOracleOutput

vapukov — Thu, 31 May 2018 23:24:59 GMT

in Your original Job design - You are not define keys for Output table (on picture - modified, right LEAD_ID was missed):

in this case Talend generate construction like:

				String mergeQuery = "MERGE INTO "
						+ tableName_tELTOracleOutput_1 + " target" + " USING ("
						+ select_query_tELTOracleOutput_1 + ") source ON (" +

						"" + ")";

				mergeQuery += " WHEN MATCHED THEN UPDATE SET target.LEAD_TYPE=source.LEAD_TYPE, SKIPPED for make it shorter ";

				mergeQuery += "WHERE " + "source.lead_id = target.lead_id";

this construction - illegal

when You define Key for source and target, it would be:

				String mergeQuery = "MERGE INTO "
						+ tableName_tELTOracleOutput_1 + " target" + " USING ("
						+ select_query_tELTOracleOutput_1 + ") source ON (" +

						"target.LEAD_ID=source.LEAD_ID" + ")";

				mergeQuery += " WHEN MATCHED THEN UPDATE SET target.LEAD_TYPE=source.LEAD_TYPE,SKIPPED ";

				mergeQuery += "WHERE " + "source.lead_id = target.lead_id";

topic java.sql.SQLSyntaxErrorException: ORA-00936: missing expression using tELTOracleOutput in Talend Studio

java.sql.SQLSyntaxErrorException: ORA-00936: missing expression using tELTOracleOutput

Re: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression using tELTOracleOutput