OPEN DATASET STATEMENT- for different languages.

[Former Member]

Hi All,

We have extract file generated from SAP which have description as one of the column. This description can be in any language. For this I have used different dataset statements. But nothing is working for all languages.

OPEN DATASET wm_datasetname FOR OUTPUT IN TEXT MODE ENCODING utf-8.

sample extract file ( First four records are in different languages. If I use above statement then it file generates but its alignment get disturbed. Could you please suggest what could correct OPEN DATASET statement. )

0001000001 Offre Spécial GSA 2010010120101231 365GB04

0001000002 Dette er test for danske sprog 2010040520100731 118GB01

0001000004 Detta är test för danska språk 2010010120100630 181EU01

0001000041 Jest to test na jÄzyk duÅ

ski 2010051520100930 139EU01

0001000121 TESTING IS MO 2009102020101020 366BE01

0001000122 TESTING IS 2009102020101020 366EU01

0001000229 B&Q PROMO Q2 10 2010032020100630 103GB01

0001000248 633943177501461162TEST 2009111920101120 367GB01

0001000249 633943179519594618TEST 2009111920101120 367GB01

0001000250 updated description 2009111920101120 367GB01

[Former Member]

Hi Yogesh,

Do you want upload the file into application server as is?or u want to convert the language.

Can you give some more info on this issue?

Thanks.

Ramya.

[Former Member]

The extract file is generated from SAP system and sending it to Oracle system to upload. The file need to uploaded in oracle system as it is. But due different language, alignment get disturbed and file format is not same for all reocrds.

Want to know which is correct OPEN DATASET statement for all languages and how to maintain alignment also for all records?

[Former Member]

try using this code to work around the special chars:

DATA: gv_in TYPE string,
      gv_out TYPE string.
gv_in = 'Rössing Uranium Limited" & "Prüflos für Prüfung freigeben'.
 
CALL FUNCTION 'SCP_REPLACE_STRANGE_CHARS'
  EXPORTING
    intext  = gv_in
  IMPORTING
    outtext = gv_out.
WRITE gv_out.

Output:

Input: Rössing Uranium Limited" & "Prüflos für Prüfung freigeben'

Output: Roessing Uranium Limited" & "Prueflos fuer Pruefung freigeben

Cheers

[Former Member]

Hi Yogesh,

First four records are in different languages. If I use above statement then it file generates but its alignment get disturbed.

Looking at your sample output I'm not completely sure what your output file is supposed to look like. The odd characters appearing in your pasted sample output indicate to me that you possibly opened the file in a non-Unicode aware application, which simply used a different character set (e.g. your system code page) for interpreting the data.

You mention the <em>alignment gets disturbed</em>, are you creating a fixed length file or a separated file (e.g. like tab delimited)?

The extract file is generated from SAP system and sending it to Oracle system to upload. The file need to uploaded in oracle system as it is. But due different language, alignment get disturbed and file format is not same for all reocrds.

Ok, so maybe you should post some further details. Are you loading the extract into Oracle using SQL*Loader? If so, do you specify the CHARSET? How do you determine that the data is actually incorrect? I.e. maybe the question seems stupid, but depending on your client's character set the displayed data might look incorrect even though it's stored correctly on the database (check out [Oracle's NLS_LANG FAQ|http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang faq.htm] especially also the section on Unicode clients).

Cheers, harald

[Former Member]

Hi It is fixed length format. Here i can not paste correctly hence included .(dots) below. Actually that dots are spacesin actual file generated from sap. If you see last three records which is in english language , there alignment is correct but this is not for first three records. hope this helps.

0001000001.... Offre Spécial GSA..... .........2010010120101231 365GB04

0001000002.... Dette er test for danske sprog...... 2010040520100731 118GB01

0001000004.... Detta är test för danska språk ....2010010120100630 181EU01

0001000041.... Jest to test na jÄzyk duÅ

ski 2010051520100930 139EU01

0001000121.... TESTING IS MO........................ 2009102020101020 366BE01

0001000122 ....TESTING IS ..............................2009102020101020 366EU01

0001000229 ....B&Q PROMO Q2 10..................2010032020100630 103GB01

Given below SQL loader file to load into Oracle

OPTIONS (ERRORS=9999,ROWS=100)

LOAD DATA

CHARACTERSET UTF8

TRUNCATE

INTO TABLE TEMP_PROMOHDR

TRAILING NULLCOLS

(

PROMONAME position(1:18) char "UPPER(:PROMONAME)",

DESCRIPTION position(19:58) char,

STARTDATE position(59:66) DATE "YYYYMMDD",

ENDDATE position(67:74) DATE "YYYYMMDD",

DURATION position(75:79) CHAR,

BD_SALESORG position(80:83) CHAR

)

Also want to know what will be correct OPEN DATASET statement.

[Former Member]

I still don't understand the issue. With UTF-8 encoding you have to accept that some characters need more than one byte. This is a feature of the encoding, not a bug. If you don't like that you could possibly switch to UTF-16 encoding, but even there some characters are using more than two bytes. The only Unicode encoding with a fixed byte length is UTF-32 (which has 4 bytes per character, so quite some space requirement).

So it's ok, that your output file has varying byte length per record. Your records are still correct fixed records with respect to character length.

When you load the data into Oracle you have to ensure to utilize [character length semantics|http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch05.htm#1008428] and then everything should be fine (and you have specified the CHAR addition).

[Former Member]

Hi Harald,

what do you mean by character length sementics?

[Former Member]

what do you mean by character length sementics?

Well, the SQL loader treats by default any lengths as byte length, not character length unless you're using CHARACTERSET UTF16. However, the small print that I missed initially is that POSITION specifications are always treated as byte positions. With a variable length encoding such as UTF8 (where a character is not equivalent to a fixed byte count, but can instead have a length between 1 and 3 bytes) therefore the POSITION parameter in your control file is useless. For example if your first character in the file is using up 3 bytes and your first field is supposed to be position(1:2) then you'd have already exceeded the first field (and what you really meant to say was that you have 2 characters in the first field, but Oracle doesn't allow you to do that using POSITION).

Can you try what happens with the following control file:

OPTIONS (ERRORS=9999,ROWS=100)
LOAD DATA
CHARACTERSET UTF8 LENGTH SEMANTICS CHAR
TRUNCATE
INTO TABLE TEMP_PROMOHDR
TRAILING NULLCOLS
(
PROMONAME CHAR(18) "UPPER(:PROMONAME)",
DESCRIPTION CHAR(40),
STARTDATE DATE "YYYYMMDD",
ENDDATE DATE "YYYYMMDD",
DURATION CHAR(5),
BD_SALESORG CHAR(4)

Don't have an Oracle instance to test this, but I think it might work. For the DATE fields I don't think a length specification is required, because this should be derived from the mask (otherwise try <em>DATE(8)</em> instead).

Cheers, harald