Using output value of column from function for another column in SQL*Loader

Question

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?

• LowlyDBA - John M: Please do not cross-post the same question across different stack exchange sites.
• adbdkb: That is why I asked in the message on the top. Like I mentioned, I do not know, if the professionals here generally respond to queries on other SE sites. Would you mind telling me which one is better to keep to get a response from DB professionals? I would like to delete one of the questions in that case.
• LowlyDBA - John M: Many people do look at both sites, but we try to keep from duplicating content and keeping it to where it is most appropriate. Keep the post here, delete the other
• adbdkb: So, there is no possible way to do what I need to do? Or am I missing something?

Answer 1

In don't think you can use the resullt of a function. The documentation for the loader can be found in the Utilities Manual. Applying SQL Operators to Fields says

The execution of SQL strings is not considered to be part of field setting. Rather, when the SQL string is executed it uses the result of any field setting (...)

Field setting means that the field names are assigned to the substrings of the current data of the record of the file that is loaded. How this works is described in Specifying the Position of a Data Field: If you have a POSITION(x:y) specification for a column named colthen the substring from position x to position y is the field value of col and it can be references as :col in an sql-string. If there is no POSITION specifiaction found as in the specification of your fields col2, col4, col5, col7, col12, col13, LAST_UPDATE_T and LAST_UPDATE_USER_I, then POSITION is defined some way depending on the previous field and the data type of the column. So because the col2-Field (that is referenced by :col2 in an sql-string) has no explicit field specification it is the field of INTEGER EXTERNAL type (of default lenght 1) following col1. So it actually is POSITION(9:9) and therefore the first character of:col6

If you want to set the column values of a table depending on other column values you can use a trigger on the table.

Answer 2

Untested, but I believe you have to do something like this:

 col13 "schema_own.get_col13_value(:col1, schema_own.get_col2_val(:col1, :col6))",

• adbdkb: Yes, that was my first inclination. But, the problem is that get_col2_val function increments a sequence, so I cannot call it twice. Is there any other solution / idea?
• Gary_W: I suspect your needs are more demanding than a typical control file will allow for, and that you will have to write a PL/SQL program to load your data. In there you can get the sequence value once and use it in multiple places.
• adbdkb: Conceding that the needs are more demanding - I am still baffled about the fact that I mentioned in the post - 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. Why is that happenig? Maybe, if that mystery is solved, we may be able to find out if I can pass the value from first function call.