Home » RDBMS Server » Server Utilities » migration from Sybase to oracle 1 table fails with SQL Error: ORA-01841: (full) year must be between (Oracle 11G linux )
migration from Sybase to oracle 1 table fails with SQL Error: ORA-01841: (full) year must be between [message #560901] Tue, 17 July 2012 13:35 Go to next message
evoradba
Messages: 98
Registered: April 2005
Location: Canada
Member
Hello
im trying to migrate Sybase 12.5 to oracle 11G R2 on linux all tables are fine expect one which fails with error

"SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"
*Cause: Illegal year entered
*Action: Input year in the specified range"

i have change the data type from CHAR to VARCHAR2 etc as for the datatype date shows as date on the tool - I'm using sqldeveloper from oracle

any help would be great
Re: migration from Sybase to oracle 1 table fails with SQL Error: ORA-01841: (full) year must be between [message #560904 is a reply to message #560901] Tue, 17 July 2012 13:43 Go to previous messageGo to next message
BlackSwan
Messages: 23189
Registered: January 2009
Senior Member
>any help would be great
HELP not possible due to lack of meaningful details (not) provided by you.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: migration from Sybase to oracle 1 table fails with SQL Error: ORA-01841: (full) year must be between [message #560907 is a reply to message #560904] Tue, 17 July 2012 14:00 Go to previous messageGo to next message
evoradba
Messages: 98
Registered: April 2005
Location: Canada
Member
hi here is the table DDL
create table mytable (
        security_adp_nbr                varchar(7)                       not null  ,
        div_ann_est_amt                 decimal(17,8)                        null  ,
        record_type_cd                  char(3)                          not null  ,
        action_cd                       char(1)                          not null  ,
        payment_freq_cd                 char(1)                              null  ,
        msd_class1_cd                   char(1)                              null  ,
        msd_class2_cd                   char(1)                              null  ,
        msd_class3_cd                   char(1)                              null  ,
        msd_class4_cd                   char(1)                              null  ,
        msd_class5_cd                   char(1)                              null  ,
        msd_class6_cd                   char(1)                              null  ,
        msd_class7_cd                   char(1)                              null  ,
        country_cd                      char(2)                              null  ,
        currency_cd                     char(3)                              null  ,
        class_industry_cd               char(2)                              null  ,
        sic_cd                          char(4)                              null  ,
        marginability_cd                char(1)                              null  ,
        source_pricing_cd               char(1)                              null  ,
        desc_sec_line1_txt              char(30)                             null  ,
        taxable_cd                      char(1)                              null  ,
        country_origin_cd               char(2)                              null  ,
        expiration_dt                   datetime                             null  ,
        royalty_ind                     char(1)                              null  ,
        remic_ind                       char(1)                              null  ,
        reit_ind                        char(1)                              null  ,
        sec_use_ind                     char(1)                              null  ,
        shr_otstd_thou_qty              int                                  null  ,
        earning_share_amt               decimal(5,3)                         null  ,
        currency_iso_cd                 char(3)                              null  ,
        redenomination_dt               datetime                             null  ,
        currency_legacy_cd              char(3)                              null  ,
        curr_legacy_iso_cd              char(3)                              null  ,
        uts_canadian_cd                 char(1)                              null  ,
        annuity_cd                      char(1)                              null  ,
        swaps_ind                       char(1)                              null  ,
        oats_nasdaq_cd                  char(1)                              null  ,
        rgstr_bond_cd                   char(1)                              null  ,
        foreign_cd                      char(1)                              null  ,
        security_ida_cd                 char(4)                              null  ,
        depository_cd                   char(4)                              null  ,
        trnfr_dptry_cd                  char(4)                              null  ,
        ibm_cd                          char(6)                              null  ,
        mcgill_cd                       decimal(5,0)                         null  ,
        issue_dt                        datetime                             null  ,
        elig_tax_crdt_ind               char(1)                              null  ,
        tax_credit_rt                   decimal(5,3)                         null  ,
        tax_withold_rt                  decimal(5,3)                         null  ,
        symbol_extended_cd              char(18)                             null  ,
        dllr_us_trade_ind               char(1)                              null  ,
        taxable_can_cd                  char(1)                              null  ,
        trnfr_chrg_amt                  decimal(5,3)                         null  ,
        fctr_file_rt                    decimal(3,2)                         null  ,
        qssp_crnt_pct                   int                                  null  ,
        qssp_prev_pct                   int                                  null  ,
        sector_cd                       char(5)                              null  ,
        updt_last_tmstp DATE DEFAULT '()' NOT NULL
)
/

  updt_last_tmstp DATE DEFAULT '()' NOT NULL
)
Error at Command Line:1,716 Column:31
Error report:
SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 -  "(full) year must be between -4713 and +9999, and not be 0"
*Cause:    Illegal year entered
*Action:   Input year in the specified range


[EDITED by LF: applied [code] tags]

[Updated on: Tue, 17 July 2012 15:02] by Moderator

Report message to a moderator

Re: migration from Sybase to oracle 1 table fails with SQL Error: ORA-01841: (full) year must be between [message #560908 is a reply to message #560907] Tue, 17 July 2012 14:07 Go to previous messageGo to next message
BlackSwan
Messages: 23189
Registered: January 2009
Senior Member
Am I correct to assume that the "create table mytable " statement is for the Oracle DB?

so what exactly do you desire/expect to be contained within UPDT_LAST_TMSTP column by DEFAULT?
Re: migration from Sybase to oracle 1 table fails with SQL Error: ORA-01841: (full) year must be between [message #560910 is a reply to message #560908] Tue, 17 July 2012 14:15 Go to previous messageGo to next message
evoradba
Messages: 98
Registered: April 2005
Location: Canada
Member
Yes the create table is the DDL from sybase to oracle i got it from the sqldeveloper tool, all other tables the UPDT_LAST_TMSTP column by DEFAULT columns in oracle become sysdate expect this one im going nuts
Re: migration from Sybase to oracle 1 table fails with SQL Error: ORA-01841: (full) year must be between [message #560911 is a reply to message #560910] Tue, 17 July 2012 14:19 Go to previous messageGo to next message
BlackSwan
Messages: 23189
Registered: January 2009
Senior Member
2:18:54 SQL> connect user1/user1
Connected.
12:19:01 SQL> create table mytable (
updt_last_tmstp DATE DEFAULT sysdate NOT NULL
)
/
12:19:03   2  12:19:03   3  12:19:03   4  
Table created.

12:19:04 SQL>
Re: migration from Sybase to oracle 1 table fails with SQL Error: ORA-01841: (full) year must be between [message #560924 is a reply to message #560907] Tue, 17 July 2012 15:31 Go to previous messageGo to next message
joy_division
Messages: 4559
Registered: February 2005
Location: East Coast USA
Senior Member
I would change all the CHARs to VARCHAR2.
Re: migration from Sybase to oracle 1 table fails with SQL Error: ORA-01841: (full) year must be between [message #561054 is a reply to message #560911] Wed, 18 July 2012 10:07 Go to previous messageGo to next message
evoradba
Messages: 98
Registered: April 2005
Location: Canada
Member
thank you for all suggestions however the issue actually was in Sybase i had to copy the table out truncate it and copy back in and all good for the conversion
thanks
Helena
Re: migration from Sybase to oracle 1 table fails with SQL Error: ORA-01841: (full) year must be between [message #569813 is a reply to message #560901] Fri, 02 November 2012 00:44 Go to previous message
manish_vichare
Messages: 3
Registered: October 2012
Junior Member
Hello Maria,
Can you please let me know the steps that u fallowed to Migrate Sybase 12.5 to oracle 11G on linux platform?
I would be really that to you.
Previous Topic: export datapump issue
Next Topic: SQLLDR connection error
Goto Forum:
  


Current Time: Sun Dec 28 00:01:23 CST 2014

Total time taken to generate the page: 0.24617 seconds