Skip Headers
Oracle® Database Lite SQL Reference
10g (10.3.0)

Part Number B28925-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

1 Using SQL

This document discusses how SQL is used with Oracle Database Lite. Topics include:

1.1 SQL Overview

Oracle Database Lite uses the SQL (Structured Query Language) database language to store and retrieve data. It includes the following categories of SQL statements:

1.1.1 Examples

This reference provides SQL statement examples. All examples are based on the default Oracle Database Lite objects.

1.1.2 Oracle SQL and SQL-92

Oracle Database Lite uses Oracle SQL as its default SQL language. Oracle SQL handles computation results and date data in a different manner than SQL-92. The differences between Oracle SQL and SQL-92 are listed in Table 1-1.

Table 1-1 Differences Between Oracle SQL and SQL-92

Oracle SQL SQL-92

Division yields a double precision result such as 3.333. For example 8/3 yields 2.666.

Division yields datatypes of operands such as 3. For example, 8/3 yields 2.

DATE datatype stores full timestamp information but only displays the date portion.

DATE datatype stores and displays date but no timestamp information.


Although Oracle Database Lite uses Oracle SQL, by default it supports several SQL-92 features including:

  • Column datatypes: TIME, TIMESTAMP, TINYINIT, and BIT

  • CASE expression

  • CAST expression

1.1.2.1 Running SQL-92 on Oracle Lite

As mentioned in the preceding section, Oracle Database Lite uses Oracle SQL by default. However, if you want to support SQL-92 by default instead of Oracle SQL, you can change the SQL compatibility parameter in the POLITE.INI file to SQL-92. To change the parameter, add the following in the POLITE.INI file.

SQLCOMPATIBILITY=SQL92

See Appendix G, "POLITE.INI Parameters" in the Oracle Database Lite Administration and Deployment Guide for more information about the POLITE.INI file.

1.2 Oracle Lite SQL and Oracle SQL Comparison

The SQL language supported by Oracle Database Lite is a subset of the SQL language supported by Oracle. Oracle Database Lite supports some additional SQL-92 database objects, functions, and commands.

1.2.1 Objects

The differences between database objects supported by Oracle Database Lite and those supported by Oracle are listed in Table 1-2. See Section 1.5, "Oracle Database Lite Database Object Naming Conventions" for more information:

Table 1-2 Differences Between Oracle Database Lite and Oracle-Supported Database Objects

Supported by Oracle Database Lite Supported by Oracle

Tables, views, indexes, sequences, schemas, snapshots.

All database objects.

A name identifier up to 128 characters for columns, indexes, tables, and schemas. User name identifiers can be up to 30 characters.

A name identifier up to 31 characters.


1.2.2 Operators

Chapter 2, "SQL Operators", lists the operators supported by Oracle Database Lite. In general, the Oracle Database Lite supports all operators supported by Oracle.

Except for datatype-related differences, the corresponding operators always work identically.

1.2.3 Functions

Chapter 3, "SQL Functions" lists the functions supported by Oracle Database Lite. The functions listed in Table 1-3 produce different results in Oracle and Oracle Database Lite.

Table 1-3 Function Behavior in Oracle Database Lite and Oracle

Function Supported by Oracle Lite Supported by Oracle

ROWID

16 characters long

18 characters long

TO_CHAR

does not accept 'nlsparams'

accepts 'nlsparams'

TO_DATE

does not accept 'nlsparams'

accepts 'nlsparams'

TO_NUMBER

does not accept 'nlsparams'

accepts 'nlsparams'


1.2.4 Commands

Some Oracle commands have a more limited functionality in Oracle Database Lite. The Oracle command parameters that are not supported by Oracle Database Lite are listed in Table 1-4.

Table 1-4 Oracle Command Parameters Not Supported by Oracle Database Lite

Command Element Unsupported by Oracle Lite

CREATE TABLE

Index clause for table and column constraints.

Exceptions into clauses for table and column constraints.

Physical organization clauses.

Deferred options for columns and tables.

CREATE TRIGGER

On Views

OR REPLACE

INSTEAD OF

REFERENCING OLD

REFERENCING NEW

WHEN

OR

ALTER TABLE

RENAME

ALTER INDEX

Rename index option.

Rebuild index option.

SET TRANSACTION

READ ONLY

READ WRITE

UPDATE

Set clause containing subqueries that select more than one column.

Returning clause where row IDs for updated rows are returned.

TO_CHAR

When used to extract timestamp from date value.


Note:

There may be differences in subqueries for Oracle and Oracle Database Lite.

Oracle Database Lite does not support the following commands and clauses.

  • Commands related to the following database objects.

    • Clusters

    • Database links

    • Stored functions and procedures other than Java stored procedures

    • Packages

    • Profiles

    • Rollback segments

    • Snapshot logs

    • Table spaces

  • Physical data storage clauses such as PCTFREE.

1.2.5 Miscellaneous Data Definition Language (DDL)

Oracle Database Lite does not support space management, table spaces, and INITRANS.

Oracle Database Lite DDL does not commit when executed as Oracle does, but commits as part of the current transaction.

1.2.6 Datatypes

Oracle Database Lite supports more datatypes than Oracle. For results similar to those of Oracle in Oracle Database Lite, use NUMBER and specify precision and scale.

Oracle anticipates datatypes to return and their display. It may produce results automatically, where Oracle Database Lite may need a specific CAST (one_datatype AS another_datatype) in the statement. You should avoid INT, FLOAT, and DOUBLE if you want portability between machine types. Oracle Database Lite uses the native implementations of these datatypes while Oracle maps these to specific NUMBER datatypes.

1.2.7 Indicator Variables

Oracle Database Lite uses 32-bit LONG indicator variables integers. Oracle uses, 16-bit SHORT indicator variables integers.

1.2.8 Data Precision During Arithmetic Operations

Oracle databases look at the datatype on the left side of an assignment when deciding how many decimal places of a result to store into a column. Oracle Database Lite follows SQL-92 convention, and only provides the maximum number of digits of precision from the right side of the assignment.

1.2.9 Data Dictionaries

The Oracle Database Lite data dictionary is different from the Oracle data dictionary. Oracle Database Lite provides many commonly used system views including ALL_TABLES and ALL_INDEXES.

1.2.10 Tables Not Installed with Oracle Database Lite

The table system.product_privs, which contains product user profiles in an Oracle database, does not exist in the Oracle Database Lite.

1.2.11 Messages

Oracle Database Lite may not generate the same messages that Oracle databases generate in response to SQL commands. The error codes may also be different. Applications should not depend on a specific error code or message text to recognize that an error has occurred.

1.2.12 Sequences

Oracle Database Lite does not support CYCLE and CACHE clauses in sequence statements. Sequence numbers are also subject to ROLLBACK under some circumstances.

1.2.13 PL/SQL

Oracle Database Lite does not support PL/SQL. However, Oracle Database Lite does support stored procedures and triggers written in Java.

1.2.14 SQL Functions

Oracle Database Lite does not support trigonometric functions, SOUNDEX, or bit operations.

1.2.15 Locking and Transactions

Oracle Database Lite begins a transaction with the first use of SELECT. In some isolation levels, the use of a SELECT on one connection can lock out an UPDATE of the same table on another connection. You may need to COMMIT after a SELECT to free the lock, so the UPDATE may proceed.

1.3 Oracle Database Lite SQL Conventions

When you issue a SQL statement, you can include one or more tabs, carriage returns, spaces, or comments anywhere a space occurs within the definition of the command. Oracle Database Lite SQL evaluates the following two statements in the same manner.

SELECT ENAME,SAL*12,MONTHS_BETWEEN(HIREDATE,SYSDATE) FROM EMP;

SELECT ENAME,
   SAL * 12,
      MONTHS_BETWEEN( HIREDATE, SYSDATE )
   FROM EMP;

Reserved words, keywords, identifiers and parameters are not case-sensitive. However, text literals and quoted names are case-sensitive. See the syntax descriptions in Chapter 3, "SQL Functions" and Chapter 4, "SQL Commands".

1.3.1 SQL Statement Syntax

SQL syntax definitions use the following conventions. SQL syntax definitions are always shown in monospace text.

1.3.1.1 Capital Letters

SELECT

Indicates literal text that must be entered as shown.

1.3.1.2 Lowercase

table_name

Indicates a place holder that should be replaced by an appropriate value or expression. Any additional delimiter that the replacement value or expression requires such as single quotes is shown.

1.3.1.3 Bracket Delimited

[PUBLIC] OR [MAXVALUE | NOMAXVALUE]

Indicates an optional item or clause. Multiple items or clauses are separated by vertical bars. Do not enter brackets or vertical bars.

1.3.1.4 Braces

{ENABLE | DISABLE | COMPILE}

Braces enclose two or more required alternative choices, separated by vertical bars. Do not enter braces or vertical bars.

1.3.1.5 Vertical Bars

{IDENTITY | NULL} OR  [MAXVALUE integer | NOMAXVALUE]

Vertical bars separate two or more choices, either required arguments enclosed in braces { } or optional arguments enclosed in brackets [ ]. Do not enter vertical bars, braces, or brackets.

1.3.1.6 Ellipsis

[, column] ...

Indicates that further repetitions of the argument expressed in the same format are permissible. Do not enter ellipses.

1.3.1.7 Underline

[ASC | DESC]

Indicates the default value used if you do not specify any of the options separated by vertical bars.

1.3.1.8 Block Letters

PCTFREE

Indicates a keyword that should be entered exactly as shown.

1.3.1.9 Initial Colon

: integer_value

Indicates a place holder that should be replaced by an appropriate reference to a host variable. You include the initial colon with the host variable reference.

1.3.2 SQL Tables

A database can be made up of one or more database files or catalogs in ODBC and SQL-92. The fundamental unit of storage in SQL is a table consisting of rows of data organized in columns. All database objects, including tables, views, and indexes, are owned by a user name or a schema. By default in Oracle Database Lite, tables are created as part of the user schema, the schema with the same name as the login ID.

1.3.3 SQL Object Names

Object names in SQL must begin with a letter and may contain numbers and the special characters "_" and "$". Names are generally not case-sensitive. Mixed case names are permitted when enclosed in double quotes (" ").

Object names may be qualified by the catalog (otherwise known as the database file name, which in Oracle Datatbase lite is the ODB file) and schema to which they belong by separating the qualifiers with a period ".". For example,

production.payroll.emp.salary

This example refers to the salary column of the emp table owned by the payroll schema in the production catalog.

1.3.4 SQL Operator Precedence

The following list describes the relative precedence of SQL operators. The operators at the top of the list have the highest precedence (they are evaluated first); the operators at the bottom of the list have the lowest precedence (they are evaluated last). Operators of equal precedence are evaluated from left to right.

  1. + (unary), -(unary), PRIOR

  2. *,/

  3. +, -, ||

  4. All comparison operators

  5. NOT

  6. AND

  7. OR

You can use parentheses in an expression to override operator precedence. Expressions inside parentheses are evaluated before those outside parentheses.

1.3.5 SQL Sessions

The execution of SQL statements requires the existence of a SQL session. An application can establish a SQL session by performing the following.

  • Issuing a SQL statement that requires a SQL session (a default session is implicitly established).

  • Issuing SQLConnect or SQLDriverConnect ODBC calls.

A SQL session is closed when one of the following occurs.

  • The SQLDisconnect API in ODBC is called.

  • An ODBC program terminates.

1.3.6 SQL Transactions

SQL databases handle requests in logical units of work called transactions. A transaction is a group of related operations that must be performed successfully before any changes to the database are finalized.

A SQL transaction starts when any DDL or DML statement is executed in a session. When you are satisfied that no errors occurred during the transaction, you can end the transaction with a COMMIT command. The database then changes to reflect the operation. If an error occurs, you can abandon the changes with the ROLLBACK command.

Oracle Database Lite does not commit a DDL statement until you issue the COMMIT command. Oracle immediately commits all DDL statements.

1.3.7 Issuing SQL Statements From a Program

Oracle Database Lite datatypes and object classes are interoperable with other programming languages. You can issue SQL statements to Oracle Database Lite in a host language if you connect to the database from within the application, using the appropriate ODBC or JDBC driver.

1.3.8 SQL and ODBC

The Open Database Connectivity (ODBC) interface from Microsoft defines a call level interface to provide interoperability across different databases. ODBC specifies a set of interface functions to allow the following features.

  • Connections to databases by different vendors.

  • Preparation and execution of SQL statements in a common language.

  • Retrieval of query results into local program variables.

Oracle Database Lite supports the ODBC 2.0 call level interface (CLI). Oracle Database Lite SQL supports implicit type conversion from the character string type to another datatype when necessary. For example, if the datatype of a column AGE is INTEGER, and you execute the following statement.

UPDATE EMPLOYEE SET AGE = '30' WHERE NAME = 'John'

'30' is automatically converted to an INTEGER type.

1.4 ODBC SQL Syntax Conventions

There are two principal reasons to use ODBC SQL syntax rather than the SQL syntax that is specific to your database.

First, SQL statements written in ODBC syntax are easily transferred among ODBC-compliant databases. Even though ODBC SQL syntax does not include many of the keywords and arguments that invoke important functionality for a specific database, SQL statements written in ODBC syntax are fully portable among all ODBC-compliant databases.

Second, you can use ODBC SQL syntax to execute SQL statements against databases that you are not familiar with. While ODBC SQL syntax cannot invoke your database's full functionality like your database's own SQL syntax, you can use it to perform many of the most common, and important, database functions.

You can always use database-specific SQL syntax, even when connected to a database through ODBC, since ODBC passes SQL statements through to a connected database without modification.

1.5 Oracle Database Lite Database Object Naming Conventions

This section lists rules for naming Oracle Database Lite database objects and their parts.

  1. User names must be from 1 to 30 characters long. Columns, indexes, tables, and schemas can be up to 128 characters long. Oracle Database Lite has no limit on name length, but it is recommended that you limit your name length to 30 characters.

  2. Names cannot contain quotation marks.

  3. Names are not case sensitive.

  4. A name must begin with an alphabetic character.

  5. Names can contain only alphanumeric characters and the characters _,$,and #. The use of $ and # is not recommended.

  6. A name cannot be an Oracle Database Lite reserved word.

  7. The word DUAL should not be used as a name for an object or part.

  8. The Oracle Database Lite SQL language contains other keywords that have special meanings. Because these keywords are not reserved, you can also use them as names for objects and object parts. However, using them as names may make your SQL statements more difficult to read. See Appendix C, "Oracle Database Lite Keywords and Reserved Words" for a list of Oracle Lite keywords.

  9. A name must be unique across its name space.

  10. A name can be enclosed in double quotes. Such names can contain any combination of characters, ignoring rules 3 through 7 in this list.

  11. Names cannot contain a dot (".") character.

1.6 Formats

The sections Number Format Elements and Date Format Elements list the elements you can use to create a valid number or date format. Formats can be used as arguments to the SQL functions: TO_DATE, TO_NUMBER, TO_CHAR, and TRUNC.

1.6.1 Number Format Elements

Oracle Database Lite number formats are listed in Table 1-5.

Table 1-5 Oracle Database Lite Number Formats

Element Example Description

9

9999

The number of nines specifies the number of significant digits returned. Blanks are returned for leading zeros and for a value of zero.

0

0000.00

Returns a leading zero or a value of zero as a 0, rather than as a blank.

$


$9999

Prefixes value with a dollar sign.

B

B9999

Returns zero value as blank, regardless of zeros in the format model.

MI

9999MI

Returns "-" after negative values. For positive values, a trailing space is returned.

S

S9999

Returns "+" for positive values and "-" for negative values.

PR

9999PR

Returns negative values in <angle brackets>. For positive values, a leading and trailing space are returned.

D

99D99

Returns the decimal character, separating the integral and fractional parts of a number.

G

9G999

Returns the group separator.

C

C999

Returns the ISO currency symbol.

L

L999

Returns the local currency symbol.

, (comma)

9,999

Returns a comma.

. (period)

99.99

Returns a period, separating the integral and fractional parts of a number.

EEEE

9.999EEEE

Returns a value in scientific notation.


1.6.2 Date Format Elements

Oracle Database Lite date formats are listed in Table 1-6.

Table 1-6 Oracle Database Lite Date Formats

Element Description

SCC or CC

Century; "S" prefixes BC dates with "-".

YYYY or SYYYY

4-digit year; "S" prefixes BC dates with "-".

IYYY

4-digit year based on the ISO standard.

YYY or YY or Y

Last 3, 2, or 1 digit(s) of year.

IYY or IY or I

Last 3, 2, or 1 digit(s) of the ISO year.

Y,YYY

Year with comma.

Q

Quarter of year (1, 2, 3, 4; JAN-MAR = 1)

MM

Month (01-12; JAN = 01)

MONTH

Name of month; padded with blanks to length of 9 characters.

MON

Abbreviated name of the month.

WW

Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.

IW

Week of year (1-52 or 1-53) based on the ISO standard.

W

Week of month (1-5) where week 1 starts on the first day of the year and continues to the seventh day of the year.

DDD

Day of year (1-366).

DD

Day of month (1-31).

D

Day of week (1-7).

DAY

Name of day, padded with blanks to length of 9 characters.

DY

Abbreviated name of day.

AM or PM

Meridian indicator.

A.M. or P.M.

Meridian indicator with periods.

HH or HH12

Hour of day (1-12).

HH24

Hour of day (0-23).

MI

Minute (0-59).

RR

Last 2 digits of year; for years in other countries.

SS

Second (0-59).

SSSSS

Seconds past midnight (0-86399).

- / . ; : "text"

Punctuation and quoted text is reproduced in the result.


1.7 Specifying SQL Conditions

Use one of the following syntax forms to specify a SQL condition. The syntax diagrams in this document use a variation of Backus-Nauer Form (BNF) notation. For a description of the convention used in this document, please see Section 4.2.6, "BNF Notation Conventions".

1.7.1 Simple Comparison Conditions

A simple comparison condition specifies a comparison with expressions or subquery results using the syntax displayed in Figure 1-1.

Figure 1-1 A SIMPLE COMPARISON Condition

Demonstrates simple comparisons, such as equals.
Description of "Figure 1-1 A SIMPLE COMPARISON Condition"

BNF Notation

{ expr { = | != | ^= | <> | > | < | >= | <= } { expr |"(" subquery")"}

For example,

SELECT * FROM EMP WHERE SAL > 2000;

For information on comparison operators, see Comparison Operators.

1.7.2 Group Comparison Conditions

A group comparison condition specifies a comparison with any or all members in a list or subquery using the syntax displayed in Figure 1-2.

Figure 1-2 A GROUP COMPARISON Condition

Demonstrates group comparisons, such as equals and ALL.
Description of "Figure 1-2 A GROUP COMPARISON Condition"

BNF Notation

{ expr    { = | != | ^= | <> | > | < | >= | <= }    { ANY | SOME | ALL }    {"(" subquery")"}| expr_list    { = | != }   { ANY | SOME | ALL }    { "(" subquery ")"}}

For example:

SELECT * FROM EMP WHERE ENAME = any ('SMITH', 'WARD', 'KING');

1.7.2.1 A Row_Value_Constructor in a Subquery Comparison

This allows the comparison of columns or expressions using a subquery that returns a multi-column result. This feature allows users to supply a row value constructor, such as a list of comma-separated expressions enclosed within parenthesis.

1.7.2.2 Subquery in Place of a Column

You may insert a subquery anywhere. An arithmetic expression or a column can appear. The subquery needs to be enclosed in parenthesis and is restricted to return a maximum of one row with one column.

For example,

  1. Subquery in a select list. The following query is supported (assuming c1 and c2 are columns in table t1 and c1 is a primary key).

    SELECT (select c1 from t1 b where a.c1 = b.c1), 
           c2 from t1 a where <condition>
    
    
    The select list of the subquery in a select list can itself contain a subquery. There is no limit to the number of nested subqueries.
  2. Subquery in an expression: The following query is supported (with the same assumption as example 1).

    SELECT *  from t1 a  where
          (select c1 from t1 where c1 = 10) =
          (select c1 from t1 b where a.c1 = b.c1) - 20;
    
    
  3. A subquery can contain Group By, Union, Minus, and Intersect, but not an Order By clause.

1.7.3 Membership Conditions

A membership condition tests for membership in a list or subquery using the syntax displayed in Figure 1-3.

Figure 1-3 A MEMBERSHIP Condition

Demonstrates membership comparisons, such as IN.
Description of "Figure 1-3 A MEMBERSHIP Condition"

BNF Notation

expr [NOT] IN { expr_list | "("subquery ")"}

For example,

SELECT * FROM EMP WHERE ENAME not in ('SMITH', 'WARD', 'KING');

1.7.4 Range Conditions

A range condition tests for inclusion in a range using the syntax displayed in Figure 1-4.

Figure 1-4 A RANGE Condition

Demonstrates range comparisons, such as BETWEEN.
Description of "Figure 1-4 A RANGE Condition"

BNF Notation

expr [ NOT ] BETWEEN expr AND expr ;

For example,

SELECT * FROM EMP WHERE SAL between 2000 and 50000;

1.7.5 NULL Conditions

A NULL condition tests for nulls using the syntax displayed in Figure 1-5.

Figure 1-5 A NULL Condition

Demonstrates the null condition.
Description of "Figure 1-5 A NULL Condition"

BNF Notation

expr IS [NOT]  NULL

For example:

SELECT * FROM EMP WHERE MGR IS NOT NULL;

1.7.6 EXISTS Conditions

An EXISTS condition tests for the existence of rows in a subquery using the syntax displayed in Figure 1-6.

Figure 1-6 An EXISTS Condition

Syntax diagram for the exists condition.
Description of "Figure 1-6 An EXISTS Condition"

BNF Notation

EXISTS "("subquery")"

For example,

SELECT * FROM EMP WHERE EXISTS (SELECT ENAME FROM EMP WHERE MGR IS NULL);

1.7.7 LIKE Conditions

A LIKE condition specifies a test involving pattern matching using the syntax displayed in Figure 1-7.

Figure 1-7 Like Conditions Syntax

SQL syntax for Like conditions
Description of "Figure 1-7 Like Conditions Syntax"

BNF Notation

char1 [NOT] LIKE  char2 [ESCAPE "'"esc_char"'" ]

For example,

SELECT * FROM EMP WHERE NAME like 'SM%"

1.7.8 Compound Conditions

A COMPOUND condition specifies a combination of other conditions using the syntax displayed in Figure 1-8.

Figure 1-8 A COMPOUND Condition

Syntax diagram for the compound condition.
Description of "Figure 1-8 A COMPOUND Condition"

BNF Notation

{ "(" condition ")"   | NOT  condition  | condition {AND | OR} condition};

For example,

SELECT * FROM EMP WHERE COMM IS NOT NULL AND SAL > 1500;

1.8 Specifying Expressions

Use one of the following syntax forms to specify a SQL expression.

1.8.1 Form I, Simple Expression

A simple expression specifies column, pseudocolumn, constant, sequence number, or null using the syntax displayed in Figure 1-9.

Figure 1-9 A SIMPLE Expression

Syntax diagram for a simple expression.
Description of "Figure 1-9 A SIMPLE Expression"

BNF Notation

{ [schema .] { table | view } "." { column | pseudocolumn }| text | catalog "." schema "." { table| view } "." { column | pseudocolumn }| number| sequence "." { CURRVAL | NEXTVAL }| NULL}

In addition to the schema of a user, schema can also be PUBLIC (double quotation marks required), in which case it must qualify a public synonym for a table, view, or materialized view. Qualifying a public synonym with PUBLIC is supported only in Data Manipulation Language (DML) statements, not Data Definition Language (DDL) statements.

The pseudocolumn can be either LEVEL, ROWID, or ROWNUM. You can use a pseudocolumn only with a table, not with a view or materialized view.

Examples

emp-ename
'this is a text string'
10

1.8.2 Form II, Function Expression

A built-in function expression specifies a call to a single-row SQL function using the syntax displayed in Figure 1-10.

Figure 1-10 A FUNCTION Expression

Syntax diagram for a function expression.
Description of "Figure 1-10 A FUNCTION Expression"

BNF Notation

function ["(" [DISTINCT | ALL] expr [, expr]...")"] ;

Some valid built-in function expressions are:

LENGTH('BLAKE')
ROUND(1234.567*43)
SYSDATE

1.8.3 Form III, Java Function Expression

java_function_name (expr , expr...)
schema.table.java_function_name (expr , expr...)

For information on how to use Java functions, see the Oracle Database Lite Developer's Guide.

1.8.4 Form IV, Compound Expression

A compound expression specifies a combination of other expressions using the syntax displayed in Figure 1-11.

Figure 1-11 A COMPOUND Expression

Syntax diagram for a compound expression.
Description of "Figure 1-11 A COMPOUND Expression"

BNF Notation

{ "(" expr ")"   | { + | - } expr  | PRIOR column  | expr( * | / | + | - | ||) expr };

Some combinations of functions are inappropriate and are rejected. For example, the LENGTH function is inappropriate within an aggregate function.

Examples

('CLARK' || 'SMITH')
LENGTH('MOOSE') * 57
SQRT(144) + 72
my_fun(TO_CHAR(sysdate,'DD-MM-YY'))

1.8.5 Form V, DECODE Expression

A DECODE expression uses the special DECODE syntax displayed in Figure 1-12.

Figure 1-12 The DECODE Expression

Syntax diagram for the decode expression.
Description of "Figure 1-12 The DECODE Expression"

BNF Notation

DECODE "(" expr "," search "," result [, search "," result]... [, default] ")" ;

To evaluate this expression, Oracle Database Lite compares expr to each search value one by one. If expr is equal to a search, Oracle Database Lite returns the corresponding result. If no match is found, Oracle Database Lite returns default, or, if default is omitted, returns null. If expr and search contain character data, Oracle Database Lite compares them using non-padded comparison semantics.

The search, result, and default values can be derived from expressions. Oracle Database Lite evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, Oracle Database Lite never evaluates a search if a previous search is equal to expr.

Oracle Database Lite automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle Database Lite automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle Database Lite converts the return value to the datatype VARCHAR2.

In a DECODE expression, Oracle Database Lite considers two nulls to be equivalent. If expr is null, Oracle Database Lite returns the result of the first search that is also null. The maximum number of components in the DECODE expression, including expr, searches, results, and default is 255.

Example

This expression decodes the value DEPTNO. In this example, if DEPTNO is 10, the expression evaluates to 'ACCOUNTING'. If DEPTNO is not 10, 20, 30, or 40, the expression returns 'NONE'.

DECODE (deptno,10, 'ACCOUNTING',
               20, 'RESEARCH',
               30, 'SALES',
               40, 'OPERATION',
                   'NONE')

1.8.6 Form VI, Expression List

An EXPRESSION LIST is a series of expressions, each separated by a comma as displayed in Figure 1-13. The entire series is enclosed in parenthesis.

Figure 1-13 The EXPRESSION List

Syntax diagram for the expression list.
Description of "Figure 1-13 The EXPRESSION List"

BNF Notation

"("[ expr [, expr]...] ")"

1.8.7 Form VII, Variable Expression

A VARIABLE EXPRESSION specifies a host variable with an optional indicator variable as displayed in Figure 1-14. This form of expression can appear in a programmatic programming interface.

Figure 1-14 The VARIABLE Expression

Syntax diagram for the variable expression.
Description of "Figure 1-14 The VARIABLE Expression"

BNF Notation

":" host_variable [[INDICATOR] ":" indicator_variable]

1.8.8 Form VIII, CAST Expression

A CAST expression converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value as displayed in Figure 1-15.

Figure 1-15 The CAST Expression

Syntax diagram for the cast expression.
Description of "Figure 1-15 The CAST Expression"

BNF Notation

CAST "(" expr AS datatype_name ")"

For the operand, expr is a built-in datatype. Table 1-7 shows which built-in datatypes accept CAST conversion to another datatype. (CAST does not support LONG, LONG RAW, or any of the LOB datatypes.)

Table 1-7 Built-In Datatypes that Accept the CAST Conversion

From/ To Char, Varchar2 Numeric Date Time Timestamp Raw

Char, Varchar2

X

X

X

X

X

X

Numeric

X

X





Date

X


X


X


Time

X



X

X


Timestamp

X


X

X

X


Raw

X





X


The Date datatype is affected by the SQLCompatibility setting defined in the POLITE.INI file.

  • Date and Timestamp are equivalent if you have set: SQLCompatibility=Oracle

  • Date and Timestamp are not equivalent if you have set: SQLCompatibility=SQL92

See the POLITE.INI Parameters Appendix in the Oracle Database Lite Administration and Deployment Guide for more information about the POLITE.INI file.

The numeric category includes the following datatypes: BIGINT, BINARY, BIT, DECIMAL, DOUBLE PRECISION, FLOAT, INTEGER, NUMBER, NUMERIC, REAL, SMALLINT, and TINYINT.

Built-In Datatype Examples 

SELECT CAST ('1997-10-22' AS DATE) FROM DUAL;
SELECT * FROM t1 WHERE CAST (ROWID AS CHAR(5)) = '01234';

1.9 Oracle Database Lite SQL Datatypes and Literals

For a complete list of Oracle Database Lite SQL datatypes, see Appendix E, "Oracle Database Lite Datatypes". For information about literals, see Appendix D, "Oracle Database Lite Literals".

1.9.1 Character String Comparison Rules

Oracle Database Lite compares character string values using one of these comparison rules:

  • blank-padded comparison semantics

  • non-padded comparison semantics

The following sections explain these comparison semantics. The results of comparing two character values using different comparison semantics may vary. Table 1-8 lists the results of comparing five pairs of character values using each comparison semantic. Generally, the results of blank-padded and non-padded comparisons are the same. The last comparison in the table illustrates the differences between the blank-padded and non-padded comparison semantics.

Table 1-8 Comparison of Blank-Padded and Non-Padded Comparison Semantics

Blank-Padded Non-Padded

'ab' > 'aa'

'ab' > 'aa'

'ab' > 'a '

'ab' > 'a   '

'ab' > 'a'

'ab' > 'a'

'ab' = 'ab'

'ab' = 'ab'

'a ' = 'a'

'a ' > 'a'


1.9.1.1 Blank-Padded Comparison Semantics

If the two values have different lengths, Oracle Database Lite first adds blanks to the end of the shorter one so that their lengths are equal. Oracle Database Lite then compares the values character by character up to the first character that differs. The value with the greater than character (>) in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle Database Lite uses blank-padded comparison semantics only when both values in the comparison are either expressions of the datatype CHAR, text literals, or values returned by the USER and DATABASE functions.

1.9.1.2 Non-Padded Comparison Semantics

Oracle Database Lite compares two values character by character up to the first character that differs. The value with the greater than character (>) in that position is considered greater. If two values of different length are identical up to the end of the shorter one, the longer value is considered greater. If two values of equal length have no differing characters, then the values are considered equal. Oracle Database Lite uses non-padded comparison semantics whenever one or both values in the comparison have the datatype VARCHAR2. As a result, when comparing a CHAR value with a VARCHAR2 value, Oracle Database Lite considers the character value 'a ' unequal to 'a'.

1.10 Comments Within SQL Statements

You can associate comments with SQL statements and schema objects. Comments within SQL statements do not affect the statement execution, but they can make your application easier to read and maintain.

A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement using one of the following options.

A SQL statement can contain multiple comments of both styles. The text of a comment can contain any printable characters in your database character set.

Example 1

SELECT * FROM EMP WHERE EMP.DEPTNO = /* The subquery matches values in EMP.DEPTNO with values in DEPT.DEPTNO */ (SELECT DEPTNO FROM DEPT WHERE LOC='DALLAS');

This statement returns the following output.

EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM    DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
     7566 JONES      MANAGER        7839 1981-04-0      2975                  20
     7902 FORD       ANALYST        7566 1981-12-0      3000                  20
     7369 SMITH      CLERK          7902 1980-12-1       800                  20
     7788 SCOTT      ANALYST        7566 1982-12-0      3000                  20
     7876 ADAMS      CLERK          7788 1983-01-1      1100                  20

Example 2

SELECT ENAME, -- select the employee name
 SAL          -- and the salary
 FROM EMP     -- from the EMP table
 WHERE SAL    -- where the salary
 >=           -- is greater than or equal to
 3000         -- 3000
 ;

This statement returns the following output:

ENAME            SAL
---------- ---------
KING            5000
FORD            3000
SCOTT           3000

1.11 Tuning SQL Statement Execution Performance With the EXPLAIN PLAN

To execute a SQL statement, Oracle might need to perform several operations. The combination of the operations Oracle uses to execute a statement is called an execution plan, which includes an access path for each table that the statement accesses and an ordering of the tables (the join order) with the appropriate join method. The execution plan shows you exactly how Oracle Database Lite executes your SQL statement.

The components of an execution plan include the following:

The EXPLAIN PLAN command stores the execution plan chosen by the Oracle Database Lite optimizer for SELECT, UPDATE, INSERT, and DELETE statements into the table—PLAN_TABLE. Before using the EXPLAIN PLAN statement, a user creates the plan table using an interactive query tool, such as msql.

You can examine the execution plan chosen by the optimizer for a SQL statement by using the EXPLAIN PLAN statement. When the statement is issued, the optimizer chooses an execution plan and then inserts data describing the plan into a database table. Simply issue the EXPLAIN PLAN statement and then query the output table.

The EXPLAIN PLAN output shows how Oracle executes SQL statements, which helps a developer or DBA understand how a query is being executed. Thus, you can identify additional indexes needed, or how best to modify the query. The query modification may involve a re-write or use of optimizer hints to change the join order.

  1. Use the SQL script—utlxplan.sql—to create the sample output table called PLAN_TABLE in your schema. Alternatively, you can use msql to create the plan table. See Section 1.11.1, "The PLAN Table".

  2. Include the EXPLAIN PLAN FOR clause prior to the SQL statement. The syntax is as follows:

    Explain_plan_statement ::= EXPLAIN PLAN [SET STATEMENT_ID = 'text' ] [INTO [schema.] plan_table] [FOR] statement;
    
    

    Where

    • statement is any SELECT, UPDATE, INSERT, DELETE statement

    • 'text' is a literal provided by the user to identify all rows for the given query.

    • schema.plan_table is the table where you want the result to be stored. The table must conform to the layout given in the utlxplan.sql script. The default value for is PLAN_TABLE in your own schema.

  3. After issuing the EXPLAIN PLAN statement, query the PLAN_TABLE for the output.

The EXPLAIN PLAN command is not unique to Oracle Database Lite. It is a feature of the Oracle database. However, not all SQL operations supported in the Oracle database are supported by Oracle Database Lite. This section shows the operation subset that you can use in Oracle Database Lite.

In addition, this section does not go into full details on how the EXPLAIN PLAN works. For a full description of the EXPLAIN PLAN, see Section 1.4, "Determining Performance of Client SQL Queries With Explain Plan" in the Oracle Database Lite Troubleshooting and Tuning Guide.

1.11.1 The PLAN Table

The PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans. See Table 1-9 for a description of the columns in the table.

Use the SQL script utlxplan.sql to manually create a local PLAN_TABLE in your schema.

Table 1-9 Plan Table

Column Data Type Description

statement_id

Varchar2(30)

User specified ID

Timestamp

Date

Date and time of creation

Remarks

Varchar2(80)

A user-specified remarks

Operation

Varchar2(30)

The name of operation, such as SELECT, INSERT, UPDATE, DELETE, TABLE ACCESS and so on. See the Operations table for more information.

Options

Varchar2(30)

Qualification for the operation

object_owner

Varchar2(30)

Owner of a table or index

object_name

Integer

Name of the table or index

Id

Integer

Step identification number

parent_id

Integer

Parent step number

Position

Integer

Order of processing among the steps that have the same parent step id

Cost

Integer

Estimated cost in number of I/Os.

Cardinality

Integer

The estimated number of rows produced

Text

Varchar2(4096)

First 4096 bytes of the statement text stored with the first step of execution. For example, id=0


Table 1-10 lists each combination of Operation and Option produced by the EXPLAIN PLAN statement and its meaning within an execution plan.

Table 1-10 Operation and Option Values Produced by the EXPLAIN PLAN

Operation Options Comments

CONNECT BY


Retrieves rows in hierarchical order for a query containing a CONNECT BY clause.

FILTER

None

Operation accepting a set of rows, eliminates some of them, and returns the rest.

FOR UPDATE

None

Operation retrieving and locking the rows selected by a query containing a FOR UPDATE clause.

INDEX


Retrieval of one or more rowids from an index.

NESTED LOOP


Operation accepting two sets of rows, an outer set and an inner set. Oracle compares each row of the outer set with each row of the inner set, returning rows that satisfy a condition.

SORT

AGGREGATE, UNIQUE, GROUP BY, ORDER BY

A sort is being performed for aggregation, duplicate removal, group by or order by operations respectively.

TABLE ACCESS

FULL

All data pages of the table will be scanned.

TABLE ACCESS

BY INDEX ROWID, BY ROWID

The table rows are accessed using rowids from an index, or provided by some other means.

UNION ALL


A UNION ALL operation is being performed.

VIEW


A logical or physical view is being materialized.

CREATE TEMP TABLE

ORDER BY, READ COMMITTED, GROUP BY, UNION, CONNECT BY, MINUS, AGGREGATE

Option indicates the reason for creating the temporary table.

INSERT


An INSERT operation is being performed.

UPDATE


An UPDATE operation is being performed.

DELETE


A DELETE operation is being performed.

SELECT


A SELECT operation is being performed.

MINUS


A MINUS operation is being performed.


1.11.2 EXPLAIN PLAN Examples

The following examples demonstrate the EXPLAIN PLAN statement. The output for each example should only be used as a guideline. The actual output is subject to change based on the analysis of internal data structures. The examples are based on a sample schema, as follows:

Sample Schema

drop table s;
drop table sp;
drop table p;
drop table j;
drop table spj;
create table S ( S# char(3), SNAME Char(10), Status Int, City char(10));
create table P (P# char(3), PNAME Char(10), Color Char(10), Weight Int, City Char(10));
create table SP (S# char(3), P# Char(3), Qty Int);create table J ( J# char(3), JNAME Char(10), City char(10));create table SPJ (S# char(3), P# Char(3), J# Char(3), Qty Int);
insert into S values ('S1', 'Smith', 20, 'London');insert into S values ('S2', 'Jones', 10, 'Paris');
insert into S values ('S3', 'Blake', 30, 'Paris');insert into S values ('S4', 'Clark', 20, 'London');
insert into S values ('S5', 'Adams', 30, 'Athens');insert into P values ('P1', 'Nut', 'Red', 12, 'London');
insert into P values ('P2', 'Bolt', 'Green', 17, 'Paris');
insert into P values ('P3', 'Screw', 'Blue', 17, 'Rome');
insert into P values ('P4', 'Screw', 'Red', 14, 'London');
insert into P values ('P5', 'Cam', 'Blue', 12, 'Paris');
insert into P values ('P6', 'Cog', 'Red', 19, 'London');
insert into J values ('J1', 'Sorter', 'Paris');insert into J values ('J2', 'Punch', 'Rome');insert into J values ('J3', 'Reader', 'Athens');
insert into J values ('J4', 'Console', 'Athens');
insert into J values ('J5', 'Collator', 'London');
insert into J values ('J6', 'Terminal', 'Oslo');
insert into J values ('J7', 'Tape', 'London');
insert into SP values ('S1', 'P1', 300);
insert into SP values ('S1', 'P2', 200);
insert into SP values ('S1', 'P3', 400);
insert into SP values ('S1', 'P4', 200);
insert into SP values ('S1', 'P5', 100);
insert into SP values ('S1', 'P6', 100);
insert into SP values ('S2', 'P1', 300);
insert into SP values ('S2', 'P2', 400);
insert into SP values ('S3', 'P2', 200);
insert into SP values ('S4', 'P2', 200);
insert into SP values ('S4', 'P4', 300);
insert into SP values ('S4', 'P5', 400);
insert into SPJ values ('S1', 'P1', 'J1', 200);
insert into SPJ values ('S1', 'P1', 'J4', 700);
insert into SPJ values ('S2', 'P3', 'J1', 400);
insert into SPJ values ('S2', 'P3', 'J2', 200);
insert into SPJ values ('S2', 'P3', 'J3', 200);
insert into SPJ values ('S2', 'P3', 'J4', 500);
insert into SPJ values ('S2', 'P3', 'J5', 600);
insert into SPJ values ('S2', 'P3', 'J6', 400);
insert into SPJ values ('S2', 'P3', 'J7', 800);
insert into SPJ values ('S2', 'P5', 'J5', 100);
insert into SPJ values ('S3', 'P3', 'J1', 200);
insert into SPJ values ('S3', 'P4', 'J2', 500);
insert into SPJ values ('S4', 'P6', 'J3', 300);
insert into SPJ values ('S5', 'P2', 'J2', 200);
commit;
create unique index SIX1 on S ( S# );
create unique index PIX1 on P ( P# );
create unique index SPIX1 on SP ( S#, P# );
create unique index SPJIX1 on SPJ ( S#, P#, J# );
create index PCOLOR on P(Color);

The following examples demonstrate three examples of the output for the EXPLAIN PLAN for specific select statements:

1.11.2.1 Example for Select Distinct and Group By

The following is an example query and corresponding output from the EXPLAIN PLAN for a select statement where select distinct P# from SPJ group by p#,j# having avg(qty) > 320;

Table 1-11 Sample Output

ID POSITION PARENT_ID OPERATION OPTIONS OBJNAME

0



SORT

ORDER BY


1

1

0

CREATE TEMP TABLE

ORDER BY


2

1

1

SELECT



3

1

2

FILTER



4

1

3

CREATE TEMP TABLE

GROUP BY


5

1

4

TABLE ACCESS

FULL

SPJ


1.11.2.2 Example for Select Statement with Union

The following is an example query and corresponding output from the EXPLAIN PLAN for a select statement where select s.* from S, SP where s.s#=Sp.P# and status > 20 and qty > 40 union Select s.* from s,sp, p where s.s#=sp.s# and sp.p#=p.p# and p.color='Red';

Table 1-12

ID POSITION PARENT_ID OPERATION OPTIONS OBJNAME

0



SORT

ORDER BY


1

1

0

CREATE TEMP TABLE

ORDER BY


2

1

1

UNION ALL



3

1

2

SELECT



4

1

3

FILTER



5

1

4

NESTED LOOP



6

1

5

TABLE ACCESS

FULL

S

7

2

5

TABLE ACCESS

FULL

SP

8

2

2

SELECT



9

1

8

FILTER



10

1

9

NESTED LOOP



11

1

10

NESTED LOOP



12

1

11

TABLE ACCESS

BY INDEX ROWID

P

13

1

12

INDEX


PCOLOR

14

2

11

TABLE ACCESS

FULL

SP

15

2

10

TABLE ACCESS

BY INDEX ROWID

S

16

1

15

INDEX


SIX1


1.11.2.3 Example for Select Statement With Multiple Qualifiers

The following is an example query and corresponding output from the EXPLAIN PLAN for a select statement where select s.* from s, sp where s.s# = sp.s# and status > 20 and city in (select city from j where j# = 'J1' or j# = 'J2');

Table 1-13

ID POSITION PARENT_ID OPERATION OPTIONS OBJECT_NAME

0



SELECT



1

1

0

FILTER



2

1

1

NESTED LOOP



3

1

2

TABLE ACCESS

FULL

S

4

2

2

TABLE ACCESS

BY INDEX ROWID

SP

5

1

4

INDEX


SPIX1

6

2

1

SELECT



7

1

6

FILTER



8

1

7

TABLE ACCESS

FULL

J