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
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
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
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.
--
//www.freelists.org/webpage/oracle-l
