How do I use a custom function in a SQL*Loader script?

2011-01-01

1292

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 SQLLoader. 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.

How do I use a custom function in a SQL*Loader script?

The Oracle (tm) Users' Co-Operative FAQ

How to Call Functions From SQL*Loader.

评论