7 Replies Latest reply: Sep 8, 2013 8:43 PM by rp0428 RSS

    TIMESTAMP WITH TIME ZONE

    1007945

      Hi Team, will Import into a table I am getting the below error message

       

      Error Message

      Record 1: Rejected - Error on table MTN_BUNDLES_EXPIRY_MIG, column EXPIRY_DATE_T.
      ORA-01840: input value not long enough for date format
      

       

      The data client provide in .XLs file

       

      2013-08-31 17:14:56

      My Table Structure is

       

      CREATE TABLE tmp_mtnuga_3g_expiry_mig
      (
          MSISDN_V            VARCHAR2 (50),
          expiry_Date_t         TIMESTAMP(6) WITH TIME ZONE
          status_date_t          TIMESTAMP(6) WITH TIME ZONE
          GOT_STARTER_PACK_V  NUMBER(10)
      );
      

      I am using 2 option to import into a table

      First option using Toad ---> Import Table -- option here i am getting error like

      The format is not matched.

      Second option using SQL Loader-->

      LOAD DATA
      INFILE 'D:\ss\TT-Projects\Customer Apps\Client Dump\3GBundle.csv' 
      BADFILE 'D:\dd\TT-Projects\Customer Apps\Client Dump\3GBundle.bad'
      DISCARDFILE 'D:\dd\TT-Projects\Customer Apps\Client Dump\3GBundle.dsc'
      
      INTO TABLE  tmp_mtnuga_3g_expiry_mig
      INSERT
      (MSISDN_V,
      EXPIRY_DATE_T,
      STATUS_DATE_T,
      GOT_STARTER_PACK_V
      )
      

       

      Please guide me how solve the TimeStamp

        • 1. Re: TIMESTAMP WITH TIME ZONE
          Etbin

          Check Part II SQL*Loader I can't remember when I last used it (being server side I use External Tables), but it should turn out something like

           

          LOAD DATA 

          INFILE 'D:\ss\TT-Projects\Customer Apps\Client Dump\3GBundle.csv'  

          BADFILE 'D:\dd\TT-Projects\Customer Apps\Client Dump\3GBundle.bad' 

          DISCARDFILE 'D:\dd\TT-Projects\Customer Apps\Client Dump\3GBundle.dsc' 

          INSERT INTO TABLE  tmp_mtnuga_3g_expiry_mig 

          fields terminated by ',' optionally enclosed by '"'

          (MSISDN_V, 

          EXPIRY_DATE_T char "to_timestamp_tz(:EXPIRY_DATE_T,'yyyy-mm-dd hh24:mi:sstzh:tzm')", 

          STATUS_DATE_T char "to_timestamp_tz(:STATUS_DATE_T,'yyyy-mm-dd hh24:mi:sstzh:tzm')", 

          GOT_STARTER_PACK_V 

           

          you must adjust the mask to match your data

           

          Regards

           

          Etbin

          • 2. Re: TIMESTAMP WITH TIME ZONE
            1007945

            Getting the below error message

            EXPIRY_DATE_T Time zone related format elements not allowed in TIMESTAMP formatting


            I have change the table data size as well like below


            EXPIRY_DATE_T  TIMESTAMP (9) WITH LOCAL TIME ZONE,

            STATUS_DATE_T  TIMESTAMP (9) WITH LOCAL TIME ZONE,

            • 3. Re: TIMESTAMP WITH TIME ZONE
              Etbin

              Sorry, I cannot test it as I have just Apex Workspace available from home and you haven't posted some data sample of yours.

              You have defined your columns as timestamp with time zone and according to documentation SQL*Loader is able to manage that data type.


              Regards


              Etbin

              • 4. Re: TIMESTAMP WITH TIME ZONE
                davidp 2

                If you changed from TIMESTAMP with TIME ZONE (which needs an explicit time zone) to TIMESTAMP WITH LOCAL TIME ZONE (which implies a time zone) then you should not have time zone pieces in your format mask.

                You said your data is like "2013-08-31 17:14:56"

                so try

                EXPIRY_DATE_T char "to_timestamp(:EXPIRY_DATE_T,'yyyy-mm-dd hh24:mi:ss')",

                STATUS_DATE_T char "to_timestamp(:STATUS_DATE_T,'yyyy-mm-dd hh24:mi:ss')",

                If you get an error again, post your current table definition, current loader file, and a few lines of data.

                • 5. Re: TIMESTAMP WITH TIME ZONE
                  rp0428
                  I am using 2 option to import into a table

                  Then perhaps you should use option #3 and  download Oracle's free sql developer. It can import that data just fine.

                  http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

                  • 6. Re: TIMESTAMP WITH TIME ZONE
                    davidp 2

                    The problem in TOAD will also have been time formats - you need to tell it the format to expect, and having the TIMESTAMP WITH TIME ZONE datatype will not have helped. SQLDeveloper also has its own storage of the formats to use.

                    At the database session level, and for SQLPlus, for TIMESTAMP WITH LOCAL TIME ZONE you'd need

                    alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss';


                    In TOAD this will be either a TIMESTAMP format or a TIMESTAMP WITH LOCAL TIME ZONE format.


                    I listed the options for SQLDeveloper and SQLPlus in https://forums.oracle.com/message/11177827#11177827


                    • 7. Re: TIMESTAMP WITH TIME ZONE
                      rp0428

                      OP is the one you need to be replying to since they are the one with the problem. I really don' t care what Toads problems are.