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:
But when I run it as a script (F5) I don't see anything in the script output window:
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?
select * from dual) as a script? I assume you've tried quitting and relaunching SQL Developer to see if this behaviour persists...clerkidcolumn because 730 of your rows have t column set to a space; if you doto_number(trim(clerkid))it will work and those rows will return null.. Similarly 732 of thefactoryidvalues 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.to_number(). Dates are odd because it's just an exercice!