Jonathan,
Thanks for the reply - in this case its DBA_SCHEDULER_JOB_RUN_DETAILS and
the SESSION_ID column.
Some of the values have the chr(0) at end of the string while others
don't.
So, I "think" the answer to your question here is that it's the Oracle
engine itself doing this in some cases but not all?
I can get around it by doing this:
REGEXP_REPLACE(session_id, '[^[:print:]]', '') (as part of my replace
string) which appears to remove non-printable characters.
215 rows out of 285,730.
SELECT session_id
,REGEXP_SUBSTR ( session_id
,'[^,]+'
,1
,1 )
AS session_id
,REGEXP_SUBSTR ( session_id
,'[^,]+'
,1
,2 )
AS session_serial#
,is_number ( REGEXP_SUBSTR ( session_id
,'[^,]+'
,1
,2 ) )
session_serial#_isnumeric
,DUMP ( session_id )
,RAWTOHEX ( session_id )
,REGEXP_REPLACE ( session_id, '[^[:print:]]', '' )
AS removed_nonprintable
,RAWTOHEX ( REGEXP_REPLACE ( session_id, '[^[:print:]]', '' ) )
AS rawtohex_cleaned
FROM dba_scheduler_job_run_details
WHERE RAWTOHEX ( session_id ) LIKE '%00' --215
rows
/
Chris
On Sat, Feb 3, 2018 at 11:55 AM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx
wrote:
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.
