| Evaluating PL/SQL Functions in sql loader control file [message #116107] |
Sun, 17 April 2005 17:00  |
narveer
Messages: 4
Registered: April 2005
Location: Dublin
|
Junior Member |
|
|
Hi,
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
LOAD DATA
INFILE data.csv
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)")
TEXT_VALUE)
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?
Thanks
Narveer
|
|
|
|
|
|
|
|
|
|
| Re: Evaluating PL/SQL Functions in sql loader control file [message #116370 is a reply to message #116177] |
Tue, 19 April 2005 13:01  |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Please see the demonstration below, which executes without any problems. If it does not solve your problem, then please provide what is different, such as sample data, control file, table structure, functions, and how you are loading. An actual copy and paste is preferable. You might try testing on a small sample of your data, then checking your log file. It may be that certain lines of your data result in an endless loop in one of your functions, or some such thing.
-- contents of data.csv:
1 prodcut desc,2 product type description,a,some product code,
5 prodcut desc,6 product type description,b,another product code,
-- contents of test.ctl:
LOAD DATA
INFILE data.csv
APPEND INTO TABLE xxx
FIELDS TERMINATED BY ','
(product_id "func_product_id (:product_id)",
product_type_id "func_product_type (:product_type_id)",
product_type)
-- table:
scott@ORA92> CREATE TABLE xxx
2 (product_id INT NOT NULL,
3 product_type_id INT NOT NULL,
4 product_type VARCHAR2(1) NOT NULL,
5 sort_field NUMBER)
6 /
Table created.
-- functions:
scott@ORA92> CREATE OR REPLACE FUNCTION func_product_id
2 (p_product_id IN VARCHAR2)
3 RETURN NUMBER
4 AS
5 BEGIN
6 RETURN SUBSTR (p_product_id, 1, 1);
7 END func_product_id;
8 /
Function created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> CREATE OR REPLACE FUNCTION func_product_type
2 (p_product_type IN VARCHAR2)
3 RETURN NUMBER
4 AS
5 BEGIN
6 RETURN SUBSTR (p_product_type, 1, 1);
7 END func_product_type;
8 /
Function created.
scott@ORA92> SHOW ERRORS
No errors.
-- load data:
scott@ORA92> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
-- results:
scott@ORA92> SELECT * FROM xxx
2 /
PRODUCT_ID PRODUCT_TYPE_ID P SORT_FIELD
---------- --------------- - ----------
1 2 a
5 6 b
scott@ORA92>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
