Page 1 of 1

Community Edition -> DBOutputTable and Oracle Upserting

Posted: Thu Aug 22, 2013 8:54 pm
by Justin.Ashworth

I'm working on a project that requires me to move data between a MS SQL Server environment and an Oracle Environment. I'm not having any issues going from Oracle to SQL, as the Embedded if statements required to do an intelligent Insert or Update work without any issues.

I am having a really difficult time getting the converse to work, from SQL Server to Oracle. I've tried two methods to do an upsert:

1) Selecting the count into a variable and then basing my if statement of that variable

Code: Select all

Declare @RowExists INT
SELECT COUNT(ID) into @RowExists where ID = $ID
IF (@RowExists = 0)

2) Using a Merge Table like this:

Code: Select all

   USING (SELECT $Col1,$Col2,$Col3 FROM DUAL) s
   ON = s.Col1

Neither of these two methods have worked, as I get generic "Invalid Query" errors, even though the queries themselves work when run through SQL Developer on the Oracle Servers.

Any insight on what I need to do to make this work would be greatly appreciated.


Re: Community Edition -> DBOutputTable and Oracle Upserting

Posted: Wed Aug 28, 2013 4:17 pm
by slechtaj
Hi Justin,

According to how I understand your question, you would like to migrate data from MSSQL Server to Oracle. What I do not understand is if you are attempting to copy the data from MSSQL to Oracle using one single SQL statement (as you cannot run MERGE INTO with tables from two different database systems).

However, if you want to do an upsert operation, you might think about another solution. This has been already discussed in the following thread: Try to Update record, Insert if not found. As you can see, you can do upsert very easily in CloverETL using multiple components even when you need to work with multiple tables stored in distinct database systems.

However, if you would like to run these statements in Clover, you should use DBExecute component. This component is basically used for more complex SQL/DML/DDL statements (for more information about this component, refer to If you decide for using DBExecute to access MSSQL database, you should keep in mind that the default statement delimiter is semicolon (;). Therefore, if SQL Server expects to receive semicolon as part of your statement, you should set "SQL statement delimiter" attribute to a character that is not used in your statement.

If you have any further question, can you post the whole graph, so that I can better understand what you have been attempting to do?

Re: Community Edition -> DBOutputTable and Oracle Upserting

Posted: Wed Aug 28, 2013 5:03 pm
by Justin.Ashworth

Thanks for the response! The linked post was great, unfortunately I'm being limited to use of the community edition only, which doesn't have access to some of those features. I'm still attempting to convince the powers that be to provide me the full version.

What I'm attempting to do is perform a select statement on a MS SQL Server to grab some data and then upsert it into a remote oracle table. The merge statement I was attempting to use works as I am selecting the data from "Dual" on the remote DB, which is a generic table with one record and just specifying the data I want returned using Clover metadata variables. In reality, what I expected to happen was N number of merge statements would run, merging into the destination table for each record inside the metadata coming from the MS SQL Statement.

I'm doing the SQL statement inside the DBOutputTable, which works perfectly fine when I'm using MS SQL. I'm attaching an example graph that I did that moves data from a MS SQL table to Another and does the upsert operation.

Re: Community Edition -> DBOutputTable and Oracle Upserting

Posted: Thu Aug 29, 2013 3:35 pm
by slechtaj

Although our Community Edition offers only limited variety of components, you may do the upsert another way. Probably the easiest solution would be preparing the whole query upfront in Reformat component.
So let's say your MERGE statement is supposed to look like this:

Code: Select all

MERGE INTO idcard mt
USING (SELECT <value> AS idNumber, <value> AS lastName, <value> AS firstName, <value> AS personType FROM DUAL) dt
ON ( =
  UPDATE SET mt.value = dt.value
  INSERT (id_num, lname, fname, status1) VALUES (dt.idNumber, dt.lastName, dt.firstName, dt.personType)

Then you can fill this statement with values (represented by <value> in above code) in reformat and send the statement as whole to DBExecute. The only thing you need to set in DBExecute is to make the component expect a query from input port (setting the Querry URL tribute). I tried to put the changes into your graph (see attached file), but I could test it with your data, so make sure all the names are correct.

Re: Community Edition -> DBOutputTable and Oracle Upserting

Posted: Wed Feb 17, 2016 4:36 am
by cassydeb
In a .csv file I have a field "DATE_CREATE" containing a date "17/02/16".
For this field, I set in the metadata, type: "date" and format: "dd/MM/yy" like this:

Code: Select all

<Field format="dd/MM/yy" name="DATE_CREATE" type="date"/>

I don't understand how cloverETL can fail with this error:
Illegal char <:> at index <xxx> MERGE INTO (...) 'Wed Feb 17 00:00:00 SBT 2016' (...)

and display in the log file a date with this wrong format: Wed Feb 17 00:00:00 SBT 2016

When I debug between ParallelReader and Reformat and view data, the format of date is good "17/02/16" but in Transform editor $in.0.DATE_CREATE equals 'Wed Feb 17 00:00:00 SBT 2016' and ignore the format of metadata.

How to resolve this problem?

Re: Community Edition -> DBOutputTable and Oracle Upserting

Posted: Mon Feb 29, 2016 9:39 pm
by slechtaj
Hi Cassydeb,

From the information you have provided it is very difficult to find out what causes the issue. Can you attach the execution log and the graph file please so that we can see what you are trying to do?