CODEWITHSUNDEEP
databaseoracle-databaseexternal
Diego
Diego

Reputation: 15

Oracle 11g External Table error

I'm trying to run a simple external table program using oracle 11g on Linux VM. The problem is that I can't query any data from .txt files.
Here's my code:

    CONN / as sysdba;

    CREATE OR REPLACE DIRECTORY DIR1 AS 'home/oracle/TEMP/X/';
    GRANT READ, WRITE ON DIRECTORY DIR1 TO user;

    CONN user/password;

    CREATE TABLE gerada
    (
        field1   INT,
        field2   Varchar2(20)
    )
    ORGANIZATION EXTERNAL
    (
            TYPE ORACLE_LOADER
            DEFAULT DIRECTORY DIR1
            ACCESS PARAMETERS 
            (
                    RECORDS DELIMITED BY NEWLINE
                    FIELDS TERMINATED BY ';'
                    MISSING FIELD VALUES ARE NULL
            )
            LOCATION ('registros.txt')
    )
    REJECT LIMIT UNLIMITED;

    --Error starts here.
    SELECT * FROM gerada;

    DROP TABLE gerada;

    DROP DIRECTORY DIR1;

Here's the error message:

ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file home/oracle/TEMP/X/GERADA_3375.log

And thats how registros.txt looks like:

    1234;hello world;

I've checked my permissions on DIR1 and I do have read/write permissions.

Any ideas?

Upvotes: 0

Views: 7828

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

ORA-29913 and ORA-29400 mean that you're unable to access to directory and/or file.

Looking carefully at the CREATE DIRECTORY command it looks like the path you're using may be mis-formatted. Try putting a forward slash at the start of the path and removing the one at the end of the path when creating the directory - e.g. CREATE OR REPLACE DIRECTORY DIR1 AS '/home/oracle/TEMP/X';.

Share and enjoy.

Upvotes: 2

Related Questions