| Author's name: MichaelBialik Author's Email: bialik@isdn.net.il |
Date written: 6-Feb-2002 Oracle version(s): 8.1.6/8.1.7 |
| Quite often we need to execute some logic during data load by SQL*Loader. It's possible to do it via triggers, but it is much more convinient to performs such once only tasks by calling built-in or user-defined functions. Following is an example of such call. | |
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.
Further reading: N/A