CODEWITHSUNDEEP
oracle-database
Patrick
Patrick

Reputation: 2577

Error using SYSDATE in oracle

The error I'm getting is "no more data to read from socket" on this line

 COALESCE( NonRewrite.SALES_LOG_DATE, SYSDATE) "SoldDate"

Oddly, I can just do NonRewrite.SALES_LOG_DATE and there are no null values anyway. If I do SYSDATE alone it gives me the same error.

If I replace SYSDATE with TO_DATE('18-MAR-2016')), I do not receive an error.

Upvotes: 0

Views: 542

Answers (1)

Jon Heller
Jon Heller

Reputation: 36807

"no more data to read from socket" is a generic error that doesn't really tell you the problem. That error means a database process crashed so hard it didn't even raise a proper exception and the connection died unexpectedly.

When that happens Oracle stores an error message in the alert log, which you can find in the path found from this query: select value from v$parameter where name = 'background_dump_dest';. Look for a file named alert*.log.

In that file there will probably be an ORA-600 or ORA-7445 error around the same time as the exception. The error usually has several parameters, for example ORA-00600: internal error code, arguments: [ktfbtgex-7], [1015817], [1024], [1015816], [], [], [], [].

The first parameter is usually the most important one. If you're lucky you can Google it and find an answer. But usually you'll need to login to support.oracle.com and search for that error. There's a special page for that, search for the "ora-600 tool". That will bring up a page to search for the first parameter of the error message.

Hopefully that tool will bring up specific documents that explain the problem. There may be a patch, or a workaround, or possibly no information at all. It's usually easiest to workaround the problem by avoiding some very specific combination of features, possibly by slightly re-writing the query.

Post the error message, the exact Oracle version, and the entire query and someone may be able to help. If the query is large you'll want to shrink it as much as possible. Shrinking the query and making a reproducible test case may take a few hours but is necessary to truly understand the problem. People who don't spend the time doing that usually end up avoiding important features and give bad advice to other developers like "avoid SYSDATE!".

These types of errors may take a long time to fix.

Upvotes: 2

Related Questions