loading bfile via SQL Loader

Question

I am trying to load values into a table who's one of the columns is BFILE using SQL Loader.

My table looks like this:

create table documents 
    ( id number primary key
    , text bfile)

Here are my CTL adn DAT files:

loader.ctl

load data
infile d':\test\loader.dat'
into table documents
replace
fields terminated by ';'
    ( id integer
    , text bfilename('MY_DIRECTORY', 'my_file.txt') terminated by eof)

loader.dat

3;my_file.txt

When I execute sqlldr command with paramters above, I received the error message:

SQL*Loader-350: Suntax error at line 7.

Expecting "," or ")", found "bfilename".

    , text bfilename('MY_DIRECTORY', 'my_file.txt') terminated by eof)

    ^

Am I doing something wrong or SQL Loader do not accept BFILEs?

Thank you,

Answer 1

The documentation has a section on loading BFILE columns.

You need to have a filler column that will represent the filename field in your data file, and then refer to that filler field name in the bfile() - and not bfilename() - field definition:

load data
infile d:\test\loader.dat
into table documents
replace
fields terminated by ';'
    ( id
    , filename filler
    , text bfile(constant 'MY_DIRECTORY', filename) )

You don't want your ID field to be declared as integer; this is a full-word binary integer, and you probably won't get the value you expect in your table column.

If you want to explicitly convert to a number you can do:

...
fields terminated by ';'
    ( id "to_number(:id)"
    , filename filler
    , text bfile(constant 'MY_DIRECTORY', filename) )

but implicit conversion will usually be OK too.