12 Replies Latest reply: Jan 8, 2013 4:04 PM by lxiscas RSS

    External table is created without data

    lxiscas
      Hi, guys:

      please help me on this problem: I tried to load data from bunch of csv files on linux server to external tables. However, the table is created without data. There is no warning message. but I check csv file with cat, there is data. here is query.
      create table worcs.ACBRD_0050_EXT(
      CODE VARCHAR2(4),
      POL_NBR VARCHAR2(8),
      CENT VARCHAR2(2),
      YR VARCHAR2(2),
      SEQ VARCHAR2(1),
      CLAIM_NBR VARCHAR2(4),
      SORT_INIT VARCHAR2(2),
      SORT_SEQ VARCHAR2(2),
      ENTER_CC_50 VARCHAR2(2),
      ENTER_YY_50 VARCHAR2(2),
      ENTER_MM_50 VARCHAR2(2),
      ENTER_DD_50 VARCHAR2(2),
      PREM_DUE_50 NUMBER(11,2),
      POL_STS_50 VARCHAR2(1),
      POL_AUDT_TYPE_50 VARCHAR2(1),
      CHANGE_50 VARCHAR2(1),
      REV_AUD_DED_50 VARCHAR2(1),
      AUDIT_ID_50 VARCHAR2(8),
      BILL_CC_50 VARCHAR2(2),
      BILL_YY_50 VARCHAR2(2),
      BILL_MM_50 VARCHAR2(2),
      BILL_DD_50 VARCHAR2(2)
      )
      organization external ( 
      default directory ksds
      access parameters
       ( records delimited by newline 
        badfile xtern_log_dir: 'xtern_acbrd_0050.bad'
       logfile xtern_log_dir:'xtern_acbrd_0050.log'
        discardfile xtern_log_dir:'xtern_acbrd_0050.dsc'
        ) location ('acbrd-0050.csv') ) REJECT LIMIT unlimited 
      ;
      And in linux it shows:
      [oracle@VM-OracleBI ksds]$ cat acbrd-0050.csv
      0050|00508081|1|11|1|    |  |  |1|11|10|31| 000001638.00|L|C|Y|A|CONF    | |  |  |  |
      0050|01803167|1|10|1|    |  |  |1|11|10|27| 000000896.00|L|C|Y|A|CONF    | |  |  |  |
      [oracle@VM-OracleBI ksds]$
        • 1. Re: External table is created without data
          Solomon Yakobson
          External table is not a physical table. Every time you issue SELECT from external table Oracle read corresponding file according to external table specification. What happens when you issue:
          select * from worcs.ACBRD_0050_EXT;
          Does it return any rows? Based on your post title I understand no data and no errors, right? Then you need to check log, bad and discard files. Since you specified REJECT LIMIT unlimited it is a good chance all records were rejected.

          SY.
          • 2. Re: External table is created without data
            Solomon Yakobson
            Actually, I missed your CSV file fields are separated by pipe (|), not by comma which is default field separator for external tables. Just add FIELDS TERMINATED BY '|' to table definition and you'll be fine (I used directory TEMP, substitute it with your Oracle directory names):
            SQL> create table ACBRD_0050_EXT(
              2  CODE VARCHAR2(4),
              3  POL_NBR VARCHAR2(8),
              4  CENT VARCHAR2(2),
              5  YR VARCHAR2(2),
              6  SEQ VARCHAR2(1),
              7  CLAIM_NBR VARCHAR2(4),
              8  SORT_INIT VARCHAR2(2),
              9  SORT_SEQ VARCHAR2(2),
             10  ENTER_CC_50 VARCHAR2(2),
             11  ENTER_YY_50 VARCHAR2(2),
             12  ENTER_MM_50 VARCHAR2(2),
             13  ENTER_DD_50 VARCHAR2(2),
             14  PREM_DUE_50 NUMBER(11,2),
             15  POL_STS_50 VARCHAR2(1),
             16  POL_AUDT_TYPE_50 VARCHAR2(1),
             17  CHANGE_50 VARCHAR2(1),
             18  REV_AUD_DED_50 VARCHAR2(1),
             19  AUDIT_ID_50 VARCHAR2(8),
             20  BILL_CC_50 VARCHAR2(2),
             21  BILL_YY_50 VARCHAR2(2),
             22  BILL_MM_50 VARCHAR2(2),
             23  BILL_DD_50 VARCHAR2(2)
             24  )
             25  organization external (
             26  default directory temp
             27  access parameters
             28   ( records delimited by newline
             29    badfile temp: 'xtern_acbrd_0050.bad'
             30   logfile temp:'xtern_acbrd_0050.log'
             31    discardfile temp:'xtern_acbrd_0050.dsc'
             32  FIELDS TERMINATED BY '|'
             33    ) location ('acbrd-0050.csv') ) REJECT LIMIT unlimited
             34  /
            
            Table created.
            
            SQL> select  *
              2    from ACBRD_0050_EXT
              3  /
            
            CODE POL_NBR  CE YR S CLAI SO SO EN EN EN EN PREM_DUE_50 P P C R AUDIT_ID BI BI BI BI
            ---- -------- -- -- - ---- -- -- -- -- -- -- ----------- - - - - -------- -- -- -- --
            0050 00508081 1  11 1            1  11 10 31        1638 L C Y A CONF
            0050 01803167 1  10 1            1  11 10 27         896 L C Y A CONF
            
            SQL>
            SY.
            • 3. Re: External table is created without data
              lxiscas
              Yes, you are right, there is no data, it is just a empty table without any error.
              • 4. Re: External table is created without data
                lxiscas
                Man:

                You are genius!:) I just realized this! MANY THANKS!!!!!

                Sam
                • 5. Re: External table is created without data
                  lxiscas
                  Hi, gurus:

                  please help me again on the similar problem: I tried to load data from csv files on linux server to external tables. However, some of tables are created without data. There is no warning message. but I check csv file with more command, there is data. here is query.


                  create table worcs.ACBRD_0115_EXT(
                  POL_NBR VARCHAR2(8),
                  CENT VARCHAR2(2),
                  YR VARCHAR2(2),
                  SEQ VARCHAR2(1),
                  CLAIM_NBR VARCHAR2(4),
                  SORT_INIT VARCHAR2(2),
                  SORT_SEQ VARCHAR2(2),
                  IND_0115 VARCHAR2(1),
                  CODE VARCHAR2(4)
                  )
                  organization external (
                  default directory ksds
                  access parameters
                  ( records delimited by newline
                  badfile xtern_log_dir: 'xtern_acbrd_0115.bad'
                  logfile xtern_log_dir:'xtern_acbrd_0115.log'
                  discardfile xtern_log_dir:'xtern_acbrd_0115.dsc'
                  fields terminated by '|' )
                  location ('acbrd-0115.csv') ) REJECT LIMIT unlimited
                  ;



                  and here is result when I check the corresponding file on Lunux.


                  more /u02/csv/ksds/acbrd-0115.csv
                  0115|00007342|1|10|1| | |00|E|
                  0115|00074654|1|10|1| | |00|P|
                  0115|00135674|1|10|1| | |00|P|
                  0115|00164142|1|10|1| | |00|P|
                  0115|00256041|1|10|1| | |00|P|
                  0115|00285111|1|10|1| | |00|P|
                  0115|00296058|1|10|1| | |00|P|
                  0115|00296564|1|10|1| | |00|E|
                  0115|00307028|1|10|1| | |00|P|
                  0115|00342137|1|10|1| | |00|P|
                  0115|00350606|1|10|1| | |00|E|
                  0115|00356000|1|10|1| | |00|P|
                  0115|00368700|1|10|1| | |00|P|
                  0115|00401939|1|10|1| | |00|P|
                  0115|00427933|1|10|1| | |00|P|



                  I suspect the empty cell of rows caused this problem, but I have no idea how to track down and solve it.

                  Thanks.

                  Sam
                  • 6. Re: External table is created without data
                    lxiscas
                    I was required to post in the same thread as question are very similar.

                    Edited by: lxiscas on Jan 8, 2013 11:43 AM
                    • 7. Re: External table is created without data
                      User286067
                      make sure you are still pointing to the right directory and the said file is in the expected location.
                      • 8. Re: External table is created without data
                        lxiscas
                        Yes, I checked it with find command in lunux, the file exists and has conetnt, but not show in external table. Not every external table got this problem, only from time to time. I guess missed something.

                        Thank you for your suggestion.

                        Sam
                        • 9. Re: External table is created without data
                          User286067
                          select * from dba_directories where directory_name = 'KSDS';

                          also share what is in the bad/log/discard files ?
                          • 10. Re: External table is created without data
                            lxiscas
                            select * from dba_directories where directory_name = 'KSDS';
                            OWNER     Directory name           Directory path
                            SYS     KSDS                    /u02/csv/ksds
                            I do not understand your second request.
                            • 11. Re: External table is created without data
                              User286067
                              change your create table as

                              POL_NBR VARCHAR2(8),
                              CENT VARCHAR2(8),
                              YR VARCHAR2(2),
                              SEQ VARCHAR2(2),
                              CLAIM_NBR VARCHAR2(4),
                              SORT_INIT VARCHAR2(2),
                              SORT_SEQ VARCHAR2(2),
                              IND_0115 VARCHAR2(2),
                              CODE VARCHAR2(4)


                              then you will get it. If you had looked into your LOG file as i mentioned previously, you would have found a boatload of ORA-12899: value too large for column errors.
                              • 12. Re: External table is created without data
                                lxiscas
                                Riamya:

                                Many thanks! your idea inspired me. I go ahead and checked the log, and found not only the columns you mentions but also some other columns have too small width. I learned how to check log file from you.

                                Thanks again!

                                Sam