Link to home
Start Free TrialLog in
Avatar of carlino70
carlino70Flag for Argentina

asked on

External Tables 11g

Hello people, I have encountered a problem using an external table.

I have to load in these tables, the data from flat files in Linux:

I have created the directory:
CREATE OR REPLACE DIRECTORY 
DATOS_SUR_5MIN AS 
'/cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_HORA_SUR/DIVIDIR';

Open in new window


and the external table:
CREATE TABLE EXT_5MIN_033
(
  TAGID      INTEGER,
  TIME       INTEGER,
  OFFSET     INTEGER,
  VALUE      FLOAT(126),
  STATUS     VARCHAR2(1 CHAR),
  MINTIME    INTEGER,
  MINOFFSET  INTEGER,
  MINIMUM    FLOAT(126),
  MINSTATUS  VARCHAR2(1 CHAR),
  MINMILLI   INTEGER,
  MAXTIME    INTEGER,
  MAXOFFSET  INTEGER,
  MAXIMUM    FLOAT(126),
  MAXSTATUS  VARCHAR2(30 BYTE),
  MAXMILLI   INTEGER,
  AVERAGE    FLOAT(126),
  AVGSTATUS  VARCHAR2(30 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY DATOS_SUR_5MIN
     ACCESS PARAMETERS 
       ( records delimited by newline
         fields terminated by ' '
         missing field values are null
        
          )
     LOCATION (DATOS_SUR_5MIN:'DATOS_HORA.3786')
  )
REJECT LIMIT UNLIMITED
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
NOMONITORING;

Open in new window


the example of the source data is DATOS_HORA.3786:
3786    1364504400      -180    13.390037536621094              1364505600      -180    13.007961273193359              0       1364504700      -180    13.411350250244141              0       13.163140296936035
3786    1364508000      -180    13.428221702575684              1364508000      -180    13.428221702575684              0       1364511000      -180    15.850734710693359              0       14.798755645751953
3786    1364511600      -180    15.880736351013184              1364512200      -180    15.788508415222168              0       1364513400      -180    16.082494735717773              0       15.985588073730469
3786    1364515200      -180    15.998467445373535              1364518200      -180    15.363040924072266              0       1364515200      -180    15.998467445373535              0       15.72225284576416
3786    1364518800      -180    15.423227310180664              1364521800      -180    14.127524375915527              0       1364518800      -180    15.423227310180664              0       14.638666152954102
3786    1364522400      -180    13.884222984313965              1364525700      -180    13.07064151763916               0       1364522400      -180    13.884222984313965              0       13.512190818786621
3786    1364526000      -180    13.030911445617676              1364529300      -180    11.904120445251465              0       1364526000      -180    13.030911445617676              0       12.493278503417969
3786    1364529600      -180    11.773018836975098              1364532900      -180    10.752440452575684              0       1364530200      -180    11.939548492431641              0       11.386655807495117
3786    1364533200      -180    10.999876976013184              1364535600      -180    10.223763465881348              0       1364533200      -180    10.999876976013184              0       10.524721145629883
3786    1364536800      -180    10.241568565368652              1364539200      -180    9.8797225952148438              0       1364537400      -180    10.246652603149414              0       10.077794075012207
3786    1364540400      -180    9.8859615325927734              1364543700      -180    9.6615476608276367              0       1364540700      -180    10.046317100524902              0       9.8492593765258789
3786    1364544000      -180    9.7599267959594727              1364544600      -180    9.5822505950927734              0       1364545500      -180    9.9880523681640625              0       9.7985029220581055
3786    1364547600      -180    9.9394035339355469              1364547900      -180    9.9350290298461914              0       1364548800      -180    10.464407920837402              0       10.181318283081055
3786    1364551200      -180    9.954559326171875               1364553900      -180    8.9546394348144531              0       1364551200      -180    9.954559326171875               0       9.2441253662109375
3786    1364554800      -180    9.4089059829711914              1364555100      -180    9.0927314758300781              0       1364558100      -180    9.77398681640625                0       9.4332675933837891

Open in new window


When I do:
select * from ext_5min_033

Open in new window

no rows is selected.
and the log says:
  
LOG file opened at 06/07/13 20:09:07

Field Definitions for table EXT_5MIN_033
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source:

    TAGID                           CHAR (255)
      Terminated by " "
      Trim whitespace same as SQL Loader
    TIME                            CHAR (255)
      Terminated by " "
      Trim whitespace same as SQL Loader
    OFFSET                          CHAR (255)
      Terminated by " "
      Trim whitespace same as SQL Loader
    VALUE                           CHAR (255)
      Terminated by " "
      Trim whitespace same as SQL Loader
    STATUS                          CHAR (255)
      Terminated by " "
      Trim whitespace same as SQL Loader
    MINTIME                         CHAR (255)
      Terminated by " "
      Trim whitespace same as SQL Loader
    MINOFFSET                       CHAR (255)
      Terminated by " "
      Trim whitespace same as SQL Loader
    MINIMUM                         CHAR (255)
      Terminated by " "
      Trim whitespace same as SQL Loader
    MINSTATUS                       CHAR (255)
      Terminated by " "
      Trim whitespace same as SQL Loader
    MINMILLI                        CHAR (255)
      Terminated by " "
      Trim whitespace same as SQL Loader
    MAXTIME                         CHAR (255)
      Terminated by " "
      Trim whitespace same as SQL Loader
    MAXOFFSET                       CHAR (255)
      Terminated by " "
     Trim whitespace same as SQL Loader
    MAXMILLI                        CHAR (255)
      Terminated by " "
      Trim whitespace same as SQL Loader
    AVERAGE                         CHAR (255)
      Terminated by " "
      Trim whitespace same as SQL Loader
    AVGSTATUS                       CHAR (255)
      Terminated by " "
      Trim whitespace same as SQL Loader
error processing column TAGID in row 1 for datafile /cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_HORA_SUR/DIVIDIR/DATOS_HORA.3786
ORA-01722: número no válido
error processing column TAGID in row 2 for datafile /cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_HORA_SUR/DIVIDIR/DATOS_HORA.3786
ORA-01722: número no válido
error processing column TAGID in row 3 for datafile /cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_HORA_SUR/DIVIDIR/DATOS_HORA.3786
ORA-01722: número no válido
error processing column TAGID in row 4 for datafile /cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_HORA_SUR/DIVIDIR/DATOS_HORA.3786
ORA-01722: número no válido
error processing column TAGID in row 5 for datafile /cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_HORA_SUR/DIVIDIR/DATOS_HORA.3786
ORA-01722: número no válido
error processing column TAGID in row 6 for datafile /cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_HORA_SUR/DIVIDIR/DATOS_HORA.3786
ORA-01722: número no válido
error processing column TAGID in row 7 for datafile /cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_HORA_SUR/DIVIDIR/DATOS_HORA.3786
ORA-01722: número no válido
error processing column TAGID in row 8 for datafile /cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_HORA_SUR/DIVIDIR/DATOS_HORA.3786
ORA-01722: número no válido
error processing column TAGID in row 9 for datafile /cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_HORA_SUR/DIVIDIR/DATOS_HORA.3786
ORA-01722: número no válido
error processing column TAGID in row 10 for datafile /cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_HORA_SUR/DIVIDIR/DATOS_HORA.3786
ORA-01722: número no válido
error processing column TAGID in row 11 for datafile /cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_HORA_SUR/DIVIDIR/DATOS_HORA.3786
ORA-01722: número no válido
error processing column TAGID in row 12 for datafile /cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_HORA_SUR/DIVIDIR/DATOS_HORA.3786
ORA-01722: número no válido
error processing column TAGID in row 13 for datafile /cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_HORA_SUR/DIVIDIR/DATOS_HORA.3786
ORA-01722: número no válido
error processing column TAGID in row 14 for datafile /cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_HORA_SUR/DIVIDIR/DATOS_HORA.3786
ORA-01722: número no válido
error processing column TAGID in row 15 for datafile /cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_HORA_SUR/DIVIDIR/DATOS_HORA.3786

Open in new window

Any idea wath el the error?

Thank you!
SOLUTION
Avatar of flow01
flow01
Flag of Netherlands image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of MikeOM_DBA
MikeOM_DBA
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of carlino70
carlino70
Flag of Argentina image

ASKER

Look in this line:

3786    1364504400      -180    13.390037536621094              1364505600      -180    13.007961273193359              0       1364504700      -180    13.411350250244141              0       13.163140296936035

Open in new window

between 13.390037536621094(column VALUE) and 1364505600(column MINTIME) there is a 'NULL' value belong to STATUS column

The same case appears in other

I can't fix the posicions exactly, because the values extension are variable, with the float values

As should build creation options to replace the outer table tabulated spaces, with "| |" in the data file?

It would be well?

fields terminated by '||'
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of carlino70
carlino70
Flag of Argentina image

ASKER

Thanks everybody!

I resolved the issue with Linux, using 'awk' :

#! /bin/ksh

 awk -F"\t" '{print $1"||"$2"||"$4"||"$5"||"$6"||"$8"||"$9"||"$11"||"$13"||"$14"||"$16"||"$17}'  < 1dia_sur.dat > 1dia_sur2.dat