Error while pulling data from an Oracle database. ORA-01858: a non-numeric character was found where a numeric was expected

Question

I'm trying to pull data from an Oracle database using SSIS. When I try to select a few fields from the source table, it returns the following error message:

    [OLE DB Source [47]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
    An OLE DB record is available.  Source: "OraOLEDB"  Hresult: 0x80040E14  Description: "ORA-01858: a non-numeric character was found where a numeric was expected".
    An OLE DB record is available.  Source: "OraOLEDB"  Hresult: 0x80004005  Description: "ORA-01858: a non-numeric character was found where a numeric was expected".

The source columns are a combination of numeric and texts, and I've also tried selecting one of them, which didn't work. I'm using the Oracle client 11.2.0.1, and it works fine with any other data sources I have connected to so far. How can I resolve this error?

Answer 1

Looks like error at Oracle side.

Please check your Source query that pullls data from Oracle. Ensure if correct data type is used.

---

-Vaibhav Chaudhari

Answer 2

Hi, thanks for your input.

The source table has different data types: NUMBER, VARCHAR2, CHAR and DATE, and simply selecting one or more of those columns fails. The same query works fine on Toad Client - any clue why it might not work specifically on SSIS?

Answer 3

Hi H.James,

According to your description, the issue is a non-numeric character was found where a numeric was expected while pulling data from an Oracle database in SSIS.

Based on the error message, the issue should be you are comparing a number column to a non-number column in a query. Such as the query below (ConfID is a number, Sdate is a date):
 where C.ConfID in (select C.Sdate
                   from Conference_C C
                   where C.Sdate < '1-July-12')

Besides, a default behavior for the Oracle OleDb Provider that change the NLS Date Format of the session to 'YYYY-MM-DD HH24:MI:SS can also cause the issue. For more details about this issue, please refer to the following blog:
http://blogs.msdn.com/b/dataaccesstechnologies/archive/2012/01/20/every-bug-is-a-microsoft-bug-until-proven-otherwise.aspx

Thanks,
Katherine Xiong

---

Katherine Xiong
TechNet Community Support