Community Edition -> DBOutputTable and Oracle Upserting

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

Justin.Ashworth
Posts: 2
Joined: Thu Aug 15, 2013 3:53 pm

Community Edition -> DBOutputTable and Oracle Upserting

Postby Justin.Ashworth » Thu Aug 22, 2013 8:54 pm

Hello,

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)
THEN
    INSERT HERE
ELSE
    UPDATE HERE
END IF


2) Using a Merge Table like this:

Code: Select all

MERGE INTO TABLE t
   USING (SELECT $Col1,$Col2,$Col3 FROM DUAL) s
   ON t.id = s.Col1
WHEN NOT MATCHED
    INSERT
WHEN MATCHED
    UPDATE


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.

-Justin

slechtaj
Posts: 192
Joined: Wed Aug 15, 2012 8:18 am

Re: Community Edition -> DBOutputTable and Oracle Upserting

Postby slechtaj » Wed Aug 28, 2013 4:17 pm

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 http://doc.cloveretl.com/documentation/UserGuide/index.jsp?topic=/com.cloveretl.gui.docs/docs/dbexecute.html). 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?
Jan Slechta
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com

How to speed up communication with CloverCARE support

Justin.Ashworth
Posts: 2
Joined: Thu Aug 15, 2013 3:53 pm

Re: Community Edition -> DBOutputTable and Oracle Upserting

Postby Justin.Ashworth » Wed Aug 28, 2013 5:03 pm

slechtaj,

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.
Attachments
TestGraph.grf
(8.3 KiB) Downloaded 274 times

slechtaj
Posts: 192
Joined: Wed Aug 15, 2012 8:18 am

Re: Community Edition -> DBOutputTable and Oracle Upserting

Postby slechtaj » Thu Aug 29, 2013 3:35 pm

Justin,

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 (mt.id = dt.id)
WHEN MATCHED THEN
  UPDATE SET mt.value = dt.value
WHEN NOT MATCHED THEN
  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.
Attachments
TestGraph.grf
Updated graph
(9.4 KiB) Downloaded 288 times
Jan Slechta
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com

How to speed up communication with CloverCARE support

cassydeb
Posts: 7
Joined: Tue Feb 02, 2016 8:06 am

Re: Community Edition -> DBOutputTable and Oracle Upserting

Postby cassydeb » Wed Feb 17, 2016 4:36 am

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?
Thanks

slechtaj
Posts: 192
Joined: Wed Aug 15, 2012 8:18 am

Re: Community Edition -> DBOutputTable and Oracle Upserting

Postby slechtaj » Mon Feb 29, 2016 9:39 pm

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?
Jan Slechta
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com

How to speed up communication with CloverCARE support