CODEWITHSUNDEEP
oracle-databasessisodbc
RodWall
RodWall

Reputation: 159

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

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:

enter image description here

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.

Upvotes: 1

Views: 2167

Answers (2)

Michael Broughton
Michael Broughton

Reputation: 4055

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.

Upvotes: 1

Non Plus Ultra
Non Plus Ultra

Reputation: 906

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.

Upvotes: 2

Related Questions