Home » RDBMS Server » Server Utilities » Sqlldr query
Sqlldr query [message #282764] Fri, 23 November 2007 06:37 Go to next message
mitrashatru
Messages: 7
Registered: November 2007
Junior Member
Hi,
I am using sqlldr to load huge bulk of data into tables. I know how to bulk load data directly from file using sqlldr command and providing a control file.
But, there are certain fields in the tables which are not present in the data files and i have to insert a praticular constant value for those fields into the table. Is there anyway by which I can do this?

Also, I want to know what is the way of specifying datatypes for the fields in sqlldr command?

Please help!

Thanks.
Re: Sqlldr query [message #282767 is a reply to message #282764] Fri, 23 November 2007 06:47 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Use the CONSTANT parameter!

Read more about SQL*Loader in the documentation.
Re: Sqlldr query [message #282775 is a reply to message #282767] Fri, 23 November 2007 07:02 Go to previous messageGo to next message
mitrashatru
Messages: 7
Registered: November 2007
Junior Member
Hi,
thanks for the quick help. Can you suggest the correct sqlloader file for the following : -

LOAD DATA
INFILE '/path/to/file.20071113'
INTO TABLE table_name
FIELDS TERMINATED BY "|" trailing nullcols
(model CONSTANT "EUE2", 
version CONSTANT "100", 
barraid, factor, 
exposure 'to_number(:exposure)', 
datadate "to_date(:datadate, 'YYYYMMDD')", 
revdate "to_date('20071113', 'YYYYMMDD')")


currently, the above control file is throwing errors. The data file contains only 4 fields separated by | as: -

barraid|factor|exposure|datadate

and the table contains 7 columns, the rest 3 cols have a fixed value. Please suggest a good control file.

Thanks.

[Updated on: Fri, 23 November 2007 07:24] by Moderator

Report message to a moderator

Re: Sqlldr query [message #282780 is a reply to message #282775] Fri, 23 November 2007 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Constant string are between ' and not " (afaik).
It would be better if you posted the errors.

Regards
Michel
Re: Sqlldr query [message #282782 is a reply to message #282775] Fri, 23 November 2007 07:31 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

the above control file is throwing errors

Is it? How is anyone but you supposed to know WHICH errors if you don't tell us?

How does the table look like (its description)? How does input data set look like (real values, not "barraid|factor|exposure|datadate" substitute)?
Re: Sqlldr query [message #282791 is a reply to message #282782] Fri, 23 November 2007 08:03 Go to previous messageGo to next message
mitrashatru
Messages: 7
Registered: November 2007
Junior Member
LOAD DATA
INFILE '/gfs/data/etd/prod/barra/1.1/2007/11/13/EUE2_100_Asset_Exposure.20071113'
INTO TABLE bulkbarraassetexposure
FIELDS TERMINATED BY "|" trailing nullcols
(model CONSTANT 'EUE2',
 version CONSTANT '100',
 barraid,
 factor,
 exposure 'to_number(:exposure)',
 datadate "to_date(:datadate, 'YYYYMMDD')",
 revdate "to_date('20071113', 'YYYYMMDD')")


I have made the changes as suggested by littlefoot. And now I dont get any error. But on seeing the log file, there are some problems.

error as in log file: -
 Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
MODEL                                                     CONSTANT
    Value is 'EUE2'
VERSION                                                   CONSTANT
    Value is '100'
BARRAID                             FIRST     *   |       CHARACTER
FACTOR                               NEXT     *   |       CHARACTER
EXPOSURE                             NEXT     *   |       CHARACTER
    SQL string for column : "to_number(:exposure)"
DATADATE                             NEXT     *   |       CHARACTER
    SQL string for column : "to_date(:datadate, 'YYYYMMDD')"
REVDATE                              NEXT     *   |       CHARACTER
    SQL string for column : "to_date('20071113', 'YYYYMMDD')"

Record 1: Rejected - Error on table BULKBARRAASSETEXPOSURE, column BARRAID.
ORA-01401: inserted value too large for column

Record 2: Rejected - Error on table BULKBARRAASSETEXPOSURE, column BARRAID.
ORA-01401: inserted value too large for column

Record 3: Rejected - Error on table BULKBARRAASSETEXPOSURE, column REVDATE.
ORA-01400: cannot insert NULL into (REVDATE)


schema of table is as follows: -

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MODEL                                     NOT NULL VARCHAR2(10)
 VERSION                                   NOT NULL VARCHAR2(10)
 BARRAID                                   NOT NULL VARCHAR2(10)
 FACTOR                                    NOT NULL VARCHAR2(20)
 EXPOSURE                                           FLOAT(126)
 DATADATE                                  NOT NULL DATE
 REVDATE                                   NOT NULL DATE


[Updated on: Fri, 23 November 2007 08:13] by Moderator

Report message to a moderator

Re: Sqlldr query [message #282794 is a reply to message #282791] Fri, 23 November 2007 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Make your lines no more than 80 characters.

What don't you understand in the following?
"inserted value too large for column"
"cannot insert NULL into (REVDATE)"

Regards
Michel
Re: Sqlldr query [message #282798 is a reply to message #282794] Fri, 23 November 2007 08:33 Go to previous messageGo to next message
mitrashatru
Messages: 7
Registered: November 2007
Junior Member
I have changed the file to look like this: -
LOAD DATA
INFILE '/gfs/data/etd/prod/barra/1.1/2007/11/13/EUE2_100_Asset_Exposure.20071113'
INTO TABLE bulkbarraassetexposure
FIELDS TERMINATED BY "|" trailing nullcols
(
model CONSTANT 'EUE2',
version CONSTANT '100',
barraid,
factor,
exposure NUMBER "to_number(:exposure)" ,
datadate "to_date(:datadate, 'YYYYMMDD')",
revdate "to_date('20071113', 'YYYYMMDD')")


error i get is: -
Quote:


SQL*Loader-350: Syntax error at line 10.
Expecting "," or ")", found "NUMBER".
exposure NUMBER "to_number(:exposure)" ,




the file contains following lines :-
Quote:


!ModelVer:100
!ReleaseDate:20071113
!Barrid|Factor|Exposure|DataDate
BELASO1|EUE2_SIZE|-0.78|20071107
BELASO1|EUE2_MOM|0.26|20071107
BELASO1|EUE2_MSEN|0.14|20071107
BELASO1|EUE2_VOL|0.67|20071107
BELASO1|EUE2_GWTH|0.02|20071107
BELASO1|EUE2_LEV|-0.03|20071107
BELASO1|EUE2_VAL|0.64|20071107
BELASO1|EUE2_FGEX|0.08|20071107
BELASO1|EUE2_YLD|-0.15|20071107
BELASO1|EUE2_COBAS|1|20071107
BELASO1|EUE2_BEL|1|20071107
BELASP1|EUE2_SIZE|-1.48|20071107



schema of table is: -
Quote:


Name Null? Type
----------------------------------------- -------- -------------
MODEL NOT NULL VARCHAR2(10)
VERSION NOT NULL VARCHAR2(10)
BARRAID NOT NULL VARCHAR2(10)
FACTOR NOT NULL VARCHAR2(20)
EXPOSURE FLOAT(126)
DATADATE NOT NULL DATE
REVDATE NOT NULL DATE



please suggest where is the problem? I am very new to oracle. What I come to know is only through your help.

Thanks.
Re: Sqlldr query [message #282800 is a reply to message #282798] Fri, 23 November 2007 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove NUMBER, your input is not a number it is a string (that represents a number).

Regards
Michel
Re: Sqlldr query [message #283478 is a reply to message #282764] Tue, 27 November 2007 05:16 Go to previous messageGo to next message
Serious Sam
Messages: 21
Registered: September 2007
Junior Member
Try this Control file,


LOAD DATA
INFILE '/gfs/data/etd/prod/barra/1.1/2007/11/13/EUE2_100_Asset_Exposure.20071113'
INTO TABLE bulkbarraassetexposure
FIELDS TERMINATED BY "|" trailing nullcols
(
barraid,
factor,
exposure,
datadate "to_date(:datadate, 'YYYYMMDD')",
revdate "to_date('20071113', 'YYYYMMDD')",
model CONSTANT "EUE2",
version CONSTANT "100"
)

Re: Sqlldr query [message #283483 is a reply to message #283478] Tue, 27 November 2007 05:30 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the difference between what you posted and what OP posted "Fri, 23 November 2007 14:02" and did not work?

Regards
Michel
Previous Topic: create a control file to load data-table
Next Topic: How to load data from one table in a database to another table in another database
Goto Forum:
  


Current Time: Thu Dec 08 08:10:58 CST 2016

Total time taken to generate the page: 0.12438 seconds