Solved

External Tables 11g

Posted on 2013-06-07
6
464 Views
Last Modified: 2013-06-11
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!
0
Comment
Question by:carlino70
6 Comments
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 125 total points
Comment Utility
With oracle xe on windows7  
I get an error on the status column,
(ORA-12899: value too large for column STATUS (actual: 10, maximum: 1)
but that sounds reasable as it  has a length of 1 and the 5e column in de data has a value starting with  1364505600
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
I need to clarify something:  Are your field terminators tabs or spaces?

If they are spaces, this is likely the issue.  When you say terminated by '<space>', I'm pretty sure it means one space.

Looks like you have fixed width.  You will need to specify column positions like this:

...
                  TAGID (1:4) INTEGER,
                  TIME  (5:18) INTEGER
...
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 125 total points
Comment Utility
Remove the "multiple" spaces from your data and keep only one space between fields. Don't forget additional space for "status" between VALUE and MINTIME:
:p
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

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:carlino70
Comment Utility
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 '||'
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
You need fixed width or delimited text.  A single space would work as a delimiter.  Just not multiple spaces.

If you can change the feed, then adding delimiters will work.
0
 

Author Comment

by:carlino70
Comment Utility
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now