Skip Headers
Oracle® Database Lite SQL Reference
10g (10.0.0)
Part No. B13812-01
  Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

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 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 "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

Chapter 4, "SQL Commands" lists commands supported by Oracle Database Lite. Oracle Database Lite supports the additional types of commands listed in Table 1-4.

Table 1-4 SQL Commands Supported by Oracle Database Lite

Command Type Supported by Oracle Lite
Embedded SQL Commands WHENEVER
Commands used in embedded SQL DELETE, INSERT, SELECT, UPDATE

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-5.

Table 1-5 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

In Embedded SQL syntax, 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 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 A, "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-6.

Table 1-6 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-7.

Table 1-7 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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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 for Java.

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.

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.

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.

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.

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.

BNF Notation

 CAST "(" expr AS datatype_name ")"

For the operand, expr is a built-in datatype. Table 1-8 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-8 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 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 B, "Oracle Database Lite Datatypes". For information about literals, see Appendix C, "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-9 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-9 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