Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

ODI mapping is stopped with errors

Hi,

I'm pretty new to ODI and Vertica. I'm trying to sync data between Oracle and Vertica using ODI studio. When I run mapping, it gives me the below error in "SERIAL-MAP_BEGIN" step.

When tries to drop a staging table :
ODI-1228: Task Drop staging (C$) table-LKM SQL to SQL (Built-In)- fails on the target connection Connect_Vertica_ODI.
Caused By: java.sql.SQLSyntaxErrorException: [Vertica]VJDBC ERROR: Syntax error at or near "EOL" at character 12


When tries to create a table with below query
create table
(
A_CONT_PART_TRAN_SRL_NUM VARCHAR2(4 CHAR),
A_ACCT_TRANSFER_FLG VARCHAR2(1 CHAR),
A_BNK_ID VARCHAR2(20 CHAR)
)
ODI-1228: Task Create staging (C$) table-LKM SQL to SQL (Built-In)- fails on the target connection Connect_Vertica_ODI.
Caused By: java.sql.SQLSyntaxErrorException: [Vertica]VJDBC ERROR: Syntax error at or near "(" at character 15

Maybe knowledge xml file is missing? I remember there was no LKM SQL Multi-Connect.GLOBAL xml file when importing knowledge modules.

Thanks,

Answers

  • VivJainVivJain Employee

    Hi,

    Please use the Integration Document for ODI with Vertica : https://vertica.com/kb/ODIcg/Content/Partner/ODIcg.htm

    Make sure you check all the options as highlighted into the document.

    Hope this helps.

    Regards/Vivek

  • marcothesanemarcothesane Administrator

    I don't see what SQL statement ODI sends to Vertica in the DROP TABLE step.

    But as the error says: Syntax error at or near "EOL" at character 12 This is consistent with sending an incomplete DROP TABLE command, without the table name. In DROP TABLE, EOL is at character 12.

    Likewise, - this command:
    create table
    (
    A_CONT_PART_TRAN_SRL_NUM VARCHAR2(4 CHAR),
    A_ACCT_TRANSFER_FLG VARCHAR2(1 CHAR),
    A_BNK_ID VARCHAR2(20 CHAR)
    )
    is also missing the table name, as the error message Syntax error at or near "(" at character 15 confirms. I highlighted the open parenthesis causing the error.

    Vertica cannot do anything here. Your ODI configuration must be in a situation where it cannot supply the table name to the SQL statement it is trying to generate. I would also think it is a configuration error. And you will have to ask ODI, not Vertica, professionals for help - and go back to the ODI documentation.

    Good luck ...

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.