Re: What am I missing here? Dump shows an extra character for this number that isn't a number
Date: Sat, 3 Feb 2018 17:55:32 +0000
Message-ID: <MM1P12301MB1658B4FFE458290B3F015EEFA5F80_at_MM1P12301MB1658.GBRP123.PROD.OUTLOOK.COM>
What clients do you have inserting this data.
There are some OCI calls that basically promise to supply data in the internal Oracle format - but if they go wrong you get garbage in place. You seem to have acquired "null" (i.e. zero) terminated strings - do you know if they appear often ?
You could scan for: substr(serial,length(serial),1) = chr(0) to identify them and update them to shorten the string by one byte.
Alternatively, if you just want to see the number you could do something like:
select to_number(case substr(v1,length(v1),1) when chr(0) then substr(v1,1,length(v1)-1) else v1 end) from t4;
Regards
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
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:
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.
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Chris Taylor <christopherdtaylor1994_at_gmail.com>
Sent: 03 February 2018 17:28:35
To: ORACLE-L
Subject: What am I missing here? Dump shows an extra character for this number that isn't a number
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
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
--
http://www.freelists.org/webpage/oracle-l
Received on Sat Feb 03 2018 - 18:55:32 CET