CREATE OR REPLACE FUNCTION get_jobplanid
  (p_jpnum IN jobplan.jpnum%TYPE)
  RETURN      jobplan.jobplanid%TYPE
AS
  v_jobplanid jobplan.jobplanid%TYPE;
BEGIN
  SELECT jobplan.JOBPLANID 
  INTO   v_jobplanid
  FROM   jobplan 
  WHERE  jobplan.jpnum = p_jpnum;
  RETURN v_jobplanid;
END get_jobplanid;
/

(
        LINE_NUMBER			SEQUENCE(COUNT),
		ROW_1        POSITION(4:15) "SUBSTR(:ROW_1,1,8)",
        CODE_INFORMATION    POSITION(16:19),
        INFORMATION_DATA    POSITION(20) CHAR(4000),
        DATE_TRAITEMENT		EXPRESSION	"(SELECT TITI FROM TOTO WHERE DATE_CREATION = (SELECT MAX(DATE_CREATION) FROM TOTO))"

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> create table x1 (x1 VARCHAR2(5),
  2  x2 VARCHAR2(55),
  3  x3 VARCHAR2(55));

Table created.

SQL> desc tm
 Name                     Null?    Type
 ------------------------ -------- ------------------------------------
 XNAME                    NOT NULL VARCHAR2(8)

SQL> select * from tm where rownum <= 1;

XNAME
--------
*****

SQL> !cat x.ctl
LOAD DATA
INFILE 'a.csv'
replace
INTO table x1
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(x1,
 x2 expression "select xname from tm where rownum <= 1",
 x3)

SQL> !cat a.csv
A,ABC,NONE
B,DEF,NONE
C,EFG,NONE

SQL> !sqlldr control=x.ctl
Username:scott/tiger@testdb
SQL*Loader: Release 10.1.0.4.2 - Production on Tue May 24 13:32:06 2011

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

Commit point reached - logical record count 3

SQL> !cat x.log

SQL*Loader: Release 10.1.0.4.2 - Production on Tue May 24 13:32:06 2011

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

Control File:   x.ctl
Data File:      a.csv
  Bad File:     a.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table X1, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
X1                                  FIRST     *   ,  O(") CHARACTER
X2                                                        EXPRESSION
    SQL string for column : "select xname from tm where rownum <= 1"
X3                                   NEXT     *   ,  O(") CHARACTER

Record 1: Rejected - Error on table X1, column X2.
ORA-00936: missing expression

Record 2: Rejected - Error on table X1, column X2.
ORA-00936: missing expression

Record 3: Rejected - Error on table X1, column X2.
ORA-00936: missing expression


Table X1:
  0 Rows successfully loaded.
  3 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  33024 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             3
Total logical records rejected:         3
Total logical records discarded:        0

Run began on Tue May 24 13:32:06 2011
Run ended on Tue May 24 13:32:10 2011

Elapsed time was:     00:00:04.03
CPU time was:         00:00:00.05

SQL> create table x1 (x1 VARCHAR2(5), x2 VARCHAR2(55), x3 VARCHAR2(55));

Table created.

SQL>  create table tm (XNAME                    VARCHAR2(8) );

Table created.

SQL> insert into tm values ('Michel');

1 row created.

SQL> commit;

Commit complete.

SQL> host sqlldr michel/michel control=x.ctl

SQL*Loader: Release 10.2.0.4.0 - Production on Mar. Mai 24 21:28:01 2011

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

Commit point reached - logical record count 3

SQL> select * from x1;
X1    X2                                                      X3
----- ------------------------------------------------------- ----------------
A     Michel                                                  ABC
B     Michel                                                  DEF
C     Michel                                                  EFG

3 rows selected.

SQL> host type x.ctl
LOAD DATA
INFILE 'a.csv'
replace
INTO table x1
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(x1,
 x2 expression "(select xname from tm where rownum <= 1)",
 x3)