Contents

Title and Copyright Information

Preface

• Syntax Descriptions
• Documentation Accessibility
• Send Us Your Comments

1 Using SQL

• 1.1 SQL Overview
  • 1.1.1 Examples
  • 1.1.2 Oracle SQL and SQL-92
    • 1.1.2.1 Running SQL-92 on Oracle Lite
• 1.2 Oracle Lite SQL and Oracle SQL Comparison
  • 1.2.1 Objects
  • 1.2.2 Operators
  • 1.2.3 Functions
  • 1.2.4 Commands
  • 1.2.5 Miscellaneous Data Definition Language (DDL)
  • 1.2.6 Datatypes
  • 1.2.7 Indicator Variables
  • 1.2.8 Data Precision During Arithmetic Operations
  • 1.2.9 Data Dictionaries
  • 1.2.10 Tables Not Installed with Oracle Database Lite
  • 1.2.11 Messages
  • 1.2.12 Sequences
  • 1.2.13 PL/SQL
  • 1.2.14 SQL Functions
  • 1.2.15 Locking and Transactions
• 1.3 Oracle Database Lite SQL Conventions
  • 1.3.1 SQL Statement Syntax
    • 1.3.1.1 Capital Letters
    • 1.3.1.2 Lowercase
    • 1.3.1.3 Bracket Delimited
    • 1.3.1.4 Braces
    • 1.3.1.5 Vertical Bars
    • 1.3.1.6 Ellipsis
    • 1.3.1.7 Underline
    • 1.3.1.8 Block Letters
    • 1.3.1.9 Initial Colon
  • 1.3.2 SQL Tables
  • 1.3.3 SQL Object Names
  • 1.3.4 SQL Operator Precedence
  • 1.3.5 SQL Sessions
  • 1.3.6 SQL Transactions
  • 1.3.7 Issuing SQL Statements From a Program
  • 1.3.8 SQL and ODBC
• 1.4 ODBC SQL Syntax Conventions
• 1.5 Oracle Database Lite Database Object Naming Conventions
• 1.6 Formats
  • 1.6.1 Number Format Elements
  • 1.6.2 Date Format Elements
• 1.7 Specifying SQL Conditions
  • 1.7.1 Simple Comparison Conditions
  • 1.7.2 Group Comparison Conditions
    • 1.7.2.1 A Row_Value_Constructor in a Subquery Comparison
    • 1.7.2.2 Subquery in Place of a Column
  • 1.7.3 Membership Conditions
  • 1.7.4 Range Conditions
  • 1.7.5 NULL Conditions
  • 1.7.6 EXISTS Conditions
  • 1.7.7 LIKE Conditions
  • 1.7.8 Compound Conditions
• 1.8 Specifying Expressions
  • 1.8.1 Form I, Simple Expression
  • 1.8.2 Form II, Function Expression
  • 1.8.3 Form III, Java Function Expression
  • 1.8.4 Form IV, Compound Expression
  • 1.8.5 Form V, DECODE Expression
  • 1.8.6 Form VI, Expression List
  • 1.8.7 Form VII, Variable Expression
  • 1.8.8 Form VIII, CAST Expression
• 1.9 Oracle Database Lite SQL Datatypes and Literals
  • 1.9.1 Character String Comparison Rules
    • 1.9.1.1 Blank-Padded Comparison Semantics
    • 1.9.1.2 Non-Padded Comparison Semantics
• 1.10 Database Constraints
• 1.11 Comments Within SQL Statements
• 1.12 Tuning SQL Statement Execution Performance With the EXPLAIN PLAN
  • 1.12.1 The PLAN Table
  • 1.12.2 EXPLAIN PLAN Examples
    • 1.12.2.1 Example for Select Distinct and Group By
    • 1.12.2.2 Example for Select Statement with Union
    • 1.12.2.3 Example for Select Statement With Multiple Qualifiers

2 SQL Operators

• 2.1 SQL Operators Overview
  • 2.1.1 Unary Operators
  • 2.1.2 Binary Operators
  • 2.1.3 Set Operators
  • 2.1.4 Other Operators
• 2.2 Arithmetic Operators
• 2.3 Character Operators
  • 2.3.1 Concatenating Character Strings
• 2.4 Comparison Operators
• 2.5 Logical Operators
• 2.6 Set Operators
• 2.7 Other Operators

3 SQL Functions

• 3.1 SQL Function Types
• 3.2 SQL Functions Overview
  • 3.2.1 Number Functions
  • 3.2.2 Character Functions
  • 3.2.3 Character Functions Returning Number Values
  • 3.2.4 Date Functions
  • 3.2.5 Conversion Functions
• 3.3 SQL Functions Alphabetical Listing
  • 3.3.1 ADD_MONTHS
  • 3.3.2 ASCII
  • 3.3.3 AVG
  • 3.3.4 CASE
  • 3.3.5 CAST
  • 3.3.6 CEIL
  • 3.3.7 CHR
  • 3.3.8 CONCAT
  • 3.3.9 CONVERT
  • 3.3.10 COUNT
  • 3.3.11 CURDATE
  • 3.3.12 CURRENT_DATE
  • 3.3.13 CURRENT_TIME
  • 3.3.14 CURRENT_TIMESTAMP
  • 3.3.15 CURTIME
  • 3.3.16 DATABASE
  • 3.3.17 DAYNAME
  • 3.3.18 DAYOFMONTH
  • 3.3.19 DAYOFWEEK
  • 3.3.20 DAYOFYEAR
  • 3.3.21 DECODE
  • 3.3.22 EXTRACT
  • 3.3.23 FLOOR
  • 3.3.24 GREATEST
  • 3.3.25 HOUR
  • 3.3.26 INITCAP
  • 3.3.27 INSTR
  • 3.3.28 INSTRB
  • 3.3.29 INTERVAL
  • 3.3.30 LAST_DAY
  • 3.3.31 LEAST
  • 3.3.32 LENGTH
  • 3.3.33 LENGTHB
  • 3.3.34 LOCATE
  • 3.3.35 LOWER
  • 3.3.36 LPAD
  • 3.3.37 LTRIM
  • 3.3.38 MAX
  • 3.3.39 MIN
  • 3.3.40 MINUTE
  • 3.3.41 MOD
  • 3.3.42 MONTH
  • 3.3.43 MONTHNAME
  • 3.3.44 MONTHS_BETWEEN
  • 3.3.45 NEXT_DAY
  • 3.3.46 NOW
  • 3.3.47 NVL
  • 3.3.48 POSITION
  • 3.3.49 QUARTER
  • 3.3.50 REPLACE
  • 3.3.51 ROUND - Date Function
  • 3.3.52 ROUND - Number Function
  • 3.3.53 RPAD
  • 3.3.54 RTRIM
  • 3.3.55 SECOND
  • 3.3.56 STDDEV
  • 3.3.57 SUBSTR
  • 3.3.58 SUBSTRB
  • 3.3.59 SUM
  • 3.3.60 SYSDATE
  • 3.3.61 TIMESTAMPADD
  • 3.3.62 TIMESTAMPDIFF
  • 3.3.63 TO_CHAR
  • 3.3.64 TO_DATE
  • 3.3.65 TO_NUMBER
  • 3.3.66 TRANSLATE
  • 3.3.67 TRIM
  • 3.3.68 TRUNC
  • 3.3.69 UPPER
  • 3.3.70 USER
  • 3.3.71 VARIANCE
  • 3.3.72 WEEK
  • 3.3.73 YEAR

4 SQL Commands

• 4.1 SQL Command Types
• 4.2 SQL Commands Overview
  • 4.2.1 Data Definition Language (DDL) Commands
  • 4.2.2 Data Manipulation Language (DML) Commands
  • 4.2.3 Transaction Control Commands
  • 4.2.4 Clauses
  • 4.2.5 Pseudocolumns
  • 4.2.6 BNF Notation Conventions
• 4.3 SQL Commands Alphabetical Listing
  • 4.3.1 ALTER SEQUENCE
  • 4.3.2 ALTER SESSION
  • 4.3.3 ALTER TABLE
  • 4.3.4 ALTER TRIGGER
  • 4.3.5 ALTER USER
  • 4.3.6 ALTER VIEW
  • 4.3.7 COMMIT
  • 4.3.8 CONSTRAINT clause
  • 4.3.9 CREATE DATABASE
  • 4.3.10 CREATE FUNCTION
  • 4.3.11 CREATE GLOBAL TEMPORARY TABLE
  • 4.3.12 CREATE INDEX
  • 4.3.13 CREATE JAVA
  • 4.3.14 CREATE PROCEDURE
  • 4.3.15 CREATE SCHEMA
  • 4.3.16 CREATE SEQUENCE
  • 4.3.17 CREATE SYNONYM
  • 4.3.18 CREATE TABLE
  • 4.3.19 CREATE SNAPSHOT TABLE
    • 4.3.19.1 Example Creating Snapshot Table
    • 4.3.19.2 Using the OL__ROW_STATUS Pseudocolumn to Select Modified Rows
    • 4.3.19.3 Retrieving Dirty Rows Using The Plus Sign ('+') on the Snapshot Table
    • 4.3.19.4 Reset the Snapshot Table With the RESET SNAPSHOT TABLE Command
  • 4.3.20 CREATE TRIGGER
  • 4.3.21 CREATE USER
  • 4.3.22 CREATE VIEW
  • 4.3.23 CURRVAL and NEXTVAL pseudocolumns
  • 4.3.24 DELETE
  • 4.3.25 DROP clause
  • 4.3.26 DROP FUNCTION
  • 4.3.27 DROP INDEX
  • 4.3.28 DROP JAVA
  • 4.3.29 DROP PROCEDURE
  • 4.3.30 DROP SCHEMA
  • 4.3.31 DROP SEQUENCE
  • 4.3.32 DROP SYNONYM
  • 4.3.33 DROP TABLE
  • 4.3.34 DROP TRIGGER
  • 4.3.35 DROP USER
  • 4.3.36 DROP VIEW
  • 4.3.37 EXPLAIN PLAN
  • 4.3.38 GRANT
  • 4.3.39 INSERT
  • 4.3.40 LEVEL pseudocolumn
  • 4.3.41 REVOKE
  • 4.3.42 ROLLBACK
  • 4.3.43 ROWID pseudocolumn
  • 4.3.44 ROWNUM pseudocolumn
  • 4.3.45 SAVEPOINT
  • 4.3.46 SELECT
    • 4.3.46.1 SELECT Command Arguments
    • 4.3.46.2 The SUBQUERY Expression
    • 4.3.46.3 The FOR_UPDATE Clause
    • 4.3.46.4 The ORDER_BY Clause
    • 4.3.46.5 The TABLE_REFERENCE Expression
    • 4.3.46.6 The ODBC_JOIN_TABLE Expression
    • 4.3.46.7 The HINT Expression
    • 4.3.46.8 The LIMIT and OFFSET Clauses
    • 4.3.46.9 Select Statement Behavior When Retrieving BLOBs in a Read Committed Transaction
    • 4.3.46.10 Examples For the SELECT Command
  • 4.3.47 SET TRANSACTION
  • 4.3.48 TRUNCATE TABLE
  • 4.3.49 UPDATE

5 System Catalog

• 5.1 ALL_COL_COMMENTS
• 5.2 ALL_CONSTRAINTS
• 5.3 ALL_CONS_COLUMNS
• 5.4 ALL_INDEXES
• 5.5 ALL_IND_COLUMNS
• 5.6 ALL_OBJECTS
• 5.7 ALL_SEQUENCES
• 5.8 ALL_SYNONYMS
• 5.9 ALL_TABLES
• 5.10 ALL_TAB_COLUMNS
• 5.11 ALL_TAB_COMMENTS
• 5.12 ALL_USERS
• 5.13 ALL_VIEWS
• 5.14 CAT
• 5.15 COLUMN_PRIVILEGES
• 5.16 DATABASE_PARAMETERS
• 5.17 DUAL
• 5.18 TABLE_PRIVILEGES
• 5.19 USER_OBJECTS

A Syntax Diagram Conventions

• A.1 Introduction
• A.2 Required Keywords and Parameters
• A.3 Optional Keywords and Parameters
• A.4 Syntax Loops
• A.5 Multipart Diagrams
• A.6 Database Objects
• A.7 BNF Notation

B SQL Limitations For Oracle Database Lite

C Oracle Database Lite Keywords and Reserved Words

• C.1 Oracle Lite Keywords
• C.2 Oracle Lite Reserved Words

D Oracle Database Lite Literals

• D.1 CHAR, VARCHAR
• D.2 DATE
• D.3 DECIMAL, NUMERIC, NUMBER
• D.4 REAL, FLOAT, DOUBLE PRECISION
• D.5 SMALLINT, INTEGER, BIGINT, TINYINT
• D.6 TIME
• D.7 TIMESTAMP

E Oracle Database Lite Datatypes

• E.1 BIGINT
• E.2 BINARY
• E.3 BIT
• E.4 BLOB
• E.5 CHAR
• E.6 CLOB
• E.7 DATE
• E.8 DECIMAL
• E.9 DOUBLE PRECISION
• E.10 FLOAT
• E.11 INTEGER
• E.12 LONG
• E.13 LONG RAW
• E.14 LONG VARBINARY
• E.15 LONG VARCHAR
• E.16 NUMBER
• E.17 NUMERIC
• E.18 RAW
• E.19 REAL
• E.20 ROWID
• E.21 SMALLINT
• E.22 TIME
• E.23 TIMESTAMP
• E.24 TINYINT
• E.25 VARBINARY
• E.26 VARCHAR
• E.27 VARCHAR2

F Index Creation Options

• F.1 Uniqueness Constraint in Oracle Lite
  • F.1.1 The Address Table Example
  • F.1.2 Using Uniqueness Constraints
  • F.1.3 Specifying the Number of Columns in an Index
    • F.1.3.1 The POLITE.INI File
    • F.1.3.2 The CREATE UNIQUE INDEX Statement
    • F.1.3.3 The CREATE TABLE and ALTER TABLE Statements
    • F.1.3.4 Usage Notes

Glossary

Index