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

That's a good point except that table was straight loaded from dba_scheduler_job_run_details. I've confirmed there are 200+ rows in that view that have the offending character (out of 281k rows).

Thanks,
Chris

On Sun, Feb 4, 2018 at 8:08 PM, Hemant K Chitale <hemantkchitale_at_gmail.com> wrote:

> You are querying ctaylor.job_history
> this is a custom table, so check how values are copied from
> dba_scheduler_job_run_details into this table.
>
>
> Hemant K Chitale
>
>
>
> On Sun, Feb 4, 2018 at 2:12 AM, Chris Taylor <
> christopherdtaylor1994_at_gmail.com> wrote:
>
>> 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_at_jlcomp.demon.co.uk> 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_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
>>>
>>> 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-l