Evaluating PL/SQL Functions in sql loader control file
Posted on 2005-04-17
I have a csv data file and it has four columns. its of following format:
product desc,product type description,product type,product code.
I have a table which has four columns:
product id - integer NOT NULL
product type id - integer NOT NULL
product type - varchar2 NOT NULL
number - field for sorting NULL
I have to write a sql ldr script which has to:
1.Insert records in first three columns
2.Takes free format of the csv file in consideration
3. product desc in the csv file should be evaluated against pl/sql function func_product_id(return a number)
4. product type desc in the csv file should be evaluated against pl/sql function func_product_type(returns a number)
5. product type should be inserted as it is.
6. Ignore the fourth column in csv file
I have come up with following ctl file but its not working
APPEND INTO TABLE XXX
FIELDS TERMINATED BY ','
( PRODUCT_ID EXPRESSION "func_product_id(:PRODUCT_ID)"
PRODUCT_TYPE_ID EXPRESSION "func_product_type(:PRODUCT_TYPE_ID)")
If I dont use EXPRESSION then it says that the insert failed.
if I use EXPRESSION then it says that it cant bind the variables.
Can you guys please suggest whats wrong here?
How can i evaluate fields using pl/sql functions so that the evaluated value is stored in the table?