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.