Home » RDBMS Server » Server Utilities » SQL Loader Referencing field problem (Oracle 10g)
| SQL Loader Referencing field problem [message #394569] |
Fri, 27 March 2009 16:09  |
tejasvn007
Messages: 3
Registered: March 2009
|
Junior Member |
|
|
I am trying to import data from a csv file into a database table called SPONSOR.
SPONSOR table has a field called SPONSOR_CODE.
There is another table SPONSOR_TYPE in database which has a field called DESCRIPTION and also SPONSOR_CODE which refers to the SPONSOR_CODE from SPONSOR table.
In my csv file i have the DESCRIPTION field which is VARCHAR2.
I have a function 'get_sponsor_type_code' which returns the SPONSOR_CODE for a DESCRIPTION field.
In my control file i use:
sponsor_code "get_sponsor_type_code(\':sponsor_code \')"
This does not work. Since the SPONSOR_CODE is a non nullable field, i get error that
Record 1: Rejected - Error on table SPONSOR, column SPONSOR_CODE.
ORA-01400: cannot insert NULL into ("SPONSOR"."SPONSOR_CODE")
How do i fix it?
|
|
|
|
|
|
|
|
| Re: SQL Loader Referencing field problem [message #394577 is a reply to message #394569] |
Fri, 27 March 2009 19:19  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You need to declare the description as a boundfiller, then use the description as a parameter to your function, without any slashes or single quotes, as demonstrated below. Any such calculated fields must also be last in the field list.
-- test.dat:
1,descra
2,descrb
3,descrc
-- test.ctl:
LOAD DATA
INFILE test.dat
INTO TABLE sponsor
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(id,
description BOUNDFILLER,
sponsor_code "get_sponsor_type_code (:description)")
-- tables, lookup data, and function:
SCOTT@orcl_11g> CREATE TABLE sponsor
2 (id NUMBER,
3 sponsor_code NUMBER)
4 /
Table created.
SCOTT@orcl_11g> CREATE TABLE sponsor_type
2 (description VARCHAR2 (15),
3 sponsor_code NUMBER)
4 /
Table created.
SCOTT@orcl_11g> INSERT ALL
2 INTO sponsor_type VALUES ('descra', 10)
3 INTO sponsor_type VALUES ('descrb', 20)
4 INTO sponsor_type VALUES ('descrc', 30)
5 SELECT * FROM DUAL
6 /
3 rows created.
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION get_sponsor_type_code
2 (p_description IN sponsor_type.description%TYPE)
3 RETURN sponsor.sponsor_code%TYPE
4 AS
5 v_sponsor_code sponsor.sponsor_code%TYPE;
6 BEGIN
7 SELECT sponsor_code
8 INTO v_sponsor_code
9 FROM sponsor_type
10 WHERE description = p_description;
11 RETURN v_sponsor_code;
12 END get_sponsor_type_code;
13 /
Function created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
-- load and results:
SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SCOTT@orcl_11g> SELECT * FROM sponsor
2 /
ID SPONSOR_CODE
---------- ------------
1 10
2 20
3 30
SCOTT@orcl_11g>
|
|
|
|
| Re: SQL Loader Referencing field problem [message #394875 is a reply to message #394577] |
Mon, 30 March 2009 12:13 |
tejasvn007
Messages: 3
Registered: March 2009
|
Junior Member |
|
|
Still not working. 
I used a function similar to the one given above.
This is my control file.
LOAD DATA
INFILE 'C:/Sponsor_List.csv'
BADFILE 'C:/bad.txt'
DISCARDFILE 'C:/discard.txt'
REPLACE INTO TABLE sponsor
TRAILING NULLCOLS
(
sponsor_code SEQUENCE(MAX,1),
sponsor_name CHAR TERMINATED BY ",",
acronym CHAR TERMINATED BY ",",
owned_by_unit CONSTANT 00001,
update_timestamp SYSDATE,
description BOUNDFILLER,
sponsor_type_code "get_sponsor_type_code(:description)"
)
|
|
|
|
| Re: SQL Loader Referencing field problem [message #394897 is a reply to message #394875] |
Mon, 30 March 2009 13:55 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You need to specify a field terminator for your description boundfiller. If it is a comma, then specify that as you have done with the other fields. If it is whitespace, you can use that. Or, if it is the new line, then you can use X'0A' or whatever it is on your operating system. You are more likely to get quicker more accurate responses if you include things like your operating system, create table statements or at least a describe of your tables, the actual function you are using, a few lines of sample data from your text file, and your SQL*Loader log file. Without being able to see these things, it is difficult to guess what is wrong. In my original example, I specified one delimiter for all fields. In the example below, I have specified them individually for each field as you have done. Either way is acceptable. I have also included the additional columns and different names that you have used.
-- c:\oracle11g\sponsor_list.csv:
name1,acr1,descra
name2,acr2,descrb
name3,acr3,descrc
-- c:\oracle11g\test.ctl:
LOAD DATA
INFILE 'C:\oracle11g\sponsor_list.csv'
BADFILE 'C:\oracle11g\bad.txt'
DISCARDFILE 'C:\discard.txt'
REPLACE INTO TABLE sponsor
TRAILING NULLCOLS
(sponsor_code SEQUENCE (MAX, 1),
sponsor_name CHAR TERMINATED BY ",",
acronym CHAR TERMINATED BY ",",
owned_by_unit CONSTANT 00001,
update_timestamp SYSDATE,
description BOUNDFILLER TERMINATED BY X'0A',
sponsor_type_code "get_sponsor_type_code (:description)")
SCOTT@orcl_11g> CREATE TABLE sponsor
2 (sponsor_code NUMBER,
3 sponsor_name VARCHAR2 (12),
4 acronym VARCHAR2 ( 7),
5 owned_by_unit VARCHAR2 (13),
6 update_timestamp DATE,
7 sponsor_type_code NUMBER)
8 /
Table created.
SCOTT@orcl_11g> CREATE TABLE sponsor_type
2 (description VARCHAR2 (15),
3 sponsor_type_code NUMBER)
4 /
Table created.
SCOTT@orcl_11g> INSERT ALL
2 INTO sponsor_type VALUES ('descra', 10)
3 INTO sponsor_type VALUES ('descrb', 20)
4 INTO sponsor_type VALUES ('descrc', 30)
5 SELECT * FROM DUAL
6 /
3 rows created.
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION get_sponsor_type_code
2 (p_description IN sponsor_type.description%TYPE)
3 RETURN sponsor.sponsor_type_code%TYPE
4 AS
5 v_sponsor_type_code sponsor.sponsor_type_code%TYPE;
6 BEGIN
7 SELECT sponsor_type_code
8 INTO v_sponsor_type_code
9 FROM sponsor_type
10 WHERE description = p_description;
11 RETURN v_sponsor_type_code;
12 END get_sponsor_type_code;
13 /
Function created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SCOTT@orcl_11g> SELECT * FROM sponsor
2 /
SPONSOR_CODE SPONSOR_NAME ACRONYM OWNED_BY_UNIT UPDATE_TI SPONSOR_TYPE_CODE
------------ ------------ ------- ------------- --------- -----------------
1 name1 acr1 00001 30-MAR-09 10
2 name2 acr2 00001 30-MAR-09 20
3 name3 acr3 00001 30-MAR-09 30
SCOTT@orcl_11g>
[edit: added csv and ctl files that I forgot to post]
[Updated on: Mon, 30 March 2009 17:41] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Sun Apr 28 22:11:58 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
