2 Replies Latest reply: Jul 24, 2013 8:19 PM by Tae Kim RSS

    unable to upload text data using ttbulkcp

    Tae Kim

      I have data in text format.

       

      [timesten@atd data2]$ cat SYS_T_INVOICE.dat | wc -l

      4178852

       

      [timesten@atd data2]$ head SYS_T_INVOICE.dat

      125189341@"201201016053068"@301.3@2011/12/31 00:00:00:000000@21703689@2@1018635@1@3@100478@2096

      125189362@"201201016053070"@107.01@2011/12/31 00:00:00:000000@370909@1@1018635@1@3@100478@2096

      125189400@"201201016053072"@60.35@2011/12/31 00:00:00:000000@370907@2@1018635@1@3@100478@2096

      125189445@"201201016053074"@175.4@2011/12/31 00:00:00:000000@370802@2@1018635@1@3@100478@2096

      125189413@"201201016053073"@256.93@2011/12/31 00:00:00:000000@370898@2@1018635@1@3@100478@2096

      125189501@"201201016053076"@74.69@2011/12/31 00:00:00:000000@70348751@2@1018635@1@3@100478@2096

      125192895@"201201026053274"@14.04@2011/12/31 00:00:00:000000@44844315@2@1002522@1@3@100146@2017

      125192922@"201201026053276"@57.13@2011/12/31 00:00:00:000000@44844315@2@1002522@1@3@100146@2017

      125192936@"201201026053277"@131.27@2011/12/31 00:00:00:000000@74768001@1@1002522@1@3@100146@2017

      125192903@"201201026053275"@100.1@2011/12/31 00:00:00:000000@503106@2@1002522@1@3@100146@2017

       

      and I tried to upload data using ttbulkcp, but I'm getting an error.

       

       

      [timesten@atd data2]$ ttbulkcp -i -s "@" -dformat="YYYY/MM/DD HH24:MI:SS:FF6" -Q 1 -e SYS_T_INVOICE.err -connstr "dsn=idea" T_INVOICE SYS_T_INVOICE.dat

       

       

      SYS_T_INVOICE.dat:

          0 rows inserted

          1 row not inserted due to errors

          1 row total (remaining rows skipped due to errors)

      [timesten@atd data2]$ cat SYS_T_INVOICE.err

      ##ttBulkCp:FSEP=@:DFORMAT='YYYY/MM/DD HH24:MI:SS:FF6'

       

       

      ###

      ### Errors/warnings from file SYS_T_INVOICE.dat:

      ###

       

       

      # While processing row 1 (global row 1) in file SYS_T_INVOICE.dat, line 1:

      ### ERROR at character 32:

      ###    While reading value for column 2 (INVOICE_NUMBER) of type VARCHAR2(20):

      ###    String is longer than maximum column length.

      125189341@"201201016053068"@301.3@2011/12/31 00:00:00:000000@21703689@2@1018635@1@3@100478@2096

      #                              *

      # (row was not inserted)

       

       

      I copied a result of "head SYS_T_INVOICE.dat" and created a file called a.txt

       

      [timesten@atd data2]$ cat a.txt

      125189341@"201201016053068"@301.3@2011/12/31 00:00:00:000000@21703689@2@1018635@1@3@100478@2096

      125189362@"201201016053070"@107.01@2011/12/31 00:00:00:000000@370909@1@1018635@1@3@100478@2096

      125189400@"201201016053072"@60.35@2011/12/31 00:00:00:000000@370907@2@1018635@1@3@100478@2096

      125189445@"201201016053074"@175.4@2011/12/31 00:00:00:000000@370802@2@1018635@1@3@100478@2096

      125189413@"201201016053073"@256.93@2011/12/31 00:00:00:000000@370898@2@1018635@1@3@100478@2096

      125189501@"201201016053076"@74.69@2011/12/31 00:00:00:000000@70348751@2@1018635@1@3@100478@2096

      125192895@"201201026053274"@14.04@2011/12/31 00:00:00:000000@44844315@2@1002522@1@3@100146@2017

      125192922@"201201026053276"@57.13@2011/12/31 00:00:00:000000@44844315@2@1002522@1@3@100146@2017

      125192936@"201201026053277"@131.27@2011/12/31 00:00:00:000000@74768001@1@1002522@1@3@100146@2017

      125192903@"201201026053275"@100.1@2011/12/31 00:00:00:000000@503106@2@1002522@1@3@100146@2017

       

      and I tried to upload data from a.txt

       

       

      [timesten@atd data2]$ ttbulkcp -i -s "@" -dformat="YYYY/MM/DD HH24:MI:SS:FF6" -Q 1 -e SYS_T_INVOICE.err -connstr "dsn=idea" T_INVOICE a.txt

       

       

      a.txt:

          10 rows inserted

          10 rows total

      [timesten@atd data2]$ ttisql "dsn=idea"

       

       

      Copyright (c) 1996, 2013, Oracle and/or its affiliates. All rights reserved.

      Type ? or "help" for help, type "exit" to quit ttIsql.

       

       

       

       

       

       

      connect "dsn=idea";

      Connection successful: DSN=idea;UID=timesten;DataStore=/home_sata/timesten/TimesTen/tt1122/idea/idea;DatabaseCharacterSet=UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/home_sata/timesten/TimesTen/tt1122/lib/libtten.so;LogDir=/home_sata/timesten/TimesTen/tt1122/idea/logs;PermSize=10240;TempSize=512;TypeMode=0;

      (Default setting AutoCommit=1)

      Command> select * from t_invoice;

      < 125189341, 201201016053068, 301.3, 2011-12-31 00:00:00, 21703689, 2, 1018635, 1, 3, 100478, 2096 >

      < 125189362, 201201016053070, 107.01, 2011-12-31 00:00:00, 370909, 1, 1018635, 1, 3, 100478, 2096 >

      < 125189400, 201201016053072, 60.35, 2011-12-31 00:00:00, 370907, 2, 1018635, 1, 3, 100478, 2096 >

      < 125189413, 201201016053073, 256.93, 2011-12-31 00:00:00, 370898, 2, 1018635, 1, 3, 100478, 2096 >

      < 125189445, 201201016053074, 175.4, 2011-12-31 00:00:00, 370802, 2, 1018635, 1, 3, 100478, 2096 >

      < 125189501, 201201016053076, 74.69, 2011-12-31 00:00:00, 70348751, 2, 1018635, 1, 3, 100478, 2096 >

      < 125192895, 201201026053274, 14.04, 2011-12-31 00:00:00, 44844315, 2, 1002522, 1, 3, 100146, 2017 >

      < 125192903, 201201026053275, 100.1, 2011-12-31 00:00:00, 503106, 2, 1002522, 1, 3, 100146, 2017 >

      < 125192922, 201201026053276, 57.13, 2011-12-31 00:00:00, 44844315, 2, 1002522, 1, 3, 100146, 2017 >

      < 125192936, 201201026053277, 131.27, 2011-12-31 00:00:00, 74768001, 1, 1002522, 1, 3, 100146, 2017 >

      10 rows found.

       

       

      data in a.txt is a subset of SYS_T_INVOICE(actually, first 10 data), but it is uploaded without an error.