Oracle SSIS Simple Query - "Literal does not match format string"

Question

I have a SQL Server Integration Services ETL Project to get data from an Oracle database. I'm using an ODBC Source component with the Connection Manager configured and working.

Inside ODBC Source, with the Connection Manager selected, I get all the Tables and Views in the dropdown list. The problem is this: I can't get data from some of the Views. I get this error:

Even if I write a simple SQL Command, I get the same error.

SELECT * FROM "SchemaName"."ViewName"

However, if I go to the Columns tab, all the columns appear correctly mapped.

I have checked for permissions on the Oracle side and everything seems OK. I can query other Views in the same schema, but some of them give me this error. As you can see, I'm not specifing any casts and the Views I am querying don't have date columns.

Does anyone understand why this error is coming up on specific Views? Thanks.

EDIT: The query works well in Oracle, so the problem is not in the View definition.

Is one of these possibly a date field that is being implicitly cast to string in a different format than the one expected due to a missmatch between server and client NLS parameters? — Michael Broughton
– Michael Broughton, Commented Nov 30, 2015 at 15:53
I will look further into that, but even if I run a SQL command like this: SELECT CODSEC FROM SchemaName.ViewName it returns the exact same error, even though CODSEC is an integer field. — RodWall
– RodWall, Commented Nov 30, 2015 at 16:40
It being an ORA-01861 error - the error is likely happening in the view SQL, not the ODBC. Try to connect to the DB using a SQL client and run your select statement to confirm that you get the same error. If so, then it is within the view definition itself, and - again - probably a conversion of string to date, or possibly to number. — Michael Broughton
– Michael Broughton, Commented Nov 30, 2015 at 16:46
Thanks for pointing that out, I forgot to include that in the original post. I have tested the query in Oracle and it ran without problems. Thanks again. — RodWall
– RodWall, Commented Nov 30, 2015 at 16:58
You tested it on the same database with the same data as where the error is occurring? If so, are there any implicit conversions happening in your mappings (i.e. source is a varchar, target is a date or vice versa?) — Michael Broughton
– Michael Broughton, Commented Nov 30, 2015 at 17:02

Non Plus Ultra · Accepted Answer · 2015-11-30 17:10:16Z

2

The problem is not in your query, neither in permissions. This error definitely comes from the view itself. Problem can occur everywhere you do a conversion from one datatype to another, so check for TO_NUMBER, TO_DATE or even TO_CHAR. In one of those statements, the format mask (or the data you want to convert) is invalid.

answered Nov 30, 2015 at 17:10

Non Plus Ultra

9067 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RodWall Over a year ago

Yes, the problem was data conversion. There was a TO_CHAR when it should be TO_DATE. Thank you.

Michael Broughton · Accepted Answer · 2015-11-30 17:14:42Z

1

This error is probably happening on execution of the view SQL, not in the ODBC, and occurs where there is one or more invalid values failing a conversion format. Usually this happens on a string-to-date conversion. I can replicate it with the following script.

> SQL> create table mytemp(x varchar2(10)); 
> Table created.
> 
> SQL> create or replace view myview as select to_date(x,'yyyy/mm/dd')
> xdat from mytemp; 
> View created
> 
> SQL> insert into mytemp values ('1965/12/12'); 
> 1 row created.
> 
> SQL> commit; 
> Commit complete.
> 
> SQL> select * from myview;
> 
> XDAT     
> ---------  
> 12-DEC-65

Yay, I have confirmed that the view is great!

Until....

> 
> SQL> insert into mytemp values ('12121965');  
> 1 row created.
> 
> SQL> commit;  
> Commit complete.
> 
> SQL> select * from myview;
> 
> select * from myview
>                * 
> Error at line 1 ORA-01861: literal does not match format string

So the SQL in the view is valid, the SQL to select from it is valid, and it all works great until one data row comes in that contains bad data.

Somewhere in the SQL being executed across that link, something like this is happening. Probably it is in the view. Maybe, if the select is part of an insert then it formatting on the insert side. But absolutely this is an issue of data conversion somewhere the in the executed SQL statement.

edited Nov 30, 2015 at 17:14

answered Nov 30, 2015 at 16:59

Michael Broughton

4,05516 silver badges12 bronze badges

1 Comment

RodWall Over a year ago

Michael Broughton, you are right. There was a TO_CHAR cast in the Where clause. When I commented it, Integration Services immediately got the data sucessfully. Since the column was of type date, it could not be casted to CHAR. I changed the cast to TO_DATE, gave the format and everything got working. Thank you for all your answers to my problem, it was really helpful.

Collectives™ on Stack Overflow

Oracle SSIS Simple Query - "Literal does not match format string"

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related