Home » RDBMS Server » Server Utilities » SQL Loader (Oracle 10G)
SQL Loader [message #463493] Thu, 01 July 2010 10:05 Go to next message
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 #463494 is a reply to message #463493] Thu, 01 July 2010 10:08 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd advise using an external table instead, it'll make the concatenation very easy.
Re: SQL Loader [message #463495 is a reply to message #463493] Thu, 01 July 2010 10:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Just concatenate them?

http://www.orafaq.com/forum/m/70798/0/?srch=concat#msg_70798

[Updated on: Thu, 01 July 2010 10:14]

Report message to a moderator

Re: SQL Loader [message #463496 is a reply to message #463495] Thu, 01 July 2010 10:19 Go to previous messageGo to next message
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 #463498 is a reply to message #463496] Thu, 01 July 2010 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So just add a call to LPAD function.

Regards
Michel
Re: SQL Loader [message #463502 is a reply to message #463498] Thu, 01 July 2010 11:18 Go to previous messageGo to next message
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 #463505 is a reply to message #463502] Thu, 01 July 2010 11:25 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Could be useful if you post the table DDL and your whole controlfile with some sample data
>>ORA-01401: inserted value too large for column
Tells it all.
Re: SQL Loader [message #463509 is a reply to message #463502] Thu, 01 July 2010 11:40 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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 #463510 is a reply to message #463505] Thu, 01 July 2010 11:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>ORA-01401: inserted value too large for column

If I got this error, I'd increase size/width of columns; just to see exactly what ends up in them.
After I fixed the load procedure, I'd size hem back at VARCHAR2(6) again.
Re: SQL Loader [message #463610 is a reply to message #463510] Fri, 02 July 2010 03:38 Go to previous message
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)"
Previous Topic: importing limited data in oracle
Next Topic: data pump impdp is SLOOOOOOW
Goto Forum:
  


Current Time: Thu Mar 28 10:16:22 CDT 2024