1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Guys Please Help Out me in coverting code from Teradata To Oracle

Discussion in 'General' started by baluaggala, Dec 13, 2014.

  1. baluaggala

    baluaggala Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Here are the Below Things i am not able to convert from Teradata to Oracle
    ------------------------------------------------------------------------------------------>>

    Source Data in Teradata :


    CREATE MULTISET TABLE icdw_sproc_t.SPROC_CDC_CNTL ,NO FALLBACK ,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL,
    DATABLOCKSIZE = 130560 BYTES, CHECKSUM = DEFAULT,
    DEFAULT MERGEBLOCKRATIO
    (
    TRG_DB_NM VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
    TRG_TBL_NM VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
    TRG_TBL_ACTV_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'Y' COMPRESS ('N','Y'),
    SRC_SYS_CD VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
    CDC_SQL VARCHAR(63000) CHARACTER SET LATIN NOT CASESPECIFIC FORMAT 'X(10000)',
    CDC_SQL_GEN_DT DATE FORMAT 'yyyy-mm-dd',
    ICDW_ISRT_TS TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0))
    PRIMARY INDEX SPROC_CDC_LOAD_CNTL_NUPI ( TRG_DB_NM ,TRG_TBL_NM ,
    SRC_SYS_CD );


    Oracle Converted :



    CREATE TABLE SPROC_CDC_CNTL
    (
    TRG_DB_NM VARCHAR2(30) NOT NULL,
    TRG_TBL_NM VARCHAR2(30) NOT NULL,
    TRG_TBL_ACTV_IND VARCHAR2(1) NOT NULL DEFAULT 'Y',
    SRC_SYS_CD VARCHAR2(10) NOT NULL,
    CDC_SQL VARCHAR2(4000) ,
    CDC_SQL_GEN_DT DATE,
    ICDW_ISRT_TS TIMESTAMP)

    Doubts :

    1 )CDC_ SQL column having 63000 varchar in teradata . but we have only 4000 limit in oracle

    which other data type we can convert that column ??

    2) In source code teradata
    PRIMARY INDEX SPROC_CDC_LOAD_CNTL_NUPI ( TRG_DB_NM ,TRG_TBL_NM ,
    SRC_SYS_CD )

    How to write in oracle ?? whether we need to create seperate index

    3) One more doubt .... i have two columns as timestamp in teradata but its not allowing in oracle(error saying one table is having one timestamp column)