topic Re: tELTOracleOutput MERGE : option Use Merge Insert in Talend Studio

tELTOracleOutput MERGE : option Use Merge Insert

XKB — Tue, 23 Feb 2016 11:22:37 GMT

Context : very basic ELT mapping to merge from source table into target table.
It looks like the tELTOracleOutput "Use Merge Insert" - that should allow to pick which target columns should actually be inserted - is not working properly.
Out of many available schema column, I check those I do want to insert but the final generated statement yields :

MERGE INTO TARGET (...)
    WHEN NOT MATCHED THEN INSERT (COL1,COL2,,,,,,,,,,,,,,,,,,,,,,,,,,,,)
    VALUES (source.COL1,source.COL2,,,,,,,,,,,,,,,,,,,,,,,,,,,,)

...which in turns leads at run-time to :

Exception in component tELTOracleOutput_1 java.sql.SQLSyntaxErrorException: ORA-00947: nombre de valeurs insuffisant

Conversely, if I actually reduce my target schema to the insertable columns (which is extremely unpractical if I want to incrementally develop my merge mapping), it yields the proper MERGE statement.
Is there something I haven't understood from the component's option ?
Thanks

Re: tELTOracleOutput MERGE : option Use Merge Insert

Anonymous — Mon, 29 Feb 2016 04:01:55 GMT

Hi,
Have you already checked the component reference with related scenario?
https://help.talend.com/search/all?query=tELTOracleOutput&content-lang=en
Best regards
Sabrina

Re: tELTOracleOutput MERGE : option Use Merge Insert

XKB — Mon, 29 Feb 2016 11:28:32 GMT

Hello Sabrina,
Of course I started by reading the fine manual : it says "Select the check box corresponding to the name of the column you want to insert"
...and it quite makes sense to me.
It just doesn't work in the end because if I do not select all columns, it still generates a bunch of commas for those unselected ones.
Only workaround : suppressing columns from target schema (which is pretty unpractical although it works)
I would expect, whilst keeping all my target schema columns, that those unchecked from Insert list would just not appear in the insert part of the statement (isn't it what is implied by the gui ?).
Thanks

Re: tELTOracleOutput MERGE : option Use Merge Insert

XKB — Tue, 22 Mar 2016 14:09:05 GMT

Hi,
Little bump on this ; I'm still encountering the problem and generated ELT Oracle code is syntaxically incorrect (when insert check boxes aren't all selected)
Haven't found any similar case in Jira.
Should I open one ? (or first ask to support ?)
Thanks

Re: tELTOracleOutput MERGE : option Use Merge Insert

Anonymous — Thu, 14 Apr 2016 15:18:15 GMT

I just encountered the same issue. Did you find a resolution?

Re: tELTOracleOutput MERGE : option Use Merge Insert

Anonymous — Thu, 14 Apr 2016 15:57:00 GMT

I believe I found the issue in this block of code:

		if(!useMergeInsert)
			useInsertWhere = false;
		if (useMergeInsert) {
		    boolean isfirstInsertWhere = true;
		    String keyColumnName=null;
			for(Map<String, String> keyColumn:insertColumns){
				if (isfirstInsertWhere) {
	 			 	isfirstInsertWhere = false;
				}else {
					mergeInsertName.append(",");
					mergeInsertValue.append(",");
					
				}
				if (keyColumn.get("INSERT").equals("true")) {
					keyColumnName=keyColumn.get("SCHEMA_COLUMN");
					keyColumnName = new Column(metadata.getColumn(keyColumnName)).getName();
					mergeInsertName.append(keyColumnName);
					mergeInsertValue.append("source." + keyColumnName);
				}				
			}
			%>
				mergeQuery += " WHEN NOT MATCHED THEN INSERT ( <%=mergeInsertName%>) VALUES ( <%=mergeInsertValue%>) ";
			<%
			}

Commas are being appended to the insert variables before checking whether or not INSERT is true.

Re: tELTOracleOutput MERGE : option Use Merge Insert

Anonymous — Fri, 15 Apr 2016 21:39:41 GMT

Great job jesseg!