tELTOracleOutput MERGE : option Use Merge Insert

One Star xkb
One Star

tELTOracleOutput MERGE : option Use Merge Insert

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
Moderator

Re: tELTOracleOutput MERGE : option Use Merge Insert

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star xkb
One Star

Re: tELTOracleOutput MERGE : option Use Merge Insert

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
One Star xkb
One Star

Re: tELTOracleOutput MERGE : option Use Merge Insert

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
One Star

Re: tELTOracleOutput MERGE : option Use Merge Insert

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

Re: tELTOracleOutput MERGE : option Use Merge Insert

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.
Seventeen Stars

Re: tELTOracleOutput MERGE : option Use Merge Insert

Great job jesseg!