Search code examples
oraclesql-loadercontrolfile

BOUNDFILLER with select statement

In an Oracle Control File, can I populate a BOUNDFILLER from a lookup table?

for example:

EMPID BOUNDFILER "SELECT EMPID from employees where refno=refno"

I tried but I get an error msg, I assume because this is not possible?

The error message is: Expecting valid column specification, "," or ")", found ....

Any ideas of how I can populate a BOUNDFILLER from a lookup table?

EDIT: Apparently I am not very clear in what the issue is.

I need the BOUDFILLER to be populated from a lookup table. When the value is coming from the source file everything works well.

Thank you.

Here are a few more lines of code to visualize what I am trying to do:

EMPID      BOUNDFILLER "(SELECT EMPID FROM table WHERE REFNO = :REFNBR)" (Trying to get empid from another table to use below)
EMPFIRSTNAME "(SELECT FIRST_NAME FROM table WHERE TRANS = :TRANS AND FILENAME =:FILENAME)"
EMPLASTNAME  "(SELECT LAST_NAME FROM table WHERE TRANS = :TRANS AND FILENAME =:FILENAME)"
EMPEMAIL    "(SELECT EMPEMAIL FROM table WHERE EMPID = :EMPID)"
EMPSUPERVISORNAME   "(SELECT EMPSUPERVISORNAME FROM table WHERE EMPID = :EMPID)"
EMPHOMECITY      "(SELECT EMPHOMEOFFICECITY FROM table WHERE EMPID = :EMPID)"
Solution

  • Any ideas of how I can populate a BOUNDFILLER from a lookup table?

    You can't. (Despite the wording in the documentation kind of suggesting you should be able to; I think that's a doc bug and it should say something more like "Filler fields cannot be specified as part of another field specification's SQL string, because..." - and then the exception for BOUNDFILELR makes more sense).

    If EMPID is not a field in your data file then you don't need a filler for it. If it is in the file but not in the target table you can skip it with a plain FILLER, unless you want to refer to that file value later too. If it is a column in your target table then you could use an EXPRESSION clause to do the look-up instead, but then you can't refer to that as a bind variable elsewhere.

    If you want to refer to it in other SQL expressions for other columns in your control file then you'll need to repeat the lookup as a subquery in those.

    For example, you could have:

    REFNBR BOUNDFILLER,
EMPID EXPRESSION "(SELECT EMPID FROM lookuptable WHERE REFNBR = :REFNBR)",
EMPFIRSTNAME EXPRESSION "(SELECT FIRST_NAME FROM anothertable WHERE empid = (SELECT EMPID FROM lookuptable WHERE REFNO = :REFNBR))",
...

    or slightly with a join:

    REFNBR BOUNDFILLER,
EMPID EXPRESSION "(SELECT EMPID FROM lookuptable WHERE REFNBR = :REFNBR)",
EMPFIRSTNAME EXPRESSION "(SELECT t1.FIRST_NAME FROM lookuptable t1 JOIN anothertable t2 ON t2.empid = t1.empid WHERE t1.REFNBR = :REFNBR)",
...

    I've declared them as EXPRESSION on the assumption that they don't have corresponding fields in the data file - essentially for these purposes that the data file only has REFNBR. (You may have other fields you haven't shown; you may even have fields correspondng to EMPID etc that you ignore - but in that case I'd treat those as FILLER and have independent EXPRESSION entries anyway to make it clear they aren't related.)

    What you can't do is either supply a SQL expression as part of a BOUNDFILLER, or refer to one EXPRESSION in another field's SQL expression, i.e.:

    REFNBR BOUNDFILLER,
EMPID EXPRESSION "(SELECT EMPID FROM lookuptable WHERE REFNBR = :REFNBR)",
EMPFIRSTNAME EXPRESSION "(SELECT FIRST_NAME FROM anothertable WHERE empid= :EMPID)",
...

    as that will throw

    SQL*Loader-291: Invalid bind variable EMPID in SQL string for column EMPFIRSTNAME.

    The reason is kind of the same for both. From the documentation:

    For each input record read, the value of the field referenced by the bind variable will be substituted for the bind variable.

    It looks at the value of the field from the file, not after any transformation form a SQL expression. If you were only using REFNBR for that lookup then you might consider not referring to that directly at and doing:

    EMPID "(SELECT EMPID FROM lookuptable WHERE REFNBR = :EMPID)",
EMPFIRSTNAME EXPRESSION "(SELECT FIRST_NAME FROM anothertable WHERE empid= :EMPID)",
...

    but in the EXPRESSION evaluation it's still using the original value of the value from the file - i.e. actually a REFBNR - and not the final value that will be inserted as EMPID. So won't find a match, or won't match the row you intended, which is probably worse.

    Given that, it wouldn't make sense for BOUNDFILLER to allow an SQL expression to be used - the result of that expression would never be used.

    A couple of other thoughts... Clearly repeating the subquery/join is messy, so I can see why a reusable modified value would be useful in this scenario. But since you can't do that, it would be simpler to load the raw REFNBR (and any other fields you need from the file) into a staging table - either physical or an external table as @Littlefoot suggested - and then query that and join to the other tables to do a final insert into your target table.

    And it looks - from what may be a very contrived example - like you're duplicating data, which may not be sensible; it might be better to actually just have REFNBR or at least just EMPID in your target table instead with referential constraints, so any changes made in anothertable are reflected automatically when it's queried. This may be part of a larger process that archives and deletes, or something; but then it would still be simpler to use a staging/external table and drive that whole process off that, instead of trying to make SQL*Loader do some of that work.