While creating external table with SQL loader to load data, I used "FILLER" keyword specified as in oracle docs as below.
A filler field, specified by BOUNDFILLER or FILLER is a data file mapped field that does not correspond to a database column. Filler fields are assigned values from the data fields to which they are mapped. Keep the following in mind regarding filler fields: The syntax for a filler field is same as that for a column-based field, except that a filler field's name is followed by FILLER.
Here is the link:
But when selecting from external table i am getting below error on the column FIRST_NAME
KUP-01005: syntax error: found "identifier": expecting one of: "binary_double, binary_float, comma, char, convert_error, date, defaultif, decimal, double, float, integer, (, lls, lls_compat, no, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar, varraw, varcharc, zoned"
KUP-01008: the bad identifier was: FILLER
What is causing the issue or i am using it in the wrong way ? But using a Char variable is working fine.
Here is what I did
create table etbl
(EMPLOYEE_ID number,
EMAIL varchar2(200),
SALARY number)
organization external
(
type oracle_loader
default DIRECTORY dir1
access parameters
(
records delimited by NEWLINE
skip 1
logfile 'log.txt'
badfile 'emp.bad'
fields terminated by ','
optionally ENCLOSED by '"'
MISSING field values are null
(
EMPLOYEE_ID char(200),
FIRST_NAME FILLER,
FILLER char(200),
EMAIL char(200),
FILLER char(200),
FILLER char(200),
FILLER char(200),
SALARY char(200),
FILLER char(200),
FILLER char(200),
FILLER char(200)
)
)
location ('emp.txt')
)
reject limit 10;
select * from etbl;
To me, it seems that you were mislead by "SQL Loader" words.
SQL Loader is utility used to load data from a file stored in directory/folder accessible to you; nice feature is that it can be your own PC, doesn't have to be database server. It is ran by calling sqlldr.exe from your operating system command prompt.
External table is a different feature; it is created in database (schema), at SQL level, using the create table ... organization external statement, following syntax appropriate for external tables. It lets you access data stored in a file which is stored in directory/folder - usually located on a database server - that is also source for Oracle object named directory that is created by sys who then grants appropriate privileges (read and/or write) to database user who will be using it. type oracle_loader represents access driver used to load data; that's not SQL Loader! From documentation:
The access driver runs inside the database server. This behavior is different from SQL*Loader, which is a client program that sends the data to be loaded over to the server.
In the end, it means that parameters you use with SQL*Loader utility (filler included) aren't the same as you use with external table.