What am I missing here? Dump shows an extra character for this number that isn't a number
Date: Sat, 3 Feb 2018 11:28:35 -0600
Message-ID: <CAP79kiSuS6hrNi8UvCPuK9kxzrsO3-N_nR1tutbN-Kdzg9_qLw_at_mail.gmail.com>
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Feb 03 2018 - 18:28:35 CET