1/16
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
Comments Within SQL Statements
1.11
Tuning SQL Statement Execution Performance With the EXPLAIN PLAN
1.11.1
The PLAN Table
1.11.2
EXPLAIN PLAN Examples
1.11.2.1
Example for Select Distinct and Group By
1.11.2.2
Example for Select Statement with Union
1.11.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 JOINED_TABLE Expression
4.3.46.8
The HINT Expression
4.3.46.9
The LIMIT and OFFSET Clauses
4.3.46.10
Select Statement Behavior When Retrieving BLOBs in a Read Committed Transaction
4.3.46.11
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
Scripting on this page enhances content navigation, but does not change the content in any way.