OraFAQ Forum: Server Utilities » SqlLoader concatenate on Windows

Home » RDBMS Server » Server Utilities » SqlLoader concatenate on Windows

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SqlLoader concatenate on Windows [message #173086]

Fri, 19 May 2006 06:15

pbindi
Messages: 7
Registered: May 2006

Junior Member

Hi,
I've a problem during loading by sqlloader with option "concatenate"

The file is (like oracle manual Mad

)
_____________________________________________
load data
infile *
concatenate 4
into table test
fields terminated by whitespace trailing nullcols (
col1,
col2,
col3,
col4)
BEGINDATA
aaa
aa
aaaa
aa
bb
bbbb
b
bb
___________________________________________

The table is
Nome Type
----------------------------------------- -------
COL1 VARCHAR2(4)
COL2 VARCHAR2(4)
COL3 VARCHAR2(4)
COL4 VARCHAR2(4)

Inside log file find this message

Record 1: Rejected - Errore nella tabella TEST.
ORA-01401: Record 1: Rifiutato - Errore nella tabella TEST.
ORA-01401: valore inserito troppo grande per la colonna
_________________________________________________________

I believe there is some problem when I created file by notepad...but I'm not sure, why I used also textpad, wordpad etc...

Thank's for patience Razz

Paolo

Report message to a moderator

Re: SqlLoader concatenate on Windows [message #173091 is a reply to message #173086]

Fri, 19 May 2006 06:26

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

error is

Quote:

ORA-01401: inserted value too large for column

All you have to do is
Increase the VARCHAR2(4) to varchar2(30) or something.

Report message to a moderator

Re: SqlLoader concatenate on Windows [message #173098 is a reply to message #173091]

Fri, 19 May 2006 06:59

pbindi
Messages: 7
Registered: May 2006

Junior Member

I don't explain very well my problem Embarassed

The file is
aaa
aa
aaaa
aa
bb
bbbb
b
bb

I need load it by sql*loader and the goal is ok, if the table will be loaded in this way

Col1 col2 col3 col4
aaa aa aaaa aa
bb bbbb b bb

Thank's

[Updated on: Fri, 19 May 2006 07:25]

Report message to a moderator

Re: SqlLoader concatenate on Windows [message #173101 is a reply to message #173098]

Fri, 19 May 2006 07:38

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Every line in datafile is terminated by 'whitespace' or blank and followed by a carriage return.

oracle@mutation#cat data.ctl
LOAD DATA
infile 'data.data'
concatenate 4
INTO TABLE datatable
fields terminated by whitespace trailing nullcols
(
col1,
col2,
col3,
col4)
oracle@mutation#cat data.data
aaa
aa
aaaa
aa
bb
bbbb
b
bb
oracle@mutation#sqlldr userid=scott/tiger control=data.ctl

SQL*Loader: Release 9.2.0.4.0 - Production on Fri May 19 08:37:44 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 2
oracle@mutation#query mutation scott.datatable

COL1 COL2 COL3 COL4
---- ---- ---- ----
aaa  aa   aaaa aa
bb   bbbb b    bb

Report message to a moderator

Re: SqlLoader concatenate on Windows [message #173102 is a reply to message #173101]

Fri, 19 May 2006 07:47

pbindi
Messages: 7
Registered: May 2006

Junior Member

Thank's,
but the problem is that if I create the file by notepad (or Textpad or wordpad), your example don't work.

On Unix system I don't have any problem...but now I'm working on Windows and I believe that this is the problem!

Can you help me?!

Thank's a lot!

Report message to a moderator

Re: SqlLoader concatenate on Windows [message #173106 is a reply to message #173102]

Fri, 19 May 2006 07:59

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Works as intended, as long as you have the file to be properly formatted (as specified in controlfile. It should be followed by blankspace and terminated by linefeed/carriage return).
I used the notepad. Attached is the actual file.

C:\>sqlldr scott/tiger@mutation control=data.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri May 19 08:57:38 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 2

C:\>sqlplus -s scott/tiger@mutation
select * from datatable;

COL1 COL2 COL3 COL4
---- ---- ---- ----
aaa  aa   aaaa aa
bb   bbbb b    bb

exit

Attachment: data.data
(Size: 0.04KB, Downloaded 1130 times)

[Updated on: Fri, 19 May 2006 07:59]

Report message to a moderator

Re: SqlLoader concatenate on Windows [message #173112 is a reply to message #173106]

Fri, 19 May 2006 08:17

pbindi
Messages: 7
Registered: May 2006

Junior Member

well,
I understant that only if every record have a blankspace like last character (before the linefeed/carriage return), then can I load this file?

If that's, I don't have this situation Embarassed

, becuase every record terminated ONLY linefeed/carriage return.

Do you know other solution for this problem?

Thank's!!

Report message to a moderator

Re: SqlLoader concatenate on Windows [message #173125 is a reply to message #173112]

Fri, 19 May 2006 08:43

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I still cannot reproduce you case.
A proper linefeed/carraige return ***will*** have whitespace as terminator. This time, i literally opened notepad (and in vi, if that matters) and Typed those entries line-by-line and Sqlldr did the job as expected.
If you cannot fix the format, convert it.
With simple scripting like this, a terminator you want and use it inside controlfile.

oracle@mutation#cat data.data | awk '{print $1","}' > data1.data
oracle@mutation#cat data1.data
aaa,
aa,
aaaa,
aa,
bb,
bbbb,
b,
bb,

Report message to a moderator

Re: SqlLoader concatenate on Windows [message #173128 is a reply to message #173112]

Fri, 19 May 2006 08:50

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

And may be your 'copy' & 'paste' mechanism is not working.Some formatting is lost.

Report message to a moderator

Re: SqlLoader concatenate on Windows [message #173129 is a reply to message #173128]

Fri, 19 May 2006 09:05

pbindi
Messages: 7
Registered: May 2006

Junior Member

maybe...
but I don't undestand it.

So, I opened your attachement file (thank's for it...) by notepad and I have blank too Razz

, while my file don't have blank at the end.

A little pleasure Embarassed

Now don't have a server Unix, where I can work "awk" statment. Do you have the same statment on Windows?

That's love microsoft!! Mad

Thank's for all!!
Ciao ciao

Paolo

ps. sorry for my bad english

[Updated on: Fri, 19 May 2006 09:12]

Report message to a moderator

Re: SqlLoader concatenate on Windows [message #173130 is a reply to message #173129]

Fri, 19 May 2006 09:12

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>Do you have the same statment on Windows?
Try CYGWIN?

Report message to a moderator

Re: SqlLoader concatenate on Windows [message #173131 is a reply to message #173130]

Fri, 19 May 2006 09:16

pbindi
Messages: 7
Registered: May 2006

Junior Member

No, I don't.
but I'm trying by google and I'll istall it

Grazie mille per l'aiuto! Laughing

Hasta pronto!

Report message to a moderator

Re: SqlLoader concatenate on Windows [message #173132 is a reply to message #173130]

Fri, 19 May 2006 09:22

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Or even gawk
http://gnuwin32.sourceforge.net/packages/gawk.htm
Methinks, all this is an overkill.
You copy/paste thingy is not doing good.
I cannot reproduce your case.
You can even try using UTL_FILE to read the file line by line and do whatever you want. Search this forum / google /docset for UTL_FILE

[Updated on: Fri, 19 May 2006 09:24]

Report message to a moderator

Previous Topic:	Please help!!! .....Anti Virus Software for Oracle Applications installed Windows Server 2003 ??
Next Topic:	Problem using SUBSTR

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 29 08:02:16 CDT 2024