ODI cannot drop, create staging table

wooby01

Hi all,

I'm trying to sync data between Oracle and Vertica using ODI tool. Mapping is created with pretty simple initial requirement, source target database and a filter. I'm just going to sync 4 attributes from Oracle database. However, when I run the job, it gives the below error.

oracle.odi.runtime.agent.execution.sql.SQLCommand
WARNING: SQLCommand execution failure: [Vertica]VJDBC ROLLBACK: Table "C$_0FILTER" does not exist
Failed query text:

drop table public.C$_0FILTER

Based on my understanding, staging table don't need to be created manually. It should be created by ODI process, right?

After the above error it prints the below error. Full of errors lol.

WARNING: SQLCommand execution failure: [Vertica]VJDBC ERROR: Syntax error at or near "CHAR" at character 89
Failed query text:

create table public.C$_0FILTER
(
TRAN_DATE DATE,
CONT_PART_TRAN_SRL_NUM VARCHAR2(4 CHAR),
ACCT_TRANSFER_FLG VARCHAR2(1 CHAR),
ACCT_ID VARCHAR2(20 CHAR)
)

Have you ever seen this error?

Francois

Hello,
did you have a look to the following article https://www.vertica.com/kb/ODIcg/Content/Partner/ODIcg.htm
You probably have to do some work on the ODI side, currently you try to do CREATE TABLE with invalid data type, the Oracle VARCHAR2 have to be replaced by VARCHAR on the Vertica side
The DATE data type exists on both side but it could or should be mapped to a timestamp on the Vertica side because the DATE type in Oracle may contain an time part and on the Vertica side it's only a date (YYYY-MM-DD)
Last, you could replace the "drop table" with a "drop table if exists "

All of these modifications should be done in ODI to get the correct syntax generated for Vertica

Regards

wooby01

Hi,

Thank you. Agree with you. Something is missing on the ODI side. As I understand, staging tables are created by ODI itself. I mean tables started with C$ prefix. But I'm not sure on which side they're going to be created. If staging tables are going to be created on the Vertica side, this syntax should be changed into something readable by Vertica.
Btw, I followed https://www.vertica.com/kb/ODIcg/Content/Partner/ODIcg.htm this guide exactly.

VivJain

Hi,

Please can you share the versions of ODI as well as Vertica client and server.
Also, please specify the OS that you have ODI installed on and the Oracle db details

Regards/Vivek

wooby01

Hi,

Thank you for recommendations. Spent almost 2 weeks to make it work, but no luck. Started running job on Talend tool. It works. Let's close this thread. Thanks again.