Contents

Title and Copyright Information

Preface

• Audience
• Related documents
• Conventions
• Documentation Accessibility
• Technical support

What's New in SQL

• Access Control
• PL/SQL support
• Oracle In-Memory Database Cache (IMDB Cache)
• Asynchronous materialized views
• RETURNING ... INTO clause
• ROWID data type
• Duplicate parameter names
• Bitmap indexes
• Set operators in subqueries
• Enhanced ":" parameter markers
• Multiline C-style comments
• View and sequence names

1 Data Types

• Type specifications
• ANSI SQL data types
• Types supported for backward compatibility in Oracle type mode
• TimesTen type mapping
• Character data types
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
• Numeric data types
  • Exact and approximate numeric data types
    • NUMBER
    • TT_BIGINT
    • TT_INTEGER
    • TT_SMALLINT
    • TT_TINYINT
    • Floating-point numbers
• BINARY and VARBINARY data types
• Numeric precedence
• ROWID data type
• Datetime data types
  • DATE
  • TIME
  • TIMESTAMP
  • TT_DATE
  • TT_TIMESTAMP
• TimesTen intervals
  • Using INTERVAL data types
  • Using DATE and TIME data types
  • Handling timezone conversions
  • Datetime and interval data types in arithmetic operations
  • Restrictions on datetime and interval arithmetic operations
• Storage requirements
• Data type comparison rules
  • Numeric values
  • Date values
  • Character values
    • Binary and linguistic sorting
    • Blank-padded and nonpadded comparison semantics
• Data type conversion
  • Implicit data type conversion
• NULL values
• INF and NAN
  • Constant values
  • Primary key values
  • Selecting Inf and NaN (floating-point conditions)
  • Expressions involving Inf and NaN
• Overflow and truncation
• Underflow
• Replication limits
• TimesTen type mode (backward compatibility)
  • Data types supported in TimesTen type mode
  • Oracle data types supported in TimesTen type mode

2 Names and Parameters

• Basic names
• Owner names
• Compound identifiers
• Dynamic parameters
• Duplicate parameter names
• Inferring data type from parameters

3 Expressions

• ROWID specification
• ROWNUM specification
• Expression specification
• Subqueries
• Aggregate functions
• Constants
• Format models
  • Number format models
    • Number format elements
  • Datetime format models
  • Datetime format elements
  • Format model for ROUND and TRUNC date functions
  • Format model for TO_CHAR of TimesTen datetime data types
• ABS
• ADD_MONTHS
• ASCIISTR
• CASE
• CAST
• CHR
• CEIL
• COALESCE
• CONCAT
• DECODE
• EXTRACT
• FLOOR
• GREATEST
• LEAST
• LOWER and UPPER
• LPAD
• LTRIM
• MOD
• NCHR
• NLSSORT
• NUMTODSINTERVAL
• NUMTOYMINTERVAL
• NVL
• POWER
• ROUND (date)
• ROUND (expression)
• RPAD
• RTRIM
• SIGN
• SQRT
• String functions
  • SUBSTR
  • INSTR
  • LENGTH
• SYS_CONTEXT
• SYSDATE and GETDATE
• TO_CHAR
• TO_DATE
• TO_NUMBER
• TRIM
• TRUNC (date)
• TRUNC (expression)
• TT_HASH
• UID
• UNISTR
• USER functions
  • CURRENT_USER
  • USER
  • SESSION_USER
  • SYSTEM_USER

4 Search Conditions

• Search condition general syntax
• ALL/ NOT IN predicate (subquery)
• ALL/NOT IN predicate (value list)
• ANY/ IN predicate (subquery)
• ANY/ IN predicate (value list)
• BETWEEN predicate
• Comparison predicate
• EXISTS predicate
• IS INFINITE predicate
• IS NAN predicate
• IS NULL predicate
• LIKE predicate
  • NCHAR and NVARCHAR2

5 SQL Statements

• Comments within SQL statements
• ALTER ACTIVE STANDBY PAIR
• ALTER CACHE GROUP
• ALTER FUNCTION
• ALTER PACKAGE
• ALTER PROCEDURE
• ALTER REPLICATION
• ALTER SESSION
• ALTER TABLE
• ALTER USER
• COMMIT
• CREATE ACTIVE STANDBY PAIR
• CREATE CACHE GROUP
• CREATE FUNCTION
• CREATE INDEX
• CREATE MATERIALIZED VIEW
• CREATE MATERIALIZED VIEW LOG
• CREATE PACKAGE
• CREATE PACKAGE BODY
• CREATE PROCEDURE
• CREATE REPLICATION
  • CHECK CONFLICTS
• CREATE SEQUENCE
• CREATE TABLE
  • Column Definition
• CREATE USER
• CREATE VIEW
• DELETE
• DROP ACTIVE STANDBY PAIR
• DROP CACHE GROUP
• DROP FUNCTION
• DROP INDEX
• DROP [MATERIALIZED] VIEW
• DROP MATERIALIZED VIEW LOG
• DROP PACKAGE [BODY]
• DROP PROCEDURE
• DROP SEQUENCE
• DROP REPLICATION
• DROP TABLE
• DROP USER
• FLUSH CACHE GROUP
• GRANT
• INSERT
• INSERT...SELECT
• LOAD CACHE GROUP
• MERGE
• REFRESH CACHE GROUP
• REFRESH MATERIALIZED VIEW
• REVOKE
• ROLLBACK
• SELECT
  • SelectList
  • TableSpec
  • DerivedTable
  • JoinedTable
• TRUNCATE TABLE
• UNLOAD CACHE GROUP
• UPDATE
  • Join update

6 Privileges

• System privileges
• Object privileges
• Privilege hierarchy
• The PUBLIC role

7 System and Replication Tables 

• Tables and views reserved for internal or future use
• Required privileges to access system tables and views
• SYS.ALL_ARGUMENTS
• SYS.ALL_COL_PRIVS
• SYS.ALL_DEPENDENCIES
• SYS.ALL_DIRECTORIES
• SYS.ALL_ERRORS
• SYS.ALL_IDENTIFIERS
• SYS.ALL_OBJECTS
• SYS.ALL_PLSQL_OBJECT_SETTINGS
• SYS.ALL_PROCEDURES
• SYS.ALL_SOURCE
• SYS.ALL_STORED_SETTINGS
• SYS.ALL_TAB_PRIVS
• SYS.ALL_USERS
• SYS.CACHE_GROUP
• SYS.COLUMNS
• SYS.COL_STATS
• SYS.DBA_ARGUMENTS
• SYS.DBA_COL_PRIVS
• SYS.DBA_DEPENDENCIES
• SYS.DBA_DIRECTORIES
• SYS.DBA_ERRORS
• SYS.DBA_IDENTIFIERS
• SYS.DBA_OBJECTS
• SYS.DBA_OBJECT_SIZE
• SYS.DBA_PLSQL_OBJECT_SETTINGS
• SYS.DBA_PROCEDURES
• SYS.DBA_SOURCE
• SYS.DBA_STORED_SETTINGS
• SYS.DBA_SYS_PRIVS
• SYS.DBA_TAB_PRIVS
• SYS.DBA_USERS
• SYS.DUAL
• SYS.INDEXES
• SYS.MONITOR
• SYS.PLAN
• SYS.PUBLIC_DEPENDENCY
• SYS.SEQUENCES
• SYS.SESSION_ROLES
• SYS.SYSTEM_PRIVILEGE_MAP
• SYS.TABLE_PRIVILEGE_MAP
• SYS.TABLES
• SYS.TBL_STATS
• SYS.TCOL_STATS
• SYS.TINDEXES
• SYS.TRANSACTION_LOG_API
• SYS.TTABLES
• SYS.TTBL_STATS
• SYS.USER_ARGUMENTS
• SYS.USER_COL_PRIVS
• SYS.USER_DEPENDENCIES
• SYS.USER_ERRORS
• SYS.USER_IDENTIFIERS
• SYS.USER_OBJECTS
• SYS.USER_OBJECT_SIZE
• SYS.USER_PLSQL_OBJECT_SETTINGS
• SYS.USER_PROCEDURES
• SYS.USER_SOURCE
• SYS.USER_STORED_SETTINGS
• SYS.USER_SYS_PRIVS
• SYS.USER_TAB_PRIVS
• SYS.USER_USERS
• SYS.VIEWS
• SYS.XLASUBSCRIPTIONS
• TTREP.REPELEMENTS
• TTREP.REPLICATIONS
• TTREP.REPNETWORK
• TTREP.REPPEERS
• TTREP.REPSTORES
• TTREP.REPSUBSCRIPTIONS
• TTREP.REPTABLES
• TTREP.TTSTORES

8 Reserved Words

Index