SQLLDR-field data in subselect in control file?

From: Eric Walstad <ewalstad_at_yahoo.com>
Date: 6 Mar 2003 10:14:06 -0800
Message-ID: <cf2ba974.0303060819.6bf23bec_at_posting.google.com>



Hi,

[Quoted] I need to load a CSV file that contains an Manufacturer Name (VARCHAR) but I need to store an Manufacturer ID (NUMBER) in the table specified in my sqlldr control file.

Is it possible to have the control file look up the Manufacturer ID based on the Manufacturer Name found in each row of the CSV file? The two tables are related as one-to-many, one manufacturer record can have many equipment records. The equipment table has a foriegn key pointing to the manufacturer table.

I've tried doing a sub select and tried making a User Defined Function but am having no luck. I've also tried creating a view that joins the two tables and having the control file inster into that view, but it complains about how it cant insert into a view with two tables.

Thanks in advance!

Eric.

Here's what my control file looks like:

LOAD DATA
    INFILE EvpDB.csv
    BADFILE 'bad_EvpDB.csv'
-- APPEND

    REPLACE
INTO TABLE equip_temp

    FIELDS TERMINATED BY ","
    OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS

(   EQUIPMENT_ID        "EQUIPMENT_TEMP_ID_SEQ.nextval",
    TYPE_ID             CONSTANT '4',

-- MANUFACTURER_ID "my_pkg.manuf_id(:MANUFACTURER_NAME)",
-- MANUFACTURER_ID "select manufacturer_id from manufacturer
WHERE manufacturer_name=(:MANUFACTURER_NAME)",

    MODEL_TRADE_NAME ,
    EVAP_MODEL_NUMBER ,
    EVAP_INDUSTRY_STANDARD_RATING,
    DuplicateEvapIndStdRating FILLER,
    EVAP_MOTOR_HORSEPOWER FILLER,

    Speed               FILLER,
    EVAP_EFFICIENCY     FILLER,
    CREATE_BY_ID        CONSTANT '1',
    CREATE_DATE         SYSDATE,
    LAST_MOD_BY_ID      CONSTANT '1',
    LAST_MOD_DATE       SYSDATE

) Received on Thu Mar 06 2003 - 19:14:06 CET

Original text of this message