This discussion is archived
9 Replies Latest reply: Jun 14, 2013 8:49 AM by LuckyJack2001 RSS

How to export packed decimal (comp-3) data

LuckyJack2001 Newbie
Currently Being Moderated

Hello,

I'm using SQL Developer V3.2.20.09, against a 10g database.  I need to convert numbers to packed decimal format, and export them for later FTP'ing to a mainframe.

I have had partial success.

I created a function which successfully converts a number to comp-3 data in raw format.  So when I pass the number 1234567890 it returns '01234567890C'.  Now, I take that raw data and run it through UTL_RAW.CAST_TO_VARCHAR2 function, which returns what looks like 'junk'.

 

Here's my query.  The inner function is what converts to packed decimal, returning 'raw':  select UTL_RAW.CAST_TO_VARCHAR2(CONVERT_COMP3(1234567890)) AS VARCHR2_DT from dual;

 

I then export the data to a text file, and look at it in a hex viewer.

Strangely enough, it looks pretty good, but not quite!

In the hex viewer, the data looks like this:  '01234567090C'.   Note that the '8' did not get converted correctly.  And this is the problem - it seems to work for every number except the 8's!  I tried different combinations of number with always the same result - the '8' becomes a '0'.

 

Can anyone shed some light on this?  Do I perhaps have to export the data in a certain way?  I'm thinking that maybe I don't need the CAST_TO_VARCHAR2 - because the query results are returning RAW data anyway. But when I export it that way, it just gets exported as straight text (not binary comp-3).

 

Thanks,

 

Paul D.

  • 1. Re: How to export packed decimal (comp-3) data
    LuckyJack2001 Newbie
    Currently Being Moderated

    Sorry, I meant to include the function so you can try it if you want:

     

    CREATE OR REPLACE FUNCTION CONVERT_COMP3(p_str IN Varchar2)

        RETURN raw

      IS

        l_str VARCHAR2(1024);

        l_num NUMBER DEFAULT to_number( p_str );

        l_sign CHAR(1) := 'C';

      BEGIN

        l_str := ltrim( rtrim( REPLACE( TRANSLATE( p_str, '+-', '-' ), '-', '' ) ) );

        IF ( l_num < 0 ) THEN

          l_str := l_str || 'D';

        ELSE

          l_str := l_str || l_sign;

        END IF;

        IF ( mod( LENGTH(l_str), 2 ) = 1 ) THEN

          l_str := '0' || l_str;

        END IF;

        RETURN hextoraw( l_str );

      END;

  • 2. Re: How to export packed decimal (comp-3) data
    rp0428 Guru
    Currently Being Moderated
    I'm using SQL Developer V3.2.20.09, against a 10g database.  I need to convert numbers to packed decimal format, and export them for later FTP'ing to a mainframe.

    I have had partial success.

    I created a function which successfully converts a number to comp-3 data in raw format.  So when I pass the number 1234567890 it returns '01234567890C'.  Now, I take that raw data and run it through UTL_RAW.CAST_TO_VARCHAR2 function, which returns what looks like 'junk'.

     

    Here's my query.  The inner function is what converts to packed decimal, returning 'raw':  select UTL_RAW.CAST_TO_VARCHAR2(CONVERT_COMP3(1234567890)) AS VARCHR2_DT from dual;

     

    I then export the data to a text file, and look at it in a hex viewer.

    Strangely enough, it looks pretty good, but not quite!

    In the hex viewer, the data looks like this:  '01234567090C'.   Note that the '8' did not get converted correctly.  And this is the problem - it seems to work for every number except the 8's!  I tried different combinations of number with always the same result - the '8' becomes a '0'.

     

    Can anyone shed some light on this?  Do I perhaps have to export the data in a certain way?  I'm thinking that maybe I don't need the CAST_TO_VARCHAR2 - because the query results are returning RAW data anyway. But when I export it that way, it just gets exported as straight text (not binary comp-3).

    Your data is NOT character data so you can't cast it to VARCHAR2. Your function is just creating a text string where each character is a HEX digit. 'COMP-3' uses a BINARY format.

     

    Once you actually have the correct RAW data export it as RAW. I have no idea what you mean by 'it just gets exported as straight text'.

     

    You haven't told us what you expect the export file to contain or what format the file is supposed to be in.

  • 3. Re: How to export packed decimal (comp-3) data
    LuckyJack2001 Newbie
    Currently Being Moderated

    Hi, rp0428,

    Your answer is helpful.  I was on the right track when I said "I'm thinking that maybe I don't need the CAST_TO_VARCHAR2 - because the query results are returning RAW data anyway".  And when I said, "it gets exported as straight text", what I mean is that when I export the raw query results (no cast_to_varchar), I choose the 'text' format option, and it just exports the string '0123456789C' as a string, rather than as the binary packed data.

    So now my question is, how do I export the data as RAW?  There is no choice for that.  There's text, csv, pdf, xml, etc. but nothing indicating raw data export.

    -Paul

  • 4. Re: How to export packed decimal (comp-3) data
    rp0428 Guru
    Currently Being Moderated

    You haven't told us what you expect the export file to contain or what format the file is supposed to be in.

  • 5. Re: How to export packed decimal (comp-3) data
    LuckyJack2001 Newbie
    Currently Being Moderated

    Hi,

    I believe I have:  packed decimal.  This is where two digits are stored on a single byte, plus a sign at the end.  So the number '20130531' takes up 5 bytes on a text file instead of 8:  '02' in byte 1, '01' in byte 2 ... '1C'  in byte 5.  '020130531C'.  You need to use a hex viewer to be able to see the values, otherwise it just looks like junk on the text file.  So it needs to be exported in raw binary format.

    If I'm misunderstanding your question, please let me know.

    Paul

  • 6. Re: How to export packed decimal (comp-3) data
    rp0428 Guru
    Currently Being Moderated

    LuckyJack2001 wrote:

     

    Hi,

    I believe I have:  packed decimal.  This is where two digits are stored on a single byte, plus a sign at the end.  So the number '20130531' takes up 5 bytes on a text file instead of 8:  '02' in byte 1, '01' in byte 2 ... '1C'  in byte 5.  '020130531C'.  You need to use a hex viewer to be able to see the values, otherwise it just looks like junk on the text file.  So it needs to be exported in raw binary format.

     

    Well, I believe you haven't. I know what packed decimal is having worked with Cobol for over 20 years. Packed decimal is NOT text it is BINARY/RAW.

    You can't put BINARY/RAW into a text file. So you can't have the number '20130531' take up  5 bytes on a text file.

     

    How do either of these make ANY sense if you are expecting a binary format?

    >

    CAST_TO_VARCHAR2

     

    And when I said, "it gets exported as straight text", what I mean is that when I export the raw query results (no cast_to_varchar), I choose the 'text' format option, and it just exports the string '0123456789C' as a string, rather than as the binary packed data.

     

    otherwise it just looks like junk on the text file

    >

    You said you are using sql developer. That tool can NOT create a binary file so it is not possible for it to save data in the true BINARY/RAW format that packed decimal uses. Since packed decimal data is variable length there is nothing to distinguish one value from the next unless a length prefix is used or fixed length values are used by padding each value with bytes of zero.

     

    Packed Decimal CAN be converted put a string representation of packed decimal into a text file by converting each nybble of every byte to a character.

    That would use 9 bytes instead of the 5 you just mention in your reply.

     

    That is why I ask 'what you expect the export file to contain or what format the file is supposed to be in' and your answer is that you want a text file to contain binary data which it can't possibly do.

     

    If the mainframe import software expects to use a BINARY file format where your sample number from above takes 5 bytes then that just isn't going to happen with sql developer. You will need to write code that produces binary and, more importantly, you need to find out the answer to the question that i ask: what FORMAT does the file need to be in? If the file is BINARY/RAW there has to be some way to distinguish the column boundaries and the record boundaries possibly by using a fixed-width representation.

     

    If you plan to use sql developer you need to convert the packed decimal format to a text representation as described and then still provide a way to distinguish the column and record boundaries, perhaps by using a 'delimited' format.

  • 7. Re: How to export packed decimal (comp-3) data
    LuckyJack2001 Newbie
    Currently Being Moderated

    Hi, rp0428

    Well, not to nitpick, but I DID state the format.  You even quoted me  "...packed decimal...So it needs to be exported in raw binary format.".  And I have no idea that you have 20 years of cobol experience - I could only guess that maybe you did not know what packed decimal format is, so I explained it.  I am not a mind-reader.  Most programmers today don't know what it is.  I barely remembered myself, being out of the mainframe area for at least a dozen years.  And still out of it (I'm trying to help another team).

     

    rp0428 wrote:

    ...

    You can't put BINARY/RAW into a text file. So you can't have the number '20130531' take up  5 bytes on a text file.

    ...

     

    Yes you can.  Try it yourself.  Create the function I have above, execute "SELECT UTL_RAW.CAST_TO_VARCHAR2(CONVERT_COMP3(20130531)) AS VARCHR2_DT FROM DUAL;",

    export as text (no header, no enclosures).  And voila.  20130531 in packed decimal format, 5 bytes. This is why I believed that maybe I was on the right track.  Unfortunately not, because some numbers don't get translated correctly - the above one just happens to.

     

    Having said that, I very much appreciate your response to me.  I believe you have answered my question - and that is that I cannot export raw binary data using SQL Developer, at least easily. 


    Regards,

    Paul

  • 8. Re: How to export packed decimal (comp-3) data
    rp0428 Guru
    Currently Being Moderated

    >

    Yes you can.  Try it yourself.

    >

    NO - you CANNOT! You tried it yourself and it does NOT WORK. You are saving TEXT characters - not binary. And you keep referring your '20130531 taking 5 'bytes' on a text file. That is where your confusion is. You talk about TEXT and BINARY as if they are one and the same.

     

    It isn't nitpicking - it is being precise.

     

    The content of character strings depends on the language and the character set being used. That means that the binary representation of a set of characters can, and will be, totally different depending on the character set and language.

     

    The only way you can put BINARY/RAW data into a text file is the way I said above:

    >

    Packed Decimal CAN be converted put a string representation of packed decimal into a text file by converting each nybble of every byte to a character.

    That would use 9 bytes instead of the 5 you just mention in your reply.

    >

  • 9. Re: How to export packed decimal (comp-3) data
    LuckyJack2001 Newbie
    Currently Being Moderated

    Hello, sir.

    No need to get upset.  You stated that '20130531' could not be exported from sql developer as fixed decimal binary.  Obviously you did not try it, you are so sure of yourself.  Well hello, it does work with that number.  5 bytes, comp-3.  Using SQL Developer export as text.  Try it.

    Realistically, though, it does NOT work since it doesn't work for all numbers but for THIS NUMBER it works.  I was being literal.  And yes, the fact that it works for this number is probably due to the character set, etc.  In SQL Developer it's not translating 9s or 8s correctly.  I tried it in PL/SQL Developer and it only gets the 8s wrong.

     

    I think the point is that it does not work, and that we'll probably have to use another method to get this done.  If needed I'll open another question.

     

    Thank you for your help, I do appreciate it.

     

    Paul

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points