Devart Forums

Hello,

We have come across a strange issue where it seems that the generated SQL sent to Oracle is - for lack of a better term - crashing Oracle. It is simply a query on a single entity with one condition (WHERE clause).

If we leave out the condition, all rows are returned successfully as expected. However, with a condition that will return a record, we receive:
ORA-03113: end-of-file on communication channel

If we put in a condition that returns no records, the error is not received.

We were able to take the Linq query and get the generated SQL from query.ToString(), then paste that SQL into Oracle SQL Developer and execute it. When we do this, it closes the connection. Oracle just cannot handle the query and cannot even return an error message. It just simply closes the pipe and disconnects the client altogether. We see:
"No more data to read from socket" and "IO Error: Software caused connection abort: socket write error" and "Closed Connection".

What could be our next troubleshooting step?

Thanks in advance.

Hello,

The problem appears to be not connected with our product.

We suggest you to try restarting the Oracle server.

Hi Alexander,

That is correct, the application becomes disconnected from the server. The problem is that it is not reasonable to restart the Oracle database or the application every time someone queries for a record. The application and database work great and return data from queries just fine until this one particular query is executed. That is what disconnects and shuts down the channel completely.

As I mentioned, the query that’s generated by dotConnect / EF is what is crashing Oracle. Executing that query directly in Oracle SQL Developer also terminates the connection.

When I compare the SQL generated between the two queries, the only difference in the filtered query is that it adds another OUTER JOIN, then an INNER JOIN to the table with the WHERE clause.

I could send you the SQL statements if it would help. They’re quite large, but you’ll be able to see what the difference is at the end.

Thank you in advance.

P.S. I just realized that I posted this under dbForge, not under dotConnect. If a moderator could move this for me, I'd appreciate it.

Hegg wrote:When I compare the SQL generated between the two queries, the only difference in the filtered query is that it adds another OUTER JOIN, then an INNER JOIN to the table with the WHERE clause.

Please refer to http://www.devart.com/dotconnect/oracle/docs/?EF.html , the Limitations section:
"Oracle doesn't have its equivalents for the SQL Server OUTER APPLY and CROSS APPLY constructions (this issue is described at http://msdn.microsoft.com/en-us/library/bb896273.aspx ). Microsoft decided not to change the current behaviour. So, users who encounter the mentioned obstacles should re-construct their LINQ-queries."

Hegg wrote:I could send you the SQL statements if it would help. They’re quite large, but you’ll be able to see what the difference is at the end.

If possible, also send to us ( http://www.devart.com/company/contact.html ) a small test project with the corresponding DDL/DML script so that we can reproduce the issue in our environment.

The problem was that Oracle was crashing on the query and it was requested that we open a ticket directly with Oracle to resolve the crash.

However, we did restructure queries and have not had any further issues - a much more preferable solution than opening a ticket with Oracle!

Thank you for the assistance.