Thanks for the question, HRVOJE.

Asked: July 06, 2018 - 9:38 am UTC

Last updated: July 26, 2018 - 2:38 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

Hello Tom !

It seems that Oracle has a bug in TO_NUMBER function on strings with trailing CHR(0).

For example it happens
when SUBSTR function is used for which there is not defined length parameter.

Example:

select substr(session_id, 1, instr(session_id, ',')-1) s,
       dump(substr(session_id, 1, instr(session_id, ',')-1)) s_d,
       substr(session_id, instr(session_id, ',') + 1) sn,
       dump(substr(session_id, instr(session_id, ',') + 1)) sn_d,
       rtrim(substr(session_id, instr(session_id, ',') + 1), chr(0)) asn,
       dump(rtrim(substr(session_id, instr(session_id, ',') + 1), chr(0))) asn_d
 from USER_SCHEDULER_JOB_RUN_DETAILS 
  where job_name = :some_job_name;

One record in my case looks like this:

S     S_D                         SN      SN_D                             ASN     ASN_D
================================================================================================
3712  Typ=1 Len=4: 51,55,49,50    59803   Typ=1 Len=6: 53,57,56,48,51,0    59803   Typ=1 Len=5: 53,57,56,48,51

Although the results from SQL*Plus looks the same
equality check on
substr(session_id, instr(session_id, ',') + 1)
and
rtrim(substr(session_id, instr(session_id, ',') + 1), chr(0))
is FALSE !
And of course it should not be !

And even worse - TO_NUMBER function fails with INVALID NUMBER when applied on
string which has trailing CHR(0).
I.e. in this case TO_NUMBER(SN) raises an exception and TO_NUMBER(ASN) does not.

USER_SCHEDULER_JOB_DETAILS is just an example here.
It happens also on normal tables.
And also I've found it only when SUBSTR function is used without LENGTH parameter,
but maybe it also happens for some other string functions.
What is strange that this kind of SUBSTR does not always produce strings with trailing CHR(0).
Only in some cases.

I can't find anything similar in BUGs repository in Oracle Support.

Can you check please if there is some patch for this
and if some other string functions are affected.

Thank you !

And yes - I've found it on 11.2, 12.1 and 12.2 versions.

BR,
Hrvoje

and Chris said...

I'm not sure what you're claiming is a bug here. chr(0) isn't a number. It returns the end of string terminator (in C). Which is not a number.

So you'll get an exception if you try and to_number it:

select to_number ( '1' || chr(0) ) from dual;

ORA-01722: invalid number

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Then SUBSTR is problematic...

HRVOJE DEVCIC, July 07, 2018 - 4:34 pm UTC

Hello Tom!

OK. I can take it as an explanation that TO_NUMBER can have problems
with C string delimiter 0. Although, it can just stop before.
That would be better solution/implementation.
But then SUBSTR is buggy as it sometimes returns string internally represented with C delimiter 0 and length one bigger then only length of number representation and sometimes it does not.
I'm talking here about SUBSTR on some normal table column. Not about some fancy staff.
If TO_NUMBER(SUBSTR(... is to be used one then always has to wrap SUBSTR in RTRIM(SUBSTR(..., CHR(0)) to be sure that INVALID NUMBER is not raised.
I'll repeat my original claim - it seems that is happening only when SUBSTR is used without LENGTH parameter.

Also my original question goes - are there some other string functions which can return C delimiter 0 as last byte?
To be prepared...

Thank you!

BR,
Hrvoje

July 09, 2018 - 10:41 am UTC

But then SUBSTR is buggy as it sometimes returns string internally represented with C delimiter 0 and length one bigger then only length of number representation and sometimes it does not.

Can you share a test case demonstrating this? This has to be complete and stand-alone; it must have create table + inserts + query that shows this problem on this data.

are there some other string functions which can return C delimiter 0 as last byte?

I'm not sure what you mean, could you clarify?

Not a bug

Anton, July 10, 2018 - 2:51 pm UTC

To Oracle chr(0), string terminator in C, is a character like any other.
See for instance
with tst as
( select 'abc' x from dual
union all select 'def' || chr(0) from dual
union all select 'abc' || chr(0) || 'def' from dual
)
select x
, length( x )
, substr( x, 1, 100 )
, length( substr( x, 1, 100 ) )
, x || 'x'
, length( x || 'x' )
, utl_raw.cast_to_raw( x )
from tst

But some front-end tools (Toad,PL/SQL DEveloper) don't display the last part of a string after and including the chr(0)

July 10, 2018 - 4:43 pm UTC

Yeah, I'm not seeing a bug here either...

Not SUBSTR, although...

HRVOJE DEVČIĆ, July 13, 2018 - 9:07 am UTC

Hello Tom !

I've already sent an example.

I'll repeat it:

create table test_t as
select dump(session_id) sid,
       substr(session_id, 1, instr(session_id, ',')-1) s,
       dump(substr(session_id, 1, instr(session_id, ',')-1)) s_d,
       substr(session_id, instr(session_id, ',') + 1) sn,
       dump(substr(session_id, instr(session_id, ',') + 1)) sn_d,
       rtrim(substr(session_id, instr(session_id, ',') + 1), chr(0)) asn,
       dump(rtrim(substr(session_id, instr(session_id, ',') + 1), chr(0))) asn_d
 from USER_SCHEDULER_JOB_RUN_DETAILS
where log_date >= trunc(sysdate)
  and substr(session_id, instr(session_id, ',') + 1) like '%' || chr(0);

The results are the same regardless of client (SQL*Plus, Toad, ...)

But now I see where is the problem !

Problematic is Oracle internal view USER_SCHEDULER_JOB_RUN_DETAILS, i.e. to be more precise
underlying SYS table:
SYS.SCHEDULER$_JOB_RUN_DETAILS.

You store in the field SESSION_ID concatenation of SID and SERIAL_NUMBER# separated by comma.
But sometimes there is added CHR(0) at the end of that field and sometimes not !

Now I'm not sure anymore if SUBSTR adds CHR(0) at the end of string in some cases because it seems that I've tested mainly with that SESSION_ID field from USER_SCHEDULER_JOB_RUN_DETAILS.

You said you did not understand what I mean with question - are there some other string functions which adds CHR(0) at the end of the string.
It is simple. As I believed that SUBSTR adds (randomly?) in some case CHR(0) at the end of the string - I've asked are there some other string functions where it can happen.

Unfortunately, I can't send you here dump of USER_SCHEDULER_JOB_RUN_DETAILS content.

BR,
Hrvoje

July 16, 2018 - 9:24 am UTC

When you send an example, it must include everything to reproduce it. i.e:

- Create table
- Inserts
- Select

Relying on information in dictionary views is not reproducible. For example, I've queried *_SCHEDULER_JOB_RUN_DETAILS across many databases and got output like:

select count(*)
 from DBA_SCHEDULER_JOB_RUN_DETAILS;

COUNT(*)   
     13745 
 
select count(*)
 from DBA_SCHEDULER_JOB_RUN_DETAILS
where substr(session_id, instr(session_id, ',') + 1) like '%' || chr(0);


COUNT(*)   
         0

i.e. zero rows where substr'ing the session_id "adds" chr(0).

As I believed that SUBSTR adds (randomly?) in some case CHR(0) at the end of the string

You need to prove that substr is doing this. And it's not in the underlying data. I'm yet to be convinced that substr is adding chr(0).

STOPPED jobs

HRVOJE DEVČIĆ, July 17, 2018 - 3:02 pm UTC

Hello Tom !

Hurrah !
I've found the pattern !

We have event-based job which STOPs scheduler jobs which have set MAX_DURATION parameter.
CHR(0) is added only to STOPPED jobs in the SESSION_ID field in the USER_SCHEDULER_JOB_RUN_DETAILS.
This is happening in both cases - when jobs are stopped manually or by this event-based job.
But not in all cases !
Sometimes this CHR(0) is not added.
But again - in our USER_SCHEDULER_JOB_RUN_DETAILS log view this is recorded only for STOPPED jobs !
All other status does not have CHR(0) !

How can I send you 100 of example records here?

BR,
Hrvoje

July 18, 2018 - 4:44 am UTC

Can you do a datapump using the VIEWS_AS_TABLES as option and export USER_SCHEDULER_JOB_RUN_DETAILS and mail it to asktom_us@oracle.com with the subject:

ID 9538313800346741665

STOPPED_JOBS with CHR(0) at the end of SESSION_ID

HRVOJE DEVČIĆ, July 18, 2018 - 7:31 am UTC

Hello Tom !

It seems that Oracle has a restriction on exporting SYS objects.
So I've created a CTAS table from USER_SCHEDULER_JOB_RUN_DETAILS
and condition STATUS = 'STOPPED' as we have large number of records in this log.
There are both records with CHR(0) at the end and those without it.

Sending dump file to you by mail.

Thank you !

BR,
Hrvoje

July 25, 2018 - 1:27 am UTC

Hi Hrvoje,

Thanks for that - I've reproduced the error on my 12.2 instance

SQL> create table t ( x timestamp, y int );

Table created.

SQL> create or replace
  2  procedure myproc is
  3  begin
  4    for i in 1 .. 20
  5    loop
  6      insert into t values (systimestamp,i);commit;
  7      dbms_lock.sleep(2);
  8    end loop;
  9  end;
 10  /

Procedure created.

SQL>
SQL> begin
  2    dbms_scheduler.create_job (
  3      job_name        => 'myjob',
  4      job_type        => 'plsql_block',
  5      job_action      => 'begin myproc; end;',
  6      start_date      => systimestamp,
  7      repeat_interval => 'freq=minutely; bysecond=0;',
  8      enabled         => true);
  9  end;
 10  /

PL/SQL procedure successfully completed.
--
-- waited for 1 successful execution, and then stopped the second one in flight
--
SQL> exec dbms_scheduler.stop_job('MYJOB',force=>true)

PL/SQL procedure successfully completed.

SQL> select status, session_id, dump(session_id)
  2  from USER_SCHEDULER_JOB_RUN_DETAILS;

STATUS
------------------------------
SESSION_ID
-------------------------------------------------------------
DUMP(SESSION_ID)
-------------------------------------------------------------
SUCCEEDED
984,53037
Typ=1 Len=9: 57,56,52,44,53,51,48,51,55

STOPPED
983,28542
Typ=1 Len=10: 57,56,51,44,50,56,53,52,50,0

I'll log a bug. In the interim a RTRIM(SESSION_ID,chr(0)) should take care of it

STOPPED jobs bug

HRVOJE DEVČIĆ, July 25, 2018 - 7:19 am UTC

Hello Tom !

Thank you !

OK.
So we can take it as a fact that SUBSTR or other string fuctions do not add CHR(0) at the end of the string,
but this is a case only for this [USER|ALL|DBA]_SCHEDULER_JOB_RUN_DETAILS view column SESSION_ID?

If this will be public bug can you please send me the bug ID?

Thanks again !

BR,
Hrvoje

July 26, 2018 - 2:38 am UTC

So we can take it as a fact that SUBSTR or other string fuctions do not add CHR(0) at the end of the string,
but this is a case only for this [USER|ALL|DBA]_SCHEDULER_JOB_RUN_DETAILS view column SESSION_ID?

Correct - it should never happen.

If this will be public bug can you please send me the bug ID?

Currently an internal bug whilst its reviewed.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Then SUBSTR is problematic...

Not a bug

Not SUBSTR, although...

STOPPED jobs

STOPPED_JOBS with CHR(0) at the end of SESSION_ID

STOPPED jobs bug

More to Explore

SQL

Equality check and TO_NUMBER bug with trailing CHR(0) on SUBSTR function

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Then SUBSTR is problematic...

Not a bug

Not SUBSTR, although...

STOPPED jobs

STOPPED_JOBS with CHR(0) at the end of SESSION_ID

STOPPED jobs bug

More to Explore

SQL