SQL> desc EMPLOYEE
 Name	  Type
EMP_ID    NUMBER(10)    -- PrimaryKey
EMP_NAME  VARCHAR2(30)
DEPT_ID  NUMBER(10)     -- ForeignKey from DEPARTMENT 

SQL> desc DEPARTMENT
 Name	  Type
DEPT_ID  NUMBER(10)
DEPT_NAME  VARCHAR2(30)

myFile.txt
------------
1,Edward,Account
2,Andrew,Finance
3,Sam, IT

CONTROL FILE (SQLLOADER)
------------
load data
infile myFile.txt
append into table EMPLOYEE
FIELDS TERMINATED BY ','
(EMP_ID,
EMP_NAME,
DEPT_ID  )   <--- ?? What should do in here

SQL> create or replace function fun_deptno (par_dname in char)
  2    return number
  3  is
  4    retval number;
  5  begin
  6    select deptno
  7      into retval
  8      from dept
  9      where dname = upper(par_dname);
 10
 11    return (retval);
 12  exception
 13    when no_data_found then
 14      return null;
 15  end;
 16  /

Function created.

SQL>

load data
infile *
append
into table emp
fields terminated by ','
  (empno,
   ename,
   deptno "fun_deptno(:deptno)"
  )
  
begindata
1,Edward,Accounting
2,Andrew,Research
3,Sam,xxx

SQL> select deptno, empno, ename from emp order by empno;

    DEPTNO      EMPNO ENAME
---------- ---------- ----------
        20       7369 SMITH
        30       7499 ALLEN
        30       7521 WARD
        20       7566 JONES
        30       7654 MARTIN
        30       7698 BLAKE
        10       7782 CLARK
        20       7788 SCOTT
        10       7839 KING
        30       7844 TURNER
        20       7876 ADAMS
        30       7900 JAMES
        20       7902 FORD
        10       7934 MILLER

14 rows selected.

SQL> $sqlldr scott/tiger@ora10 control=test12.ctl log=test12.log

SQL*Loader: Release 10.2.0.1.0 - Production on ╚et Kol 11 07:53:40 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 2
Commit point reached - logical record count 3

SQL> select deptno, empno, ename from emp order by empno;

    DEPTNO      EMPNO ENAME
---------- ---------- ----------
        10          1 Edward
        20          2 Andrew
                    3 Sam
        20       7369 SMITH
        30       7499 ALLEN
        30       7521 WARD
        20       7566 JONES
        30       7654 MARTIN
        30       7698 BLAKE
        10       7782 CLARK
        20       7788 SCOTT
        10       7839 KING
        30       7844 TURNER
        20       7876 ADAMS
        30       7900 JAMES
        20       7902 FORD
        10       7934 MILLER

17 rows selected.

SQL>

First the function id created successfully no mistake:

SQL> create or replace function fun_deptno (par_dname in char)
  2    return number
  3  is
  4    retval number;
  5  begin
  6    select deptno
  7      into retval
  8      from dept
  9      where dname = upper(par_dname);
 10
 11    return (retval);
 12  exception
 13    when no_data_found then
 14      return null;
 15  end;
 16  /

Function created.

load data
infile *
append
into table emp
fields terminated by ','
  (empno,
   ename,
   deptno "fun_deptno(:deptno)"
  )
  
begindata
1,Edward,Accounting
2,Andrew,Research
3,Sam,xxx

deptno "fun_deptno(:retval)"

deptno "fun_deptno(:deptno)"
   ^                  
   |    
This DEPTNO is a table column (EMP.DEPTNO)

deptno "fun_deptno(:deptno)"
                      ^
                      |
We are passing DEPTNO value from an input file, i.e. it is a value that represents the DEPTNO.
However, as the input file contains DNAME (such as "Accounting"), we are - really - passing
DNAME to the function.
Colon in front of the parameter name (:DEPTNO) means that actual value from the input file is
used ("Accounting").