To homepage To homepage
Solved

Oracle DB - ORA-01830 - datefilter

  • 12 November 2019
  • 5 replies
  • 74 views
M
Rank

Hi,

I am trying to import data from a Oracle db and need a datefilter to limit the amount of data extracted, for instance last 7 days.

Version: Discovery Hub 19.2.9.64

Everything works fine when I test the SQL in Oracle, but when I put it into TX this error occurs every time:
ORA-01830: date format picture ends before converting entire input string

Details:

ORA-01830: date format picture ends before converting entire input string ...
Module: OraOLEDB
System.Data.OleDb.OleDbException
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForMultpleResults(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at TimeXtender.DataManager.StepTransferSSISExecute.BulkCopySource(Boolean executeIncrementalLoad, DataSource dataSource, VariableResolveObject dynamicResolveObject)
at TimeXtender.DataManager.StepTransferSSISExecute.DoStageDataSource(Boolean isIncrementalAllowed, DataSource dataSource, StepSetup stepSetup, VariableResolveObject dynamicResolveObject)
at TimeXtender.DataManager.StepTransferSSISExecute.DoStage(StepSetup stepSetup, VariableResolveObject dynamicResolveObject)

SQL for filter defined as a Projct Variable on the Oracle datasource:

SELECT TO_DATE(CURRENT_DATE-7, 'DD.MM.YYYY') FROM DUAL

Custom data selection rule examples that fails:

[Datefield]>='[Project Variable]'

alternatively

TO_DATE([Date Column], 'DD.MM.YYYY')>='[Project Variable]'

In Oracle [Date Column] is defined as data type: DATE

In TX it is translated to: Datetime2(7)

How can I debug this?

For instance I can't find any place in TX where I can find the exact SQL sent to Oracle data source when running the execution. So what is happening is kind of black box.

Please advise.

icon

Best answer by mahmoud.ismail 13 November 2019, 09:50

View original

5 replies

M
Rank
Badge

Hi Marius

Thank you for your post.
My suggested approach for this is to utilize the override data types feature.
The article Using the override data type feature by my colleague describes how to set up both general and specific rules.

I believe this would help you set up your data selection rule.

Br
Mahmoud Ismail

M
Rank

Hi,

I have tried different versions of the override data types several times without other results than different Oracle error codes. For instance to datetime2 or nvarchar. My understanding of that feature is that it transforms the values before loading them into MS SQL database.

My need is for the SQL that TX sends to Oracle database to contain a valid WHERE clause filtering a date column. I find it rare if no one else has done or tested this before. Oracle databases and date filters for incremental load are quite common. I only have read access to Oracle datebase so are unable to trace the TX query there.

 

Br

Marius Gundersen

M
Rank
Badge

Hi Marius

You are right, this is not the first time we get this, and I have found something that might help you here:

The Oracle default date format when NLS_FORMAT is not set it DD-MON-YYYY; ie 01-JAN-2002 for January 1st 2002.

It works perfectly on my machine.

If you want the solution to be independent from NLS_FORMAT, then you should use the following selection rule instead:

%CALC%to_date('2002/01/01', 'yyyy/mm/dd')

Alternatively, it should work fine with Datetime2, so if you set the project variable to be in that form, that might also be a solution for you.

Br
Mahmoud Ismail

M
Rank

Thank you Mahmoud, based on your input I have successfully generated a static date filter. With some further adjustments I should be able to make this dynamic.


Br
Marius Gundersen

M
Rank
Badge

Anytime :)

Glad I could help.

Br
Mahmoud Ismail

Reply


Terms and ConditionsCookie settings