tELTOracleOutput MERGE : option Use Merge Insert

[XKB]

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

[Anonymous]

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

[XKB]

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

[XKB]

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

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

Great job jesseg!