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

Hi Mark
To your first question:
There are two different SQL constructs (called operators in SQL Reference) IS [NOT] NULL
[NOT] LIKE
Effectively, you are saying why the second clause is LIKE and not IS LIKE? I think this is the way IBM designed it back in 70-ties or 80-ties, and those who
did it already retired or became managers. Anyway, I tested it in Sybase and it works exactly the same way as in Oracle.

Vsevolod

Mark Greaves wrote:

> 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 Fri May 29 1998 - 18:20:38 CDT