Converting EBCDIC (with packed decimals) to ASCII for loading to Oracle
669510Nov 10 2008 — edited Nov 10 2008Hi,
I am working on a task to convert EBCDIC data from mainframe system - which also contains packed decimal fileds to ASCII to load it to Oracle but am left clueless after trying a few solutions that did not work well.
The file will have large number of records and I would know the placement of packed decimal fields in that file. This has to be loaded via an Developer 2000 forms interface which internally calls SQL Loader to read and load the records in the file.
I tried changing the characterset to WE8EBCDIC.. for SQL loader but got the error CharacterSet not found. I also tried using a small procedure to convert EBCDIC to ASCII using UTL_RAW.CAST_TO_VARCHAR2 (US7ASCII, AL32UTF8) but didnt succeed as well.
As a last resort I also created a look up table and did a read of Hex values from EBCDIC file converted to corresponding ASCII character but this failed for larger files and the packed fields.
Can someone please guide me on whats the best way to do this. Please let me know if you have any questions around the requirement.
Regards,
RR