HTTP 404 error on incremental load with update & delete detection

Hi Wouter

What is the specific Oracle provider you use? It may relate to what provider type it is.

Hi Thomas, I'm using the OracleClient Data Provider; ODX storage is ADLS gen2

Can you use an Oracle SQL Profiler tool to review what query statements were sent to the source for HTTP 404 error ?

Also, there is a new option for the ADO and OLEDB data sources called "Date and time syntax'

Options are: Default, ANSI, and ODBC.
For Oracle, you will need to set it to ANSI to get the date-time formatting to work.

@Syed Yousuf not sure how to use an SQL Profiler tool. I have already set the datetime syntax to ANSI (already asked this in a different topic and this was answered by you). Before setting it to ANSI the incremental load did not work at all. After setting it to ANSI, the incremental load does work correctly, but not when checking the delete detection box.

Given the fact that the error is an HTTP error I think the problem lies more on the ODX storage side, because HTTP is of course not used to connect to Oracle, but it is used to connect to the adls gen2 storage container. 

Hi Wouter

You may use an Oracle query Profiler tool to review what query statements were sent to the source which led to HTTP 404 error in ODX processing.  Also examine the folders in ADLS storage.

For additional troubleshooting, you may open a ticket with our support team.

Hi @wouter.goslinga did you manage to resolve the error? Would you like to setup a screenshare to investigate further?

Hi @Christian Hauggaard , no it is not solved yet, setting up a screenshare would be a good idea, i'll send you a DM.

Hi @wouter.goslinga thanks for the call. What was the outcome of the test with the TimeXtender Oracle data source? Did you experience the same issue there, or was it only for the OracleClient Data Provider data source?

Hi @Christian Hauggaard ; the TimeXtender Oracle data source fails to synchronize, so I'm unable to check if the problem exists with this provider.

@wouter.goslinga what error are you getting for the TimeXtender Oracle data source provider?

The error when syncing is this one:

The execution failed with error:
System.Data.OracleClient.OracleException (0x80131938): ORA-01555: snapshot too old: rollback segment number 23 with name "_SYSSMU23_3997554902$" too small
 

There is a good discussion on the above error on Oracle forums. 

Here is an example. Does that help ?

@Syed Yousuf thanks for the link, but it contains a lot of very technical Oracle stuff that I do not understand and to be honest, IMHO it should not be necessary to take a deep dive in to source system database settings or find an Oracle DBA just to be able to sync it in the ODX. Because the source system is managed by its vendor, I have no influence on settings in the source system (or, more specific, the Oracle database), neither do I have any influence on the query that the ODX server runs when synchronizing.

I think it should be the case that if I set up an Oracle connection properly (which I did, the connection shows successful), the sync should work without failing.

The regular Oracle Data Source (the one that gives me the HTTP error which this topic is about) does sync correctly, so it should be possible for the TimeXtender Oracle data source to do this as well. 

Hi @wouter.goslinga when I google this Oracle error, the search results suggest that the error may be resolved by retrying the sync task to a time when during server off-peak hours (i.e. it might be a transient issue). 

In terms of the HTTP error for the OracleClient Data Provider data source, I have been able to reproduce this now, and will get back to you on this.