What am I missing here? Dump shows an extra character for this number that isn't a number

  • From: Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 3 Feb 2018 11:28:35 -0600

I cannot convert this number to a number - it's stored as varchar2(128
byte).

The number (that is not a number) is 18607 (5 digits) EXCEPT there's
something "more" to it if I dump it or trim it.

select session_id, session_serial, length(session_serial),
trim(session_serial), length(trim(session_serial)), dump(session_serial)
from ctaylor.job_history
where session_serial like '%18607%'
and session_id = 407
/

SESSION_ID SESSION_SERIAL LENGTH(SESSION_SERIAL) TRIM(SESSION_SERIAL)
LENGTH(TRIM(SESSION_SERIAL)) DUMP(SESSION_SERIAL)
407 18607 6 18607 6 Typ=1 Len=6: 49,56,54,48,55,0
How did Oracle internally store this?  Why is there a "0" on the dump?

Here's a comparison of another row that coverts to a number correctly:

SESSION_ID SESSION_SERIAL LENGTH(SESSION_SERIAL) TRIM(SESSION_SERIAL)
LENGTH(TRIM(SESSION_SERIAL)) DUMP(SESSION_SERIAL)
407 18607 6 18607 6 Typ=1 Len=6: 49,56,54,48,55,0
116 18607 5 18607 5 Typ=1 Len=5: 49,56,54,48,55
The original session_id, session_serial were stored in a string like (sid,
serial) and I used REGEXP_SUBSTR to break it into 2 separate
elements/columns.

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 02-2018