Table of Contents Previous Next

Table of Contents

1 Introduction
1.1 What’s New
1.2 Typographical Conventions Used in this Guide
1.3 Configuration Parameters Compatible with Oracle Databases
1.3.1 edb_redwood_date
1.3.2 edb_redwood_raw_names
1.3.3 edb_redwood_strings
1.3.4 edb_stmt_level_tx
1.3.5 oracle_home
1.4 About the Examples Used in this Guide
2 SQL Tutorial
2.1 Getting Started
2.1.1 Sample Database
2.1.1.1 Sample Database Installation
2.1.1.2 Sample Database Description
2.1.2 Creating a New Table
2.1.3 Populating a Table With Rows
2.1.4 Querying a Table
2.1.5 Joins Between Tables
2.1.6 Aggregate Functions
2.1.7 Updates
2.1.8 Deletions
2.1.9 The SQL Language
2.2 Advanced Concepts
2.2.1 Views
2.2.2 Foreign Keys
2.2.3 The ROWNUM Pseudo-Column
2.2.4 Synonyms
2.2.5 Hierarchical Queries
2.2.5.1 Defining the Parent/Child Relationship
2.2.5.2 Selecting the Root Nodes
2.2.5.3 Organization Tree in the Sample Application
2.2.5.4 Node Level
2.2.5.5 Ordering the Siblings
2.2.5.6 Retrieving the Root Node with CONNECT_BY_ROOT
2.2.5.7 Retrieving a Path with SYS_CONNECT_BY_PATH
2.2.6 Multidimensional Analysis
2.2.6.1 ROLLUP Extension
2.2.6.2 CUBE Extension
2.2.6.3 GROUPING SETS Extension
2.2.6.4 GROUPING Function
2.2.6.5 GROUPING_ID Function
2.3 Profile Management
2.3.1 Creating a New Profile
2.3.1.1 Creating a Password Function
2.3.2 Altering a Profile
2.3.3 Dropping a Profile
2.3.4 Associating a Profile with an Existing Role
2.3.5 Unlocking a Locked Account
2.3.6 Creating a New Role Associated with a Profile
2.3.7 Backing up Profile Management Functions
2.4 Optimizer Hints
2.4.1 Default Optimization Modes
2.4.2 Access Method Hints
2.4.3 Specifying a Join Order
2.4.4 Joining Relations Hints
2.4.5 Global Hints
2.4.6 Using the APPEND Optimizer Hint
2.4.7 Parallelism Hints
2.4.8 Conflicting Hints
3 Stored Procedure Language
3.1 Basic SPL Elements
3.1.1 Character Set
3.1.2 Case Sensitivity
3.1.3 Identifiers
3.1.4 Qualifiers
3.1.5 Constants
3.1.6 User-Defined PL/SQL Subtypes
3.2 SPL Programs
3.2.1 SPL Block Structure
3.2.2 Anonymous Blocks
3.2.3 Procedures Overview
3.2.3.1 Creating a Procedure
3.2.3.2 Calling a Procedure
3.2.3.3 Deleting a Procedure
3.2.4 Functions Overview
3.2.4.1 Creating a Function
3.2.4.2 Calling a Function
3.2.4.3 Deleting a Function
3.2.5 Procedure and Function Parameters
3.2.5.1 Positional vs. Named Parameter Notation
3.2.5.2 Parameter Modes
3.2.5.3 Using Default Values in Parameters
3.2.6 Subprograms – Subprocedures and Subfunctions
3.2.6.1 Creating a Subprocedure
3.2.6.2 Creating a Subfunction
3.2.6.3 Block Relationships
3.2.6.4 Invoking Subprograms
3.2.6.5 Using Forward Declarations
3.2.6.6 Overloading Subprograms
3.2.6.7 Accessing Subprogram Variables
3.2.7 Compilation Errors in Procedures and Functions
3.2.8 Program Security
3.2.8.1 EXECUTE Privilege
3.2.8.2 Database Object Name Resolution
3.2.8.3 Database Object Privileges
3.2.8.4 Definer’s vs. Invokers Rights
3.2.8.5 Security Example
3.3 Variable Declarations
3.3.1 Declaring a Variable
3.3.2 Using %TYPE in Variable Declarations
3.3.3 Using %ROWTYPE in Record Declarations
3.3.4 User-Defined Record Types and Record Variables
3.4 Basic Statements
3.4.1 NULL
3.4.2 Assignment
3.4.3 SELECT INTO
3.4.4 INSERT
3.4.5 UPDATE
3.4.6 DELETE
3.4.7 Using the RETURNING INTO Clause
3.4.8 Obtaining the Result Status
3.5 Control Structures
3.5.1 IF Statement
3.5.1.1 IF-THEN
3.5.1.2 IF-THEN-ELSE
3.5.1.3 IF-THEN-ELSE IF
3.5.1.4 IF-THEN-ELSIF-ELSE
3.5.2 RETURN Statement
3.5.3 GOTO Statement
3.5.4 CASE Expression
3.5.4.1 Selector CASE Expression
3.5.4.2 Searched CASE Expression
3.5.5 CASE Statement
3.5.5.1 Selector CASE Statement
3.5.5.2 Searched CASE statement
3.5.6 Loops
3.5.6.1 LOOP
3.5.6.2 EXIT
3.5.6.3 CONTINUE
3.5.6.4 WHILE
3.5.6.5 FOR (integer variant)
3.5.7 Exception Handling
3.5.8 User-defined Exceptions
3.5.9 PRAGMA EXCEPTION_INIT
3.5.10 RAISE_APPLICATION_ERROR
3.6 Transaction Control
3.6.1 COMMIT
3.6.2 ROLLBACK
3.6.3 PRAGMA AUTONOMOUS_TRANSACTION
3.7 Dynamic SQL
3.8 Static Cursors
3.8.1 Declaring a Cursor
3.8.2 Opening a Cursor
3.8.3 Fetching Rows From a Cursor
3.8.4 Closing a Cursor
3.8.5 Using %ROWTYPE With Cursors
3.8.6 Cursor Attributes
3.8.6.1 %ISOPEN
3.8.6.2 %FOUND
3.8.6.3 %NOTFOUND
3.8.6.4 %ROWCOUNT
3.8.6.5 Summary of Cursor States and Attributes
3.8.7 Cursor FOR Loop
3.8.8 Parameterized Cursors
3.9 REF CURSORs and Cursor Variables
3.9.1 REF CURSOR Overview
3.9.2 Declaring a Cursor Variable
3.9.2.1 Declaring a SYS_REFCURSOR Cursor Variable
3.9.2.2 Declaring a User Defined REF CURSOR Type Variable
3.9.3 Opening a Cursor Variable
3.9.4 Fetching Rows From a Cursor Variable
3.9.5 Closing a Cursor Variable
3.9.6 Usage Restrictions
3.9.7 Examples
3.9.7.1 Returning a REF CURSOR From a Function
3.9.7.2 Modularizing Cursor Operations
3.9.8 Dynamic Queries With REF CURSORs
3.10 Collections
3.10.1 Associative Arrays
3.10.2 Nested Tables
3.10.3 Varrays
3.11 Collection Methods
3.11.1 COUNT
3.11.2 DELETE
3.11.3 EXISTS
3.11.4 EXTEND
3.11.5 FIRST
3.11.6 LAST
3.11.7 LIMIT
3.11.8 NEXT
3.11.9 PRIOR
3.11.10 TRIM
3.12 Working with Collections
3.12.1 TABLE()
3.12.2 Using the MULTISET UNION Operator
3.12.3 Using the FORALL Statement
3.12.4 Using the BULK COLLECT Clause
3.12.4.1 SELECT BULK COLLECT
3.12.4.2 FETCH BULK COLLECT
3.12.4.3 EXECUTE IMMEDIATE BULK COLLECT
3.12.4.4 RETURNING BULK COLLECT
3.13 Errors and Messages
4 Triggers
4.1 Overview
4.2 Types of Triggers
4.3 Creating Triggers
4.4 Trigger Variables
4.5 Transactions and Exceptions
4.6 Compound Triggers
4.7 Trigger Examples
4.7.1 Before Statement-Level Trigger
4.7.2 After Statement-Level Trigger
4.7.3 Before Row-Level Trigger
4.7.4 After Row-Level Trigger
4.7.5 INSTEAD OF Trigger
4.7.6 Compound Triggers
5 Packages
6 Object Types and Objects
6.1 Basic Object Concepts
6.1.1 Attributes
6.1.2 Methods
6.1.3 Overloading Methods
6.2 Object Type Components
6.2.1 Object Type Specification Syntax
6.2.2 Object Type Body Syntax
6.3 Creating Object Types
6.3.1 Member Methods
6.3.2 Static Methods
6.3.3 Constructor Methods
6.4 Creating Object Instances
6.5 Referencing an Object
6.6 Dropping an Object Type
7 Open Client Library
8 Oracle Catalog Views
9 Tools and Utilities
10 Table Partitioning
10.1 Selecting a Partition Type
10.1.1 Interval Partitioning
10.2 Using Partition Pruning
10.2.1 Example - Partition Pruning
10.3 Partitioning Commands Compatible with Oracle Databases
10.3.1 CREATE TABLE…PARTITION BY
10.3.1.1 Example - PARTITION BY LIST
10.3.1.2 Example - PARTITION BY RANGE
10.3.1.3 Example - INTERVAL PARTITIONING
10.3.1.4 Example - PARTITION BY HASH
10.3.1.5 Example - PARTITION BY RANGE, SUBPARTITION BY LIST
10.3.2 ALTER TABLE...ADD PARTITION
10.3.2.1 Example - Adding a Partition to a LIST Partitioned Table
10.3.2.2 Example - Adding a Partition to a RANGE Partitioned Table
10.3.3 ALTER TABLE…ADD SUBPARTITION
10.3.3.1 Example - Adding a Subpartition to a LIST-RANGE Partitioned Table
10.3.3.2 Example - Adding a Subpartition to a RANGE-LIST Partitioned Table
10.3.4 ALTER TABLE...SPLIT PARTITION
10.3.4.1 Example - Splitting a LIST Partition
10.3.4.2 Example - Splitting a RANGE Partition
10.3.5 ALTER TABLE...SPLIT SUBPARTITION
10.3.5.1 Example - Splitting a LIST Subpartition
10.3.5.2 Example - Splitting a RANGE Subpartition
10.3.6 ALTER TABLE…EXCHANGE PARTITION
10.3.6.1 Example - Exchanging a Table for a Partition
10.3.7 ALTER TABLE…MOVE PARTITION
10.3.7.1 Example - Moving a Partition to a Different Tablespace
10.3.8 ALTER TABLE…RENAME PARTITION
10.3.8.1 Example - Renaming a Partition
10.3.9 ALTER TABLE…SET INTERVAL
10.3.9.1 Example - Setting an Interval Partitioning
10.3.10 DROP TABLE
10.3.11 ALTER TABLE…DROP PARTITION
10.3.11.1 Example - Deleting a Partition
10.3.12 ALTER TABLE…DROP SUBPARTITION
10.3.12.1 Example - Deleting a Subpartition
10.3.13 TRUNCATE TABLE
10.3.13.1 Example - Emptying a Table
10.3.14 ALTER TABLE…TRUNCATE PARTITION
10.3.14.1 Example - Emptying a Partition
10.3.15 ALTER TABLE…TRUNCATE SUBPARTITION
10.3.15.1 Example - Emptying a Subpartition
10.4 Handling Stray Values in a LIST or RANGE Partitioned Table
10.5 Specifying Multiple Partitioning Keys in a RANGE Partitioned Table
10.6 Retrieving Information about a Partitioned Table
10.6.1 Table Partitioning Views - Reference
10.6.1.1 ALL_PART_TABLES
10.6.1.2 ALL_TAB_PARTITIONS
10.6.1.3 ALL_TAB_SUBPARTITIONS
10.6.1.4 ALL_PART_KEY_COLUMNS
10.6.1.5 ALL_SUBPART_KEY_COLUMNS
11 ECPGPlus
12 dblink_ora
12.1 dblink_ora Functions and Procedures
12.1.1 dblink_ora_connect()
12.1.2 dblink_ora_status()
12.1.3 dblink_ora_disconnect()
12.1.4 dblink_ora_record()
12.1.5 dblink_ora_call()
12.1.6 dblink_ora_exec()
12.1.7 dblink_ora_copy()
12.2 Calling dblink_ora Functions
13 System Catalog Tables
14 Acknowledgements
Table of Contents Previous Next