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 Go to next message
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 #394571 is a reply to message #394569] Fri, 27 March 2009 16:59 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Did you read & follow the bouncing RED ball advice as stated in URL below?
http://www.orafaq.com/forum/f/1/136107/
Did you read & follow Posting Guidelines as contained in URL below?
http://www.orafaq.com/forum/t/88153/0/

>How do i fix it?
Not easily.

One possibility is to load a constant & then after load UPDATE.
Another way would be to write a script to put the desired into the file prior to loading it.
Another way would to make table EXTERNAL TABLE & write some PL/SQL to load the data.

Good Luck.
Re: SQL Loader Referencing field problem [message #394576 is a reply to message #394569] Fri, 27 March 2009 19:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please post your table ddl,your control file, sample data and OS.
Please use CODE tags.
Re: SQL Loader Referencing field problem [message #394577 is a reply to message #394569] Fri, 27 March 2009 19:19 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
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 Go to previous messageGo to next message
tejasvn007
Messages: 3
Registered: March 2009
Junior Member
Still not working. Sad

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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
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

Re: SQL Loader Referencing field problem [message #394905 is a reply to message #394897] Mon, 30 March 2009 14:59 Go to previous message
tejasvn007
Messages: 3
Registered: March 2009
Junior Member
It is working now Smile
I put the delimiter now for the BOUNDFILLER field.
Thanks for your help !!!
Previous Topic: SQL LOADER for multiple table
Next Topic: Expdp failed - when using mapped drive.
Goto Forum:
  


Current Time: Sun Dec 04 02:44:40 CST 2016

Total time taken to generate the page: 0.24975 seconds