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

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

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