Skip to main content
Question

ODI cannot drop, create staging table

  • March 29, 2022
  • 4 replies
  • 13 views
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?

    4 replies

    Francois
    Forum|alt.badge.img+1
    • Francois
    • Participating Frequently
    • March 29, 2022

    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
    • wooby01
    • Author
    • Participating Frequently
    • March 30, 2022

    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
    Forum|alt.badge.img
    • VivJain
    • Participating Frequently
    • March 31, 2022

    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
    • wooby01
    • Author
    • Participating Frequently
    • April 14, 2022

    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.

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement