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

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