Contents

Title and Copyright Information

Preface

• Audience
• Related documents
• Conventions
• Documentation Accessibility

What's New

• New features in Release 11.2.2.4.0
• New features in Release 11.2.2.1.0
• New features in Release 11.2.2.0.0

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
  • NUMBER
  • TT_BIGINT
  • TT_INTEGER
  • TT_SMALLINT
  • TT_TINYINT
  • Floating-point numbers
    • BINARY_DOUBLE
    • BINARY_FLOAT
    • FLOAT and FLOAT(n)
• BINARY and VARBINARY data types
• Numeric precedence
• LOB data types
  • BLOB
  • CLOB
  • NCLOB
  • Difference between NULL and empty LOBs
  • Initializing LOBs
• 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, Namespace and Parameters

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

3 Expressions

• Expression specification
• Subqueries
• Constants
• Format models
  • Number format models
  • Datetime format models
  • Format model for ROUND and TRUNC date functions
  • Format model for TO_CHAR of TimesTen datetime data types
• CASE expressions
• ROWID
• ROWNUM psuedocolumn

4 Functions

• Numeric functions
• Character functions returning character values
• Character functions returning number values
• String functions
• LOB functions
• NLS character set functions
• General comparison functions
• Conversion functions
• Datetime functions
• Aggregate functions
• Analytic functions
  • SQL syntax
  • Parameters
• USER functions
• Cache grid functions
• ABS
• ADD_MONTHS
• ASCIISTR
• AVG
• CAST
• CHR
• CEIL
• COALESCE
• CONCAT
• COUNT
• CURRENT_USER
• DECODE
• DENSE_RANK
• EMPTY_BLOB
• EMPTY_CLOB
• EXTRACT
• FIRST_VALUE
• FLOOR
• GREATEST
• GROUP_ID
• GROUPING
• GROUPING_ID
• INSTR, INSTRB, INSTR4
• LAST_VALUE
• LEAST
• LENGTH, LENGTHB, LENGTH4
• LOWER and UPPER
• LPAD
• LTRIM
• MAX
• MIN
• MOD
• MONTHS_BETWEEN
• NCHR
• NLS_CHARSET_ID
• NLS_CHARSET_NAME
• NLSSORT
• NULLIF
• NUMTODSINTERVAL
• NUMTOYMINTERVAL
• NVL
• POWER
• RANK
• REPLACE
• ROUND (Date)
• ROUND (expression)
• ROW_NUMBER
• RPAD
• RTRIM
• SESSION_USER
• SIGN
• SOUNDEX
• SQRT
• SUBSTR, SUBSTRB, SUBSTR4
• SUM
• SYS_CONTEXT
• SYSDATE and GETDATE
• SYSTEM_USER
• TIMESTAMPADD
• TIMESTAMPDIFF
• TO_BLOB
• TO_CHAR
• TO_CLOB
• TO_DATE
• TO_LOB
• TO_NCLOB
• TO_NUMBER
• TRIM
• TRUNC (date)
• TRUNC (expression)
• TT_HASH
• TTGRIDMEMBERID
• TTGRIDNODENAME
• TTGRIDUSERASSIGNEDNAME
• UID
• UNISTR
• USER

5 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
  • Pattern matching for strings of NCHAR, NVARCHAR2, and NCLOB data types

6 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
• CALL
• 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 SYNONYM
• CREATE TABLE
  • Column Definition
  • In-memory columnar compression of tables
• 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 REPLICATION
• DROP SEQUENCE
• DROP SYNONYM
• DROP TABLE
• DROP USER
• FLUSH CACHE GROUP
• GRANT
• INSERT
• INSERT...SELECT
• LOAD CACHE GROUP
• MERGE
• REFRESH CACHE GROUP
• REFRESH MATERIALIZED VIEW
• REVOKE
• ROLLBACK
• SELECT
  • WithClause
  • SelectList
  • TableSpec
  • JoinedTable
  • DerivedTable
  • GROUP BY clause
    • SQL syntax
    • Parameters
    • Examples
    • ROLLUP, CUBE and GROUPING SETS clauses
• TRUNCATE TABLE
• UNLOAD CACHE GROUP
• UPDATE
  • Join update

7 Privileges

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

8 Reserved Words

Index