| SQL Loader [message #463493] |
Thu, 01 July 2010 10:05  |
ssantoshss
Messages: 4
Registered: July 2010
Location: IN
|
Junior Member |
|
|
I am trying to use SQL Loader for a data load...
Need help to concatenate fields before i load
</t> == tab delimited
2009</t>7</t>
2009</t>10</t>
2009</t>6</t>
i need to concatenate and make the new field with Date and Month together
Final output needs to be in table column
200907
200910
200906
|
|
|
|
|
|
|
|
| Re: SQL Loader [message #463496 is a reply to message #463495] |
Thu, 01 July 2010 10:19  |
ssantoshss
Messages: 4
Registered: July 2010
Location: IN
|
Junior Member |
|
|
Just Concatenate will work fine only if MONTHS are 10,11 and 12..
This case does not work as i have months 1,2,3 which needs to come as 01,02,03
|
|
|
|
|
|
| Re: SQL Loader [message #463502 is a reply to message #463498] |
Thu, 01 July 2010 11:18 |
ssantoshss
Messages: 4
Registered: July 2010
Location: IN
|
Junior Member |
|
|
i am a new b on SQL Loader, Made the below changes
DDATE CONSTANT "TRIM(:YEAR) || LPAD(TRIM(:MONTH),2,0)",
BDATE CONSTANT "TRIM(:BYEAR) || LPAD(TRIM(:BMONTH),2,0)"
The DDATE and BDATE columns are defined as VARCHAR2(6)
The records are getting rejected with the below message
Record 1: Rejected - Error on table NEW_LOAD_NA, column DDATE.
ORA-01401: inserted value too large for column
|
|
|
|
|
|
| Re: SQL Loader [message #463509 is a reply to message #463502] |
Thu, 01 July 2010 11:40 |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Based on information you have provided so far,
it works.
oracle@kanada#./somescript
desc t3:
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
C1 VARCHAR2(4)
C2 VARCHAR2(2)
C3 VARCHAR2(6)
controlfile used:
load data
infile *
truncate into table t3
fields terminated by ',' trailing nullcols
(
c1,
c2,
c3 " :c1 || lpad(:c2,2,0) " )
begindata
2009,7
2009,10
2009,6
invoking sqlldr
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Jul 1 12:25:17 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 3
check the data:
C1 C2 C3
---- -- ------
2009 7 200907
2009 10 200910
2009 6 200906
|
|
|
|
|
|
| Re: SQL Loader [message #463610 is a reply to message #463510] |
Fri, 02 July 2010 03:38 |
ssantoshss
Messages: 4
Registered: July 2010
Location: IN
|
Junior Member |
|
|
Thanks a lot BlackSwan, it helped dbug the problem.....Declaring as column as constant in control was creating the problem :
Thanks Mahesh for the solution.
DDATE "TRIM(:YEAR) || LPAD(TRIM(:MONTH),2,0)",
BDATE "TRIM(:BYEAR) || LPAD(TRIM(:BMONTH),2,0)"
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
