This forum is currently locked. You can't register or post questions at this time. (read more)

SQL Loader multibyte characters

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

EL04
Member
Posts: 1
Joined: Mon Jan 24, 2011 4:59 am

SQL Loader multibyte characters

Postby EL04 » Mon Jan 24, 2011 5:38 am

Hi
I need to load multibyte characters from a position based datafile into the database using SQL Loader. The database & the control file are set to UTF-8 character set.
The characters are loaded fine using insert statements. But while loading from SQL Loader, the characters are loaded properly, but the position of the characters in the subsequent columns are pushed depending on the number of multibyte characters.

Control File:
LOAD DATA
CHARACTERSET UTF8
INFILE * INTO TABLE TEST
TRAILING NULLCOLS
(
ELEMENT1 POSITION(1:24),
ELEMENT2 POSITION(25:64),
ELEMENT3 POSITION(65:68),
ELEMENT4 POSITION(69:72),
ELEMENT5 POSITION(73:80),
ELEMENT6 POSITION(81:120)

)

DataFile:
SAMPLE1 Saméplee à Séampleee L7 ABC 60 00004454ABCD

The data that got loaded in the columns:
ELEMENT1: SAMPLE1
ELEMENT2: Saméplee à Séampleee L7
ELEMENT3: A
ELEMENT4: BC 6
ELEMENT5: 0 0004
ELEMENT6: 454ABCD

The data in the columns ELEMENT3 through ELEMENT6 are incorrect.
There are 3 multibyte characeters in the ELEMENT2 column. So the position of the data in the remaining columns have been misplaced.
The data in the column ELEMENT3 has been moved to the ELEMENT4 and so on. Any help would be appreciated!

Thanks...

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: SQL Loader multibyte characters

Postby Tim... » Mon Jan 24, 2011 11:50 am

Hi.

Sounds like the positions are in bytes and you need to adjust the controlfile to take this into account, unless of course I am missing the point of your question.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

kaderno
Member
Posts: 1
Joined: Wed Oct 03, 2012 10:55 am

Re: SQL Loader multibyte characters

Postby kaderno » Wed Oct 03, 2012 10:59 am

Same problem here, I think the position is specified in bytes so, unless you know exactly the number of special characters you can not use position based control files.

I found this around:

The control file specifies the specific starting and ending byte location of each field. This format is harder to create and less flexible but can yield performance benefits.

Any idea on how to deal with this?

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: SQL Loader multibyte characters

Postby Tim... » Wed Oct 03, 2012 4:29 pm

Hi.

I've not had to do this. I think I would log an SR with Oracle support to see how they expect it to be done.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 5 guests