CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloperexternal-tables
Chuck
Chuck

Reputation: 391

Unable to query external table as a script from SQL Developer

I have an external table defined as:

CREATE TABLE EXAM_BDE_ventes (
    customerNumber varchar(255),
    clerkId varchar(255),
    productId varchar(255),
    saleDate varchar(255),
    factoryId varchar(255)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
    DEFAULT DIRECTORY mydirectory
    ACCESS PARAMETERS
    (
        RECORDS DELIMITED BY newline
        SKIP 0
        CHARACTERSET UTF8
        BADFILE logs:'ventes.txt.bad'
        LOGFILE logs:'ventes.txt.log'
        FIELDS TERMINATED BY ';'
        OPTIONALLY ENCLOSED BY '"'
    )
LOCATION ('LightSaberInc.txt'))
REJECT LIMIT UNLIMITED;

The LightSaberInc.txt file is here, and has nearly 75K lines.

If I query that table as a statement (Ctrl+Enter) I can see the data from the table:

working

But when I run it as a script (F5) I don't see anything in the script output window:

not working

The log doesn't show any error.

I think this weird bug is hiding an error while I imported the csv. This error is generating other problems later in my code, such as numbers not being properly recognized when I use to_number() .

Why can't I query the external table from a script?

Upvotes: 0

Views: 1033

Answers (1)

Chuck
Chuck

Reputation: 391

Ok so actually in the script I needed to specify '\r\ninstead of newline.

I guess the file was created using an OS that doesn't use the value newline to specify a new line, but '\r\n instead.

Upvotes: 1

Related Questions