I am trying to define a SQL*Loader control file where col1 is determined by using a user function which takes a couple of parameters with values from the input file. This part is working fine.
I want to use the value returned by above function as a parameter for another user defined function and that does not work. The value sent to the second function is not the calculated value of the first column.
Is this possible?
Here is my control file
( col1 BOUNDFILLER POSITION(1:8),
col2 INTEGER EXTERNAL "schema_own.get_col2_val(:col1, :col6)",
col3 POSITION(29:33) CHAR,
col4 "sysdate",
col5 constant " ",
col6 POSITION(9:11) CHAR,
col7 "sysdate",
col8 POSITION(12:18) CHAR,
col9 POSITION(19:28) CHAR,
col10 POSITION(29:33) CHAR,
col11 POSITION(52:63) CHAR,
col12 "(col2)", --constant "TEST",
col13 "schema_own.get_col13_value(:col1, :col2)",
LAST_UPDATE_TS "sysdate",
LAST_UPDATE_USER_I constant "SQLLDR"
)
What seems to be happening is that the second parameter in get_col13_value seems to contain the FIRST character of the col6 instead of the number that col2 should have from the call to get_col2_val.
I tried col12 with both (col2) and (:col2) - same outcome.
Am I missing something?
Also, when to use col2 vs. :col2 in sql loader?
Thanks
If you consider the following simplified control file
load data
infile *
into table T
replace
trailing nullcols
( col1 BOUNDFILLER POSITION(1:8),
col2 INTEGER EXTERNAL "f1(:col1, :col6)",
col3 POSITION(10:12) CHAR,
col6 POSITION(20:22) CHAR,
col13 "f2(:col1,:col2)"
)
BEGINDATA
ABC A3 B6
EFG E3 F6
HIJ H3 I6
then what SQL Loader is doing is effectively forming the following insert statement
insert into T
( col1,
col2,
col3,
col6,
col13
values
( :col1,
f1(:col1, :col6),
:col3,
:col6,
f2(:col1,:col2)
)
So you can see why a reference to ":col2" isnt going to really assist, because nothing has been bound into col2, at the point of time you do the insert.
I'd suggest you nest the functions
load data
infile *
into table T
replace
trailing nullcols
( col1 BOUNDFILLER POSITION(1:8),
col2 INTEGER EXTERNAL "f1(:col1, :col6)",
col3 POSITION(10:12) CHAR,
col6 POSITION(20:22) CHAR,
col13 "f2(:col1,f1(:col1, :col6))"
)
BEGINDATA
ABC A3 B6
EFG E3 F6
HIJ H3 I6
I tested with the following table / functions
SQL> create table T
2 ( col1 varchar2(20),
3 col2 int,
4 col3 varchar2(10),
5 col6 varchar2(10),
6 col13 varchar2(20)
7 )
8 /
Table created.
SQL>
SQL> create or replace function F1(a varchar2,b varchar2) return int is
2 begin
3 return ascii(a)+ascii(b);
4 end;
5 /
Function created.
SQL>
SQL> create or replace function F2(a varchar2,b int) return varchar2 is
2 begin
3 return a||b;
4 end;
5 /
Function created.
and then ran sqllldr and got the desired result
SQL> select * from t;
COL1 COL2 COL3 COL6 COL13
-------------------- ---------- ---------- ---------- ----------
131 A3 B6 ABC131
139 E3 F6 EFG139
145 H3 I6 HIJ145