| Sqlldr query [message #282764] |
Fri, 23 November 2007 06:37  |
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 #282775 is a reply to message #282767] |
Fri, 23 November 2007 07:02  |
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 #282791 is a reply to message #282782] |
Fri, 23 November 2007 08:03 |
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 #282798 is a reply to message #282794] |
Fri, 23 November 2007 08:33 |
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 #283478 is a reply to message #282764] |
Tue, 27 November 2007 05:16 |
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 |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
