Thanks for the question, Jean.
Asked: October 02, 2006 - 11:22 am UTC
Last updated: October 02, 2006 - 12:05 pm UTC
Version: 9.0.2.6
Viewed 1000+ times
You Asked
Hi Tom,
I used you comp3.convert package and it works fine. I also used utl_raw.cast_to_varchar2 with it. My question is how do I get the output in packed format into a spool file preserving the leading zeros. I have a 9 char field that I send to comp3.convert and get the 9 digets out with the sign. I use utl_raw.cast_to_varchar2 to get the packed output but it suppresses the leading zeros. Example, input into your package is 000004500 and I get 000004500c. After it is passed through utl_raw.cast_to_varchar, I get a the output in packed format with the leading zeros suppressed so my 5 position output field ends up without the leading zeros. This file is being send to an IMS system that needs the leading zeros.
Here is an example
SQL> column a format a10
SQL>
Wrote file afiedt.buf
1* select comp3.convert('000004500') a from dual
SQL> /
A
----------
000004500C
SQL>
Wrote file afiedt.buf
1* select utl_raw.cast_to_varchar2(comp3.convert('000004500')) a from dual
SQL>
SQL> /
A
----------
P
and Tom said...
well, it is having the leading zeroes, they are there.
when you called comp3.convert, that converted the input STRING into a RAW. When you give sqlplus a RAW, it prints out the hex. So, that first string, doesn't really have leading "zero character strings". it has a series of bytes and you are seeing HEX codes
when you trick it and say "pretend this raw is a string", it prints it out. problem is, the leading two bytes are all binary zero's. You cannot "see" a binary zero.
ops$tkyte%ORA10GR2> select comp3.convert('000004500') a from dual;
A
-------------------------
000004500C
ops$tkyte%ORA10GR2> select utl_raw.cast_to_varchar2(comp3.convert('000004500')) a from dual;
A
-------------------------
P
ops$tkyte%ORA10GR2> select dump(utl_raw.cast_to_varchar2(comp3.convert('000004500')),16) a from dual;
A
-------------------------
Typ=1 Len=5: 0,0,4,50,c
see, it is all there, safe and sound.
Is this answer out of date? If it is, please let us know via a Comment