Re: SQL Standard
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Thu, 22 Apr 2010 07:43:56 -0700 (PDT)
Message-ID: <e50eb20d-9826-44ab-b8b8-2aa0d0443af3_at_z7g2000yqb.googlegroups.com>
On Apr 21, 10:52 pm, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> I am reading an old book about SQL standard, "A Guide to SQL Standard",
> by C.J. Date. On the page 87, the book lists some functions that are part
> of the SQL standard:
>
> Connected to:
> Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
> Production
> With the Partitioning, OLAP, Data Mining and Real Application Testing
> options
>
> SQL> select current_user from
> dual;
> select current_user from dual
> *
> ERROR at line 1:
> ORA-00904: "CURRENT_USER": invalid identifier
>
> Elapsed: 00:00:00.15
> SQL> select current_time() from
> dual;
> select current_time() from dual
> *
> ERROR at line 1:
> ORA-00904: "CURRENT_TIME": invalid identifier
>
> Elapsed: 00:00:00.13
> SQL> select session_user from
> dual;
> select session_user from dual
> *
> ERROR at line 1:
> ORA-00904: "SESSION_USER": invalid identifier
>
> Elapsed: 00:00:00.13
> SQL> select session_user() from
> dual;
> select session_user() from dual
> *
> ERROR at line 1:
> ORA-00904: "SESSION_USER": invalid identifier
>
> Elapsed: 00:00:00.14
> SQL> show
> session_user
> SP2-0735: unknown SHOW option beginning "session_us..."
> SQL> select bit_length('ABCD') from
> dual;
> select bit_length('ABCD') from dual
> *
> ERROR at line 1:
> ORA-00904: "BIT_LENGTH": invalid identifier
>
> Elapsed: 00:00:00.14
> SQL>
>
> I know that the vast majority of these functions has well known
> equivalents in Oracle but I must say that I am curious about the reasons
> for refusing to go with the standard.
>
> --http://mgogala.byethost5.com
Date: Thu, 22 Apr 2010 07:43:56 -0700 (PDT)
Message-ID: <e50eb20d-9826-44ab-b8b8-2aa0d0443af3_at_z7g2000yqb.googlegroups.com>
On Apr 21, 10:52 pm, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> I am reading an old book about SQL standard, "A Guide to SQL Standard",
> by C.J. Date. On the page 87, the book lists some functions that are part
> of the SQL standard:
>
> Connected to:
> Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
> Production
> With the Partitioning, OLAP, Data Mining and Real Application Testing
> options
>
> SQL> select current_user from
> dual;
> select current_user from dual
> *
> ERROR at line 1:
> ORA-00904: "CURRENT_USER": invalid identifier
>
> Elapsed: 00:00:00.15
> SQL> select current_time() from
> dual;
> select current_time() from dual
> *
> ERROR at line 1:
> ORA-00904: "CURRENT_TIME": invalid identifier
>
> Elapsed: 00:00:00.13
> SQL> select session_user from
> dual;
> select session_user from dual
> *
> ERROR at line 1:
> ORA-00904: "SESSION_USER": invalid identifier
>
> Elapsed: 00:00:00.13
> SQL> select session_user() from
> dual;
> select session_user() from dual
> *
> ERROR at line 1:
> ORA-00904: "SESSION_USER": invalid identifier
>
> Elapsed: 00:00:00.14
> SQL> show
> session_user
> SP2-0735: unknown SHOW option beginning "session_us..."
> SQL> select bit_length('ABCD') from
> dual;
> select bit_length('ABCD') from dual
> *
> ERROR at line 1:
> ORA-00904: "BIT_LENGTH": invalid identifier
>
> Elapsed: 00:00:00.14
> SQL>
>
> I know that the vast majority of these functions has well known
> equivalents in Oracle but I must say that I am curious about the reasons
> for refusing to go with the standard.
>
> --http://mgogala.byethost5.com
I do not have a copy of the actual ANSI and ISO SQL standards handy to check but I do know that C J Date often uses his own version of SQL in his more recent books and not ANSI standard SQL.
I have read that no database verdor fully implements the ANSI standard for SQL. Obviously some parts of the standard are easier to implement than others for a specific database vendor and some standards were proposed and pushed by certain vendors to support their database engine.
It is also possible that some of these were proposed rther than adopted standards as I note that both Oracle 10gR2 and SQL Server 2008 support current_timestamp rather than current_time.
- Mark D Powell --