This discussion is archived
12 Replies Latest reply: Jan 8, 2013 2:04 PM by lxiscas RSS

External table is created without data

lxiscas Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Man:

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

    Sam
  • 5. Re: External table is created without data
    lxiscas Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points