Inconsistency in SQL syntax, and ORA-3113 and ORA-1041 errors

This is really a two part question. I am running Oracle 8.0.4.0.0 under Sun OS 5.5.1.

First, why is it that when selecting from a table, where you want to return rows where a certain column is not null, the syntax is of the form:

    SELECT * FROM emp WHERE comm IS NOT NULL;

But when you want to return rows where a certain column is not like a pattern, the syntax is:

    SELECT * FROM emp WHERE ename NOT LIKE 'S%';

In other words, the keyword "IS" is needed for the NOT NULL condition, but it is not used for the NOT LIKE condition. This seems rather inconsistent to me.

My second question is related to the above. When I mistakenly keyed in the second query with the "IS" keyword,

    SELECT * FROM emp WHERE ename IS NOT LIKE 'S%';

I get the error message

    ORA-03113: end-of-file on communication channel

Then, if I enter any other query (legitimate or not), I get the message

    ORA-01041: internal error. hostdef extension doesn't exist

The action for ORA-01041 is "report as a bug." I did call Oracle Support and left a message (they were experiencing a "peak level of calls" - gee, when are they ever NOT experiencing a peak???), but haven't heard back yet. I wonder if this is a new bug to Oracle8.

Any comments?

Mark Greaves
DBA Consultant
Berger & Co. Received on Thu May 28 1998 - 14:11:03 CDT