Invoke function from SQL*Loader

It is possible to call any built-in or user-defined function during load process. Usually it's done for date columns, when non-default date format must be used, however user-defined function(s) may be called to perform some application logic.

The syntax for such calls is quite simple:

LOAD DATA
INFILE *
APPEND
INTO TABLE dept (
deptno POSITION(01:02) INTEGER EXTERNAL,
dname POSITION(03:16) CHAR "LDR_PCK.NAME(:dname, :deptno)",
loc POSITION(17:29) CHAR
)
BEGINDATA
21Dep Loc
22Dep Loc
Here LDR_PCK.NAME is name of package and function, :dnname and :deptno are parameters.

When calling user-defined functions we must remember that only conventional path may be used. An attempt to use direct path will cause an error:

SQL*Loader-00417 SQL String (on column column_name) not allowed in direct path.