The Oracle (tm) Users' Co-Operative FAQ

How to Call Functions From SQL*Loader.


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.

Back to index of questions


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


Back to top

Back to index of questions