0

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?

Improve this question
4
  • Please do not cross-post the same question across different stack exchange sites.
    – LowlyDBA - John M
    Commented Jan 25, 2015 at 15:47
  • 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.
    – adbdkb
    Commented Jan 25, 2015 at 16:03
  • 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
    – LowlyDBA - John M
    Commented Jan 25, 2015 at 16:16
  • So, there is no possible way to do what I need to do? Or am I missing something?
    – adbdkb
    Commented Mar 28, 2015 at 13:40

2 Answers 2

Reset to default
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.

Improve this answer
0

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))",
Improve this answer
3
  • 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?
    – adbdkb
    Commented Jan 29, 2015 at 1:25
  • 1
    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.
    – Gary_W
    Commented Jan 29, 2015 at 21:15
  • 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.
    – adbdkb
    Commented Jan 29, 2015 at 23:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.