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 me out in coverting code from Teradata To Oracle

Discussion in 'SQL PL/SQL' 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)
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    Just to comment and complement above response from Sergey.

    It's important to know which version of Oracle you have.

    As for the options:
    1) You can use CLOB (as Sergey already mentioned)
    2) No you don't need separate indexes to map the multi-column primary index; just add the following at the end of your CREATE TABLE statement:

    Code (SQL):
    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,
      CONSTRAINT SPROC_CDC_LOAD_CNTL_NUPI PRIMARY KEY (TRG_DB_NM ,TRG_TBL_NM , SRC_SYS_CD)
    )
    3) I don't know your Oracle version, but on 11g, you can have 2 TIMESTAMP columns in the same table. If your version doesn't allow yo u to do so, then please explore the other options as provided in Sergey's link. If nothing else works, then I'm afraid you may have to resort to a combination of DATETIME + VARCHAR field to store the information.