Write text data in pseudo-packed format

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).

    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 Received on Mon Aug 30 2010 - 14:52:54 CDT