ORA-01858 error when referencing a view from SQL Server (SSIS)

Question

We are calling an Oracle view from an SQL Server SSIS package. When we reference this view from SSIS, we are getting the below ORA-01858 error. This view has no issues being accessed within Oracle.

ORA-01858: a non-numeric character was found where a numeric was expected

I have narrowed down to the columns where we are bringing in the data from a varchar2 flex field as shown below. This varchar2 field is storing the date and I am converting as shown below. Oracle has no problems with this. We are running into this error ever since we started calling this from SQL Server.

to_char(to_date(substr(wdj.dff_detail,1,9)),'YYYYMM')

Thanks in advance.

Seems like the root of the problem is choosing the wrong datatype in Oracle. If you are storing a date you should use a date datatype, not a string datatype. This has nothing to do with which DBMS. — Sean Lange

Littlefoot Littlefoot · Accepted Answer · 2020-03-06T19:40:09

We lack some critical information: what exactly do you store into that column? I know, it is a string that represents date value, but - in which format?

Looking at code you posted, it seems that date value occupies first 9 characters of that string, so I presume that it is dd-mon-yy.

If that's so, then look at the following example:

SQL> alter session set nls_date_language = 'english';

Session altered.

SQL> with wdj (dff_detail) as
  2    (select '06-mar-20 20:31' from dual union all
  3     select '25-aug-19 13:30' from dual
  4    )
  5  select
  6                    substr(dff_detail, 1, 9)                          sbstr,
  7            to_date(substr(dff_detail, 1, 9), 'dd-mon-yy')            dsbstr,
  8    to_char(to_date(substr(dff_detail, 1, 9), 'dd-mon-yy'), 'yyyymm') ymdbstr
  9  from wdj;

SBSTR                                DSBSTR   YMDBST
------------------------------------ -------- ------
06-mar-20                            06.03.20 202003
25-aug-19                            25.08.19 201908

SQL>

lines #1 - 4: sample data
line #6: substring of the first 9 characters
line #7: substring converted to date, using appropriate format mask! That's what you miss in your code
line #8: date displayed in yyyymm format (through the to_char function)

Of course, it won't work if any value doesn't follow format I presumed (e.g. mar-06-20), if it contains invalid values (e.g. 68-abc-83) etc.

Conclusion? Don't store dates as strings.

ORA-01858 error when referencing a view from SQL Server (SSIS)

1 Answers