Re: PL/SQL UTL_RAW.CAST_TO_RAW problem reading HEX data field with utl_file

Op 30-4-2010 1:42, Luke schreef:
> Hello,
>
> Oracle 11.2.0.1.0 running on SunOS 5.10.
>
> I am encountering a PL/SQL problem with UTL_RAW.CAST_TO_RAW and can
> use some help.
>
> I am reading and processing an input file that is a mix of ascii and
> hex fields.
>
> Here is a snippet of the processing for the hex field in question:
>
> w_input_line CHAR(187); -- space for the input record
> raw_hex_field raw(2);
>
> 1. UTL_FILE.GET_LINE(w_file_handle, w_input_line);
> 2. raw_hex_field := UTL_RAW.CAST_TO_RAW (substr(w_input_line, 93,
> 2));
>
> This works fine for hex values x'0009', x'000B' and x'FFFF' but has a
> problem with x'000A':
>
> Here is some debugging output:
>
> dbms_output.put_line ('raw_hex_field: ' || '>' || raw_hex_field ||
> '<' );
> raw_hex_field:>0009<
> raw_hex_field:>000B<
> raw_hex_field:>FFFF<
> raw_hex_field:>00< <<<<<<<<<<<<<<<<<<<<<<<<<<<<< HUH?
> where is my 0A?
>
> This is what I would hope to see:
>
> SQL->exec dbms_output.put_line( '>' || utl_raw.cast_to_raw( chr(00)
> || chr(10) ) || '<');
> >000A<
>
> PL/SQL procedure successfully completed.
>
>
> Is it possibly interpreting x'0A' as a line feed?
>
> Changing the input record format is not an option for us. Can anyone
> help to resolve this?
>
> TIA
>
> Luke
>
>
>

Just did a quick test. It's utl_file.get_line going wrong here. Does not pick up 0A, sees it as an emtpty line.

Try this (note that I use a varchar2 for w_input_line to get the returned length right)

CREATE OR REPLACE PROCEDURE testhex IS

   w_input_line VARCHAR2(187); -- space for the input record    raw_hex_field RAW(2);
   w_file_handle utl_file.file_type;
BEGIN
   w_file_handle := utl_file.fopen('TEST2', 'test.txt', 'W');    FOR i IN 1 .. 100
   LOOP
     utl_file.put_line(w_file_handle, chr(0) || chr(i));    END LOOP;
   utl_file.fclose(_file_handle);
   w_file_handle := utl_file.fopen('TEST2', 'test.txt', 'R');    FOR i IN 1 .. 100
   LOOP

     utl_file.get_line(w_file_handle, w_input_line);
     raw_hex_field := utl_raw.cast_to_raw(substr(w_input_line, 1, 2));
     dbms_output.put_line(length(w_input_line));
     dbms_output.put_line(raw_hex_field);

You'll notice some strings coming back at length 2 (correct), and some as length 1 (000A,000D) (= cr, lf) after reading them with utl_file.get_line.

Shakespeare Received on Fri Apr 30 2010 - 11:13:18 CEST