How to use User Defined Functions in SQL*Loader?
CREATE OR REPLACE FUNCTION xxemp_fun (hdate DATE)
RETURN VARCHAR2
IS
l_date VARCHAR2 (10);
BEGIN
SELECT TO_CHAR (hdate, 'MON') INTO l_date FROM DUAL;
RETURN l_date;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error');
END;
Control file
LOAD DATA
INFILE 'C:\file_1.dat'
TRUNCATE
INTO TABLE testemp
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
hdate date "xxemp_fun(:hdate)"
)
CREATE OR REPLACE FUNCTION xxemp_fun (hdate DATE)
RETURN VARCHAR2
IS
l_date VARCHAR2 (10);
BEGIN
SELECT TO_CHAR (hdate, 'MON') INTO l_date FROM DUAL;
RETURN l_date;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error');
END;
Control file
LOAD DATA
INFILE 'C:\file_1.dat'
TRUNCATE
INTO TABLE testemp
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
hdate date "xxemp_fun(:hdate)"
)
