0

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?

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
Chuck
  • 351
  • 1
  • 6
  • 20
  • How did you import the data, and to what kind of table? – Alex Poole Nov 18 '16 at 00:28
  • I've edited my post with the request I do to create the table and import the data. – Chuck Nov 18 '16 at 10:24
  • I can't duplicate this in 4.2.0, I get the expected results in the script output window. Which version are you using? Can you run other queries (even `select * from dual`) as a script? I assume you've tried quitting and relaunching SQL Developer to see if this behaviour persists... – Alex Poole Nov 18 '16 at 10:57
  • You'll get ORA-01722 from the `clerkid` column because 730 of your rows have t column set to a space; if you do `to_number(trim(clerkid))` it will work and those rows will return null.. Similarly 732 of the `factoryid` values are a space, and would also need to be trimmed before being converted to numbers. You could do that in the external table definition, of course. The dates look odd too. Those issues are nothing to do with the script behaviour though. – Alex Poole Nov 18 '16 at 11:05
  • I am using SQL Developper 4.1.5, other queries are fine. I guess I am gonna try with 4.2.0. The spaces are not a problem, I already treat them before trying to use `to_number()`. Dates are odd because it's just an exercice! – Chuck Nov 18 '16 at 21:59
  • The problem is identical if I use SQL Developper 4.2.0. How can you have an output ? I really don't get it. – Chuck Nov 18 '16 at 22:09
  • Much thanks to you @AlexPoole for your help, even if it was something else. I really appreciated you taking time to try to help me. – Chuck Nov 18 '16 at 22:30

1 Answers1

1

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.

Chuck
  • 351
  • 1
  • 6
  • 20