Re: Write text data in pseudo-packed format
From: Ed Prochak <edprochak_at_gmail.com>
Date: Fri, 3 Sep 2010 03:16:18 -0700 (PDT)
Message-ID: <ef946d29-1e30-4592-9c66-ef3d907f42d0_at_i13g2000yqd.googlegroups.com>
On Aug 30, 3:52 pm, Luke <luke_ai..._at_hotmail.com> wrote:
> Hello,
>
> Oracle 11.2.0.1.0 running on SunOS 5.10.
>
> I have a problem extracting data and writing it to a flat file with PL/
> SQL and can use
> some help.
>
> I am extracting and writing records in ebcidic character set to an
> output file
> using UTL_FILE, 'WB' (write binary) mode and UTL_PG (to write packed
> decimal
> output).
>
> HEXTORAW, UTL_RAW.CAST_TO_RAW and UTL_PG.NUMBER_TO_RAW_FORMAT with
> UTL_FILE.PUT_RAW
> have provided the formatting options that I have needed except for the
> followng case:
>
> The database column is CHAR(6) and contains lower case data, e.g.,
> 'aaaaaa'.
>
> This CHAR(6) column needs to be written to the flat file as 3 bytes,
> sort of a
> pseudo-packed format, similar to packed decimal but non-numeric text.
>
> HEXTORAW ( 'aaaaaa' ) writes 3 non-displayable bytes, with hex value
> x'AAAAAA'
> (upper case).
Date: Fri, 3 Sep 2010 03:16:18 -0700 (PDT)
Message-ID: <ef946d29-1e30-4592-9c66-ef3d907f42d0_at_i13g2000yqd.googlegroups.com>
On Aug 30, 3:52 pm, Luke <luke_ai..._at_hotmail.com> wrote:
> Hello,
>
> Oracle 11.2.0.1.0 running on SunOS 5.10.
>
> I have a problem extracting data and writing it to a flat file with PL/
> SQL and can use
> some help.
>
> I am extracting and writing records in ebcidic character set to an
> output file
> using UTL_FILE, 'WB' (write binary) mode and UTL_PG (to write packed
> decimal
> output).
>
> HEXTORAW, UTL_RAW.CAST_TO_RAW and UTL_PG.NUMBER_TO_RAW_FORMAT with
> UTL_FILE.PUT_RAW
> have provided the formatting options that I have needed except for the
> followng case:
>
> The database column is CHAR(6) and contains lower case data, e.g.,
> 'aaaaaa'.
>
> This CHAR(6) column needs to be written to the flat file as 3 bytes,
> sort of a
> pseudo-packed format, similar to packed decimal but non-numeric text.
>
> HEXTORAW ( 'aaaaaa' ) writes 3 non-displayable bytes, with hex value
> x'AAAAAA'
> (upper case).
I think you do not understand what you have there. A binary value is not uppercase or lower case.
>
> Close, but I need it to write those 3 bytes as lower case.
>
> UTL_RAW.CAST_TO_RAW ( CONVERT ( 'aaaaaa' , 'WE8EBCDIC500' ,
> 'US7ASCII' ) )
> writes 6 displayble lower case bytes: 'aaaaaa'.
>
> Correct case, too many bytes.
>
> Can anyone suggest a solution to this?
>
> TIA
>
> Luke
I still don't see the problem. Binary is Binary, Maybe if you explain the whole process (IOW include what the consumer of that file expects.)
Ed Received on Fri Sep 03 2010 - 05:16:18 CDT