All Forums Connectivity
jkurzy 1 post Joined 01/06
31 Jan 2006
Oracle Gateway - Teradata ODBC - unable to insert dates

Hello,I am using the Oracle Gateway for Teradata, Teradata ODBC version 3.5. I have created a table in Oracle and loaded it with 5000 rows. I created the same table in Teradata and attempted to load it from Oracle. Having trouble getting dates to load.Oracle TableSQL> desc applications3 Name Null? Type ----------------------------------------- -------- ---------------------------- APP_ID NUMBER(38) SUB_ID NUMBER(38) WARR_PLAN_ID NUMBER LANGUAGE_CODE CHAR(2) DAYS NUMBER(38) CURRENCY_CODE NUMBER(38) CONTRACT_TYPE_CODE CHAR(3) AMORTIZATION_INTEREST NUMBER(9,6) LIFE_STATUS NUMBER(38) CURRENT_WQ NUMBER(38) LOCK_STATUS NUMBER(38) DAG_ID NUMBER(38) CONTRACT_NO NUMBER(8) CONTRACT_CREATED_BY NUMBER(38) ENTERED_BY NUMBER(38) LOCKED_BY NUMBER CONTRACT_CREATED_DATE DATE ENTERED_DATE DATE OWNED_BY NUMBER(38) SUBFOCUS_BY NUMBER(38) SUBFOCUS_DATE DATE DEALER_ID NUMBER(38) LAST_ARCHIVED_BY NUMBER LAST_ARCHIVED_DATE DATE COUNTRY_CODE CHAR(2) IVA_RATE NUMBER APP_ADDRESS_GRP NUMBER(38) DISCOUNT_RATE NUMBER(9,6) APP_TYPE_ID NUMBER(38) FOCUS_BR_CODE NUMBER(2) IRR NUMBER(9,6) WARR_AMT_FINANCED CHAR(1) PRE_APPRV_AMT NUMBER(10,2) PRE_APPRV_TERM NUMBER PRE_APPRV_PMT NUMBER(10,2) PRE_APPRV_EXP_DATE DATE RECALC_REQUIRED CHAR(1) CBRA_ID NUMBER(38) INTEREST_RATE_TYPE CHAR(1) CHRG_OPEN_FEE CHAR(1) GFV_AMT NUMBER(9,2) IS_MULTI CHAR(1) TRANSFER_DATE DATE IS_CONTRACT_BACKDATED CHAR(1) CONTRACT_DATE_EXPIRED CHAR(1) IS_COTIZADOR_APP CHAR(1) PURCHASE_TYPE_CODE CHAR(1) LEASE_CONTRACT_NO NUMBER(38) NON_ZERO_DAYS NUMBER(38) SUBS_VAL NUMBER(9,6) CURRENT_VERSION NUMBER(38) CHECK_VIN CHAR(1) PAYSTATUS CHAR(1) CSC CHAR(1) IS_SCORED CHAR(1) FRANCHISE_CODE VARCHAR2(30) SP_ID NUMBER(38) IS_PREDISCOUNTED CHAR(1) IS_BLOCKED CHAR(1) LAST_SCORE_NO_TL CHAR(1) IS_DIRECT_DEBIT CHAR(1) UNBLOCK_REASON VARCHAR2(35) UNBLOCK_FOCUS_CODE CHAR(1) UNBLOCK_DATE DATETeradata TableSQL> desc applications@teradata; Name Null? Type ----------------------------------------- -------- ---------------------------- APP_ID NOT NULL NUMBER(10) SUB_ID NUMBER(10) WARR_PLAN_ID NUMBER(10) LANGUAGE_CODE CHAR(2) DAYS NUMBER(10) CURRENCY_CODE NUMBER(10) CONTRACT_TYPE_CODE CHAR(3) AMORTIZATION_INTEREST NUMBER(9,6) LIFE_STATUS NUMBER(10) CURRENT_WQ NUMBER(10) LOCK_STATUS NUMBER(10) DAG_ID NUMBER(10) CONTRACT_NO NUMBER(10) CONTRACT_CREATED_BY NUMBER(10) ENTERED_BY NUMBER(10) LOCKED_BY NUMBER(10) CONTRACT_CREATED_DATE DATE ENTERED_DATE DATE OWNED_BY NUMBER(10) SUBFOCUS_BY NUMBER(10) SUBFOCUS_DATE DATE DEALER_ID NUMBER(10) LAST_ARCHIVED_BY NUMBER(10) LAST_ARCHIVED_DATE DATE COUNTRY_CODE CHAR(2) IVA_RATE NUMBER(10) APP_ADDRESS_GRP NUMBER(10) DISCOUNT_RATE NUMBER(9,6) APP_TYPE_ID NUMBER(10) FOCUS_BR_CODE NUMBER(10) IRR NUMBER(9,6) WARR_AMT_FINANCED CHAR(1) PRE_APPRV_AMT NUMBER(10,2) PRE_APPRV_TERM NUMBER(10) PRE_APPRV_PMT NUMBER(10,2) PRE_APPRV_EXP_DATE DATE RECALC_REQUIRED CHAR(1) CBRA_ID NUMBER(10) INTEREST_RATE_TYPE CHAR(1) CHRG_OPEN_FEE CHAR(1) GFV_AMT NUMBER(9,2) IS_MULTI CHAR(1) TRANSFER_DATE DATE IS_CONTRACT_BACKDATED CHAR(1) CONTRACT_DATE_EXPIRED CHAR(1) IS_COTIZADOR_APP CHAR(1) PURCHASE_TYPE_CODE CHAR(1) LEASE_CONTRACT_NO NUMBER(10) NON_ZERO_DAYS NUMBER(10) SUBS_VAL NUMBER(9,6) CURRENT_VERSION NUMBER(10) CHECK_VIN CHAR(1) PAYSTATUS CHAR(1) CSC CHAR(1) IS_SCORED CHAR(1) FRANCHISE_CODE VARCHAR2(30) SP_ID NUMBER(10) IS_PREDISCOUNTED CHAR(1) IS_BLOCKED CHAR(1) LAST_SCORE_NO_TL CHAR(1) IS_DIRECT_DEBIT CHAR(1) UNBLOCK_REASON CHAR(35) UNBLOCK_FOCUS_CODE CHAR(1) UNBLOCK_DATE DATEinsert statementSQL> insert into applications@teradata 2 select 3 APP_ID 4 , SUB_ID 5 , WARR_PLAN_ID 6 , LANGUAGE_CODE 7 , DAYS 8 , CURRENCY_CODE 9 , CONTRACT_TYPE_CODE 10 , AMORTIZATION_INTEREST 11 , LIFE_STATUS 12 , CURRENT_WQ 13 , LOCK_STATUS 14 , DAG_ID 15 , CONTRACT_NO 16 , CONTRACT_CREATED_BY 17 , ENTERED_BY 18 , LOCKED_BY 19 , CONTRACT_CREATED_DATE 20 , ENTERED_DATE 21 , OWNED_BY 22 , SUBFOCUS_BY 23 , SUBFOCUS_DATE 24 , DEALER_ID 25 , LAST_ARCHIVED_BY 26 , LAST_ARCHIVED_DATE 27 , COUNTRY_CODE 28 , IVA_RATE 29 , APP_ADDRESS_GRP 30 , DISCOUNT_RATE 31 , APP_TYPE_ID 32 , FOCUS_BR_CODE 33 , IRR 34 , WARR_AMT_FINANCED 35 , PRE_APPRV_AMT 36 , PRE_APPRV_TERM 37 , PRE_APPRV_PMT 38 , PRE_APPRV_EXP_DATE 39 , RECALC_REQUIRED 40 , CBRA_ID 41 , INTEREST_RATE_TYPE 42 , CHRG_OPEN_FEE 43 , GFV_AMT 44 , IS_MULTI 45 , TRANSFER_DATE 46 , IS_CONTRACT_BACKDATED 47 , CONTRACT_DATE_EXPIRED 48 , IS_COTIZADOR_APP 49 , PURCHASE_TYPE_CODE 50 , LEASE_CONTRACT_NO 51 , NON_ZERO_DAYS 52 , SUBS_VAL 53 , CURRENT_VERSION 54 , CHECK_VIN 55 , PAYSTATUS 56 , CSC 57 , IS_SCORED 58 , FRANCHISE_CODE 59 , SP_ID 60 , IS_PREDISCOUNTED 61 , IS_BLOCKED 62 , LAST_SCORE_NO_TL 63 , IS_DIRECT_DEBIT 64 , UNBLOCK_REASON 65 , UNBLOCK_FOCUS_CODE 66 , UNBLOCK_DATE 67 from applications3 68 /insert into applications@teradata*ERROR at line 1:ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Transparent gateway for TERA][NCR][ODBC Teradata Driver] Invalid date/time/timestamp value (SQL State: 00000; SQL Code: 0) ORA-02063: preceding 2 lines from TERADATA Any ideas??? I have duble checked dateformat = AAA in [DSN]

You must sign in to leave a comment.