Re: load from EBCDIC file to Oracle 9i tables using UTL_FILE

I'm reading data from EBCDIC file in Oracle PL/SQL using UTL_FILE. I wasn't able to read BINARY data type from EBCDIC to Oracle. Initially i thought the problem was due to the following reasons discussed in the article.

http://support.sas.com/techsup/technote/ts642.html <quote>
Solutions
The only way to overcome the problem of non-standard numeric data being corrupted by the FTP is to move the data without translating it. This will necessitate making some significant changes in your program. It may also require preprocessing the data file on the mainframe. The sections below list the different types of files and situations, a recommended approach to read in the file, and a sample program to accomplish the task.
</quote>

But we have confirmed that the contents of EBCDIC file is fine by looking into the EBCDIC file using a tool. The contents are absolutely ok.

Now how do i read the Binary data from EBCDIC file.

My code is like this...

Open the file using UTL_FILE.FOPEN

UTL_FILE.GET_LINE(file_handler,string,lengthofthestring)

DBMS_OUTPUT.PUT_LINE(SUBSTR(CONVERT(string,ASCIIUS7,EBCDIC),1,4));

--This is generating an output as "&". The actual data is 005. Since
this is
--declared as binary, I'm unable to read and print it. same is the
case with
--other binary data types.

UTL_FILE.FCLOSE. How do I resolve this? I would appreciate your help on this. This is something critical and immediate requirement for us.

Thanks
Karuna Received on Thu Feb 24 2005 - 06:48:39 CST