CODEWITHSUNDEEP
oracle-database
Laerte Junior
Laerte Junior

Reputation: 382

Oracle External Tables do not return data

for instance I have this file downloaded from wikipedia

https://dumps.wikimedia.org/other/pageviews/2018/2018-04/pageviews-20180407-130000.gz

After donwload and unzip I am trying to return the data using external tables, but no data is returned. I realized (or I guess) that the delimiter in the file is space char(' '). Here is my code :

    CREATE TABLE Funcionarios_Externa (
Campo1 VARCHAR2(4000)
,campo2 VARCHAR2(4000)
,campo3 VARCHAR2(4000)
,campo4 VARCHAR2(4000)
,campo5 VARCHAR2(4000)
) ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY DADOS_LOGISTICA
ACCESS PARAMETERS (
RECORDS DELIMITED BY newline
READSIZE 200048576
SKIP 1
FIELDS TERMINATED BY ' ' OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
)
LOCATION ('pageviews-20170301-000000')
) REJECT LIMIT UNLIMITED;

I am not sure on what I am doing wrong. No errors, just the data is not returned.

Can someone help me ? Thanks in advance

enter image description here

This is the log file and no badfile is generated (I added the bad file clause in the script

Field Definitions for table FUNCIONARIOS_EXTERNA
  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: 

    CAMPO1                          CHAR (4000)
      Terminated by " "
      Enclosed by """ and """
      Trim whitespace same as SQL Loader
    CAMPO2                          CHAR (4000)
      Terminated by " "
      Enclosed by """ and """
      Trim whitespace same as SQL Loader
    CAMPO3                          CHAR (4000)
      Terminated by " "
      Enclosed by """ and """
      Trim whitespace same as SQL Loader
    CAMPO4                          CHAR (4000)
      Terminated by " "
      Enclosed by """ and """
      Trim whitespace same as SQL Loader
    CAMPO5                          CHAR (4000)
      Terminated by " "
      Enclosed by """ and """
      Trim whitespace same as SQL Loader

Upvotes: 0

Views: 2331

Answers (2)

Vijay Balebail
Vijay Balebail

Reputation: 185

Based on the link with sample file to load, i observered that the files does not have comma separation..it have whitespacce seperation. I changed the file name to have a .txt just in case.. Then could create external table syntax as below using sql*developer .. 

SET DEFINE OFF
--CREATE OR REPLACE DIRECTORY Enter New Dir AS 'M:\extract';
--GRANT READ ON DIRECTORY Enter New Dir TO USER;
--GRANT WRITE ON DIRECTORY Enter New Dir TO USER;
--drop table vijay_ext3;
CREATE TABLE vijay_ext3 
( c1 VARCHAR2(300),
  c2 VARCHAR2(128),
  c3 NUMBER(3),
  c4 NUMBER(3))
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY DB_DUMP_DIR
     ACCESS PARAMETERS 
       (records delimited BY '\n' 
           DB_DUMP_DIR:'pageviews.bad'
           DB_DUMP_DIR:'pageviews.discard'
           DB_DUMP_DIR:'pageviews.log'
           skip 1 
           fields terminated BY WHITESPACE
           OPTIONALLY ENCLOSED BY '"' AND '"'
           lrtrim
           missing field VALUES are NULL
           ( c1 CHAR(4000),
             c2 CHAR(4000),
             c3 CHAR(4000),
             c4 CHAR(4000)
           )
       )
     LOCATION ('pageviews.txt')
  )
  REJECT LIMIT UNLIMITED;

select * from vijay_ext3 WHERE ROWNUM <= 100;

Upvotes: 1

Vijay Balebail
Vijay Balebail

Reputation: 185

If external tables works sometimes and not others , you are probably connecting to a RAC db.. -- To ensure you are connecting to the instance where the file is located, you need to add INSTANCE_NAME the tnsnames.ora. if you are using easyconnect, then you can add instance name as well. Eg. connect vijay/oracle@//10.0.10.101:1521/vijay/vijay1

connect username/password@//hostname:port/service_name/instance_name

If this is not a RAC instance, then clearly the issue is in DIRECTORY info as you are bond to have a bad file there or a Oracle error during select or create.

Upvotes: 0

Related Questions