I have asked the same question on StackOveflow. Do not know, if this site is linked to that or not and thus whether that question will be seen by professionals here, so I am adding it here. Please let me know, if this is inappropriate to do so.
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 possble? Can someone give me a sample?
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 the second parameter in get_col13_value seems to be containing 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?
Can someone also help me understand when to use col2 vs. :col2 in sql loader?