external tables problem
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: external tables problem

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I am trying to create some external tables and I get strange results.

    I have a flat file as follows

    Code:
    20020821 00:00:00;Sop Ges Arpa;1;4;1;0;0;    
    20020821 00:00:00;Sop Ges Arpa Rech;0;3;1;0;0;   
    20020821 00:00:00;Sop Ges Arpa Res;0;20;3;0;7;   
    20020821 00:00:00;Sop Ges Arpa Tra;5;0;5;0;0;   
    20020821 00:00:00;Sop Ges Portabilidad;9;1;9;0;0;
    created external table as follows

    Code:
    create table
    colas_caup
    (fecha date,
     cola VARCHAR2(45),
     ABIERTAS number,
     CERRADAS number,
     PENDIENTES number,
     RECHAZADAS number,
     RESUELTAS number)
    organization external 
    (type oracle_loader
     default directory tablas_externas
     access parameters
       (records delimited by newline
        fields terminated by ';'
        missing field values are null
          (fecha char date_format date mask 'YYYYMMDD HH24:MI:SS',
           cola,
           ABIERTAS,
           CERRADAS,
           PENDIENTES,
           RECHAZADAS,
           RESUELTAS))
     location('colas.dat'))
    reject limit unlimited;
    the problem is when I query I get only the first row back with no errors at all in the log!

    if then I change the flat file into

    Code:
    20020821 00:00:00;Sop Ges Arpa;1;4;1;0;0    
    20020821 00:00:00;Sop Ges Arpa Rech;0;3;1;0;0   
    20020821 00:00:00;Sop Ges Arpa Res;0;20;3;0;7   
    20020821 00:00:00;Sop Ges Arpa Tra;5;0;5;0;0   
    20020821 00:00:00;Sop Ges Portabilidad;9;1;9;0;0
    I get 0 rows and error in log stating:

    error processing column RESUELTAS in row 1 for datafile /tmp/colas.dat
    ORA-01722: invalid number

    I then again edit the flat file so it shows now

    Code:
    20020821 00:00:00;Sop Ges Arpa;1;4;1;0;0
    query the table and get the row back with no errors

    I cant see what the heck is going on

    Anyone tried external tables :?


  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Just check if there are any invisible characters like 'TAB' at the end of last character in flat file.

    20020821 00:00:00;Sop Ges Arpa;1;4;1;0;0

    Sameer

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    nop, there is no tabs

    in fact this flat file is one of many that feed datamart loading processes with sql loader which loads with no problems at all


  4. #4
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Just a suggestion..

    How about taking partial file for loading..

    If the file has only 1 rec and then it shows properly, then we can give a try taking records in batches... Might be one of the one of the record in the flat file has a junk character.

    may be initially 100 then next 100

    I understand file may have millions of records.. You must have shown us a sample records.. but we can give a try....

    Sameer

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well the funny thing is querying following flat file

    Code:
    20020821 00:00:00;Sop Ges Arpa;1;4;1;0;0;    
    20020821 00:00:00;Sop Ges Arpa Rech;0;3;1;0;0;   
    20020821 00:00:00;Sop Ges Arpa Res;0;20;3;0;7;   
    20020821 00:00:00;Sop Ges Arpa Tra;5;0;5;0;0;   
    20020821 00:00:00;Sop Ges Portabilidad;9;1;9;0;0;
    I get 20020821 00:00:00;Sop Ges Arpa;1;4;1;0;0; back as result

    if i change the file to

    Code:
    20020821 00:00:00;Sop Ges Arpa Rech;0;3;1;0;0;   
    20020821 00:00:00;Sop Ges Arpa Res;0;20;3;0;7;   
    20020821 00:00:00;Sop Ges Arpa Tra;5;0;5;0;0;   
    20020821 00:00:00;Sop Ges Portabilidad;9;1;9;0;0;
    20020821 00:00:00;Sop Ges Arpa;1;4;1;0;0;
    I get 20020821 00:00:00;Sop Ges Arpa Rech;0;3;1;0;0; back as result so the data are not bad, the problem is it only reads the first row

    and if I have 1 million rows obviously I am not gonna split it into 1 million files

  6. #6
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588

    Red face

    Originally posted by pando
    well the funny thing is querying following flat file
    if I have 1 million rows obviously I am not gonna split it into 1 million files
    Ofcourse not.. But I have tried the same code at my home, I just copy-pasted everything and it worked without a single error

    I am using WinXP, O9i R2. Heres the output.. There must be something wrong with the flatfile only.. No syntax error.. nothing.. Following is the spooled output..


    Code:
    SQL> CREATE OR REPLACE DIRECTORY tablas_externas AS 'c:\oracle\etl_file';
    
    Directory created.
    
    SQL> CREATE TABLE
      2  colas_caup
      3  (fecha DATE,
      4   cola VARCHAR2(45),
      5   ABIERTAS NUMBER,
      6   CERRADAS NUMBER,
      7   PENDIENTES NUMBER,
      8   RECHAZADAS NUMBER,
      9   RESUELTAS NUMBER)
     10  ORGANIZATION EXTERNAL
     11  (TYPE oracle_loader
     12   DEFAULT DIRECTORY tablas_externas
     13   ACCESS PARAMETERS
     14     (RECORDS DELIMITED BY NEWLINE
     15      FIELDS TERMINATED BY ';'
     16      MISSING FIELD VALUES ARE NULL
     17        (fecha CHAR date_format DATE MASK 'YYYYMMDD HH24:MI:SS',
     18         cola,
     19         ABIERTAS,
     20         CERRADAS,
     21         PENDIENTES,
     22         RECHAZADAS,
     23         RESUELTAS))
     24   LOCATION('colas.dat'))
     25  REJECT LIMIT UNLIMITED;
    
    Table created.
    
    SQL> 
    SQL> SELECT * FROM colas_caup;
    
    FECHA     COLA                                            ABIERTAS   CERRADAS PENDIENTES RECHAZADAS  RESUELTAS                                                                         
    
    --------- --------------------------------------------- ---------- ---------- ---------- ---------- ----------
    
    21-AUG-02 Sop Ges Arpa                                           1          4          1          0          0                                                                                        
    21-AUG-02 Sop Ges Arpa Rech                                      0          3          1          0          0                                                                                        
    21-AUG-02 Sop Ges Arpa Res                                       0         20          3          0          7                                                                                        
    21-AUG-02 Sop Ges Arpa Tra                                       5          0          5          0          0
    21-AUG-02 Sop Ges Portabilidad                                   9          1          9          0          0                                                                                        
    
    5 rows selected.
    I will be glad if u reply back after solving the problem

    Regards
    Sameer

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hmmm this is gettng my nerves, I just cut and pasted in another server and it works

    bummer :-(

  8. #8
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    You probably have problems with 'newline'. It can be CHR(13) or CHR(10)||CHR(13) together. On of the above is giving you problems.

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I am not sure, the dat is generated by sql*plus spool

  10. #10
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    I think that sqlplus spool adds blanks en every result row until linesize.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width