Total Pageviews

February 29, 2016

2/29/2016 06:52:00 PM
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)"

)