UCS-2 SQL-LOADER ASCII

[Stk]

Hi,

The current set up of a process is like this. Source is a zip file that contains 4 flat files which are actual data of 4 corresponding tables while there is another meta data flat file that tells the number of lines in the flat file and its structure. Using shell we ftp it from the remote server to local and using the metadata file we create ctl files and use sqlldr utility to load in to the oracle tables.

Before loading we do cross check if the number of rows mentioned in the meta data matches with the actual number of rows in the individual flat files. WC command is used to get the number of rows from the individual flat files.

The meta data file and the data file were ASCII files till now. So WC returned correct number of lines and it matched with the meta data so the process continued to the next step. But now the file type as changed. The data files are no more ASCII files but UCS-2 (Some of the countires character set are not supported by ASCII and hence the vendor has decided to supply the data in UCS-2 format). So when we do a line count it gives the double the number of the actual number of lines in the flat file. (UCS files are 2bytes per character while ASCII are 1byte per character)

$ wc -l *

3978 DE_GEOGRAPHY_GEO_200502_33360_2.TXT (Dimension Geography Flat file)

48 DE_PERIOD_PER_200502_33360_2.TXT (Dimension Period Flat file)

1442 DE_PRODUCT_PRO_200502_33360_2.TXT (Dimension Product Flat file)

See below info in metadata..

No of lines
--------------
721,
1989
24

$ more SYS_CONTROL_TABLE.TXT (Meta data flat file)

"DE","DIM","200502",721,"PSO","PRODUCT","MNTH","DE_PRODUCT_PRO_200502_33360_2.TXT","PRO",2005-03-10

"DE","DIM","200502",1989,"PSO","GEOGRAPHY","MNTH","DE_GEOGRAPHY_GEO_200502_33360_2.TXT","GEO",2005-03-10

"DE","DIM","200502",24,"PSO","PERIOD","MNTH","DE_PERIOD_PER_200502_33360_2.TXT","PER",2005-03-10

So when the shell compares these two value it fails and so the process aborts. Do you have any idea how to overcome this issue.

Also I believe UCS-2 files cannot be directly loaded thro' SQLLDR utility. I understand we need to convert this file into UTF-8 format using Utility 'RECODE'. Has anybody worked on this earlier. If yes, pls let me know the simplest way to go about.

[Stk]

any help ?