Oracle Compliance To Core SQL:2003

E011

Numeric data types

E031

Identifiers

E061

Basic predicates and search conditions

E081

Basic privileges

E091

Set functions

E101

Basic data manipulation

E111

Single row SELECT statement

E131

Null value support (nulls in lieu of values)

E141

Basic integrity constraints

E151

Transaction support

E152

Basic SET TRANSACTION statement

E153

Updatable queries with subqueries

E161

SQL comments using leading double minus

E171

SQLSTATE support

F041

Basic joined table

F051

Basic date and time

F081

UNION and EXCEPT in views

F131

Grouped operations

F181

Multiple module support

F201

CAST function

F221

Explicit defaults

F261

CASE expressions

F311

Schema definition statement

F471

Scalar subquery values

F481

Expanded NULL predicate

T631

IN predicate with one list element

E021, Character data types

Oracle fully supports these subfeatures:

• E021-01, CHARACTER data type

• E021-07, Character concatenation

• E021-08, UPPER and LOWER functions

• E021-09, TRIM function

• E021-10, Implicit casting among character data types

• E021-12, Character comparison

Oracle partially supports these subfeatures:

• E021-02, CHARACTER VARYING data type (Oracle does not distinguish a zero-length VARCHAR string from NULL)

• E021-03, Character literals (Oracle regards the zero-length literal '' as being null)

Oracle has equivalent functionality for these subfeatures:

• E021-04, CHARACTER_LENGTH function: use LENGTH function instead

• E021-05, OCTET_LENGTH function: use LENGTHB function instead

• E021-06, SUBSTRING function: use SUBSTR function instead

• E021-11, POSITION function: use INSTR function instead

E051, Basic query specification

Oracle fully supports the following subfeatures:

• E051-01, SELECT DISTINCT

• E051-02, GROUP BY clause

• E051-04, GROUP BY can contain columns not in <select list>

• E051-05, Select list items can be renamed

• E051-06, HAVING clause

• E051-07, Qualified * in select list

Oracle partially supports the following subfeatures:

• E051-08, Correlation names in FROM clause (Oracle supports correlation names, but not the optional AS keyword)

Oracle does not support the following subfeature:

• E051-09, Rename columns in the FROM clause

E071, Basic query expressions

Oracle fully supports the following subfeatures:

• E071-01, UNION DISTINCT table operator

• E071-02, UNION ALL able operator

• E071-05, Columns combined by table operators need not have exactly the same type

• E071-06, table operators in subqueries

Oracle has equivalent functionality for the following subfeature:

• E071-03, EXCEPT DISTINCT table operator: Use MINUS instead of EXCEPT DISTINCT

E121, Basic cursor support

Oracle fully supports the following subfeatures:

• E121-01, DECLARE CURSOR

• E121-02, ORDER BY columns need not be in select list

• E121-03, Value expressions in ORDER BY clause

• E121-04, OPEN statement

• E121-06, Positioned UPDATE statement

• E121-07, Positioned DELETE statement

• E121-08, CLOSE statement

• E121-10, FETCH statement, implicit NEXT

Oracle partially supports the following subfeatures:

• E121-17, WITH HOLD cursors (in the standard, a cursor is not held through a ROLLBACK, but Oracle does hold through ROLLBACK)

F031, Basic schema manipulation

Oracle fully supports these subfeatures:

• F031-01, CREATE TABLE statement to create persistent base tables

• F031-02, CREATE VIEW statement

• F031-03, GRANT statement

Oracle partially supports this subfeature:

• F031-04, ALTER TABLE statement: ADD COLUMN clause (Oracle does not support the optional keyword COLUMN in this syntax)

Oracle does not support these subfeatures (because Oracle does not support the keyword RESTRICT):

• F031-13, DROP TABLE statement: RESTRICT clause

• F031-16, DROP VIEW statement: RESTRICT clause

• F031-19, REVOKE statement: RESTRICT clause

F812, Basic flagging

Oracle has a flagger, but it flags SQL-92 compliance rather than SQL:2003 compliance

T321, Basic SQL-invoked routines

Oracle fully supports these subfeatures:

• T321-03, function invocation

• T321-04, CALL statement

Oracle supports these subfeatures with syntactic differences:

• T321-01, user-defined functions with no overloading

• T321-02, user-defined procedures with no overloading

The Oracle syntax for CREATE FUNCTION and CREATE PROCEDURE differs from the standard as follows:

• In the standard, the mode of a parameter (IN, OUT or INOUT) comes before the parameter name, whereas in Oracle it comes after the parameter name.

• The standard uses INOUT, whereas Oracle uses IN OUT.

• Oracle requires either IS or AS after the return type and before the definition of the routine body, while the standard lacks these keywords.

• If the routine body is in C (for example), then the standard uses the keywords LANGUAGE C EXTERNAL NAME to name the routine, whereas Oracle uses LANGUAGE C NAME.

• If the routine body is in SQL, then Oracle uses its proprietary procedural extension called PL/SQL.

Oracle supports the following subfeatures in PL/SQL but not in Oracle SQL:

• T321-05, RETURN statement

Oracle provides equivalent functionality for the following subfeatures:

• T321-06, ROUTINES view: Use the ALL PROCEDURES metadata view.

• T321-07, PARAMETERS view: Use the ALL_ARGUMENTS and ALL _METHOD_PARAMS metadata views.

F021, Basic information schema

Oracle does not have any of the views in this feature. However, Oracle makes the same information available in other metadata views:

• Instead of TABLES, use ALL_TABLES.

• Instead of COLUMNS, use ALL_TAB_COLUMNS.

• Instead of VIEWS, use ALL_VIEWS.

  However, Oracle's ALL_VIEWS does not display whether a user view was defined WITH CHECK OPTION or if it is updatable. To see whether a view has WITH CHECK OPTION, use ALL_CONSTRAINTS, with TABLE_NAME equal to the view name and look for CONSTRAINT_TYPE equal to 'V'.

• Instead of TABLE_CONSTRAINTS, REFERENTIAL_CONSTRAINTS and CHECK_CONSTRAINTS, use ALL_CONSTRAINTS.

  However, Oracle's ALL_CONSTRAINTS does not display whether a constraint is deferrable or initially deferred.

S011, Distinct types

Distinct types are strongly typed scalar types. A distinct type can be emulated in Oracle using an object type with only one attribute. The standard's Information Schema view called USER_DEFINED_TYPES is equivalent to Oracle's metadata view ALL_TYPES.

T695, Translation support

The Oracle CONVERT function can convert between many character sets. Oracle does not provide the ability to add or drop character set conversions.

F501

Features and conformance views