Documentation | EnterpriseDB

How EnterpriseDB Works For You

Oracle Costs Eating
Up Your Budget?
Learn How Postgres Can
Rein in Oracle Costs

Get Started Today!
Tools for evaluations & pilots,
through deployment and into production

1.1 What’s New

1.2 Typographical Conventions Used in this Guide

1.3 Oracle Compatible Configuration Parameters

1.3.1 edb_redwood_date

1.3.2 edb_redwood_strings

1.3.3 edb_stmt_level_tx

1.3.4 oracle_home

1.4 About the Examples Used in this Guide

2.1 Getting Started

2.1.1 Sample Database

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.8 Deletions

2.2 Advanced Concepts

2.2.2 Foreign Keys

2.2.3 The ROWNUM Pseudo-Column

2.2.5 Hierarchical Queries

3 The SQL Language

3.1.1 Lexical Structure

3.1.2 Identifiers and Key Words

3.1.3 Constants

3.2.1 Numeric Types

3.2.2 Character Types

3.2.3 Binary Data

3.2.4 Date/Time Types

3.2.5 Boolean Type

3.3 SQL Commands

3.3.1 ALTER INDEX

3.3.2 ALTER ROLE

3.3.3 ALTER SEQUENCE

3.3.4 ALTER SESSION

3.3.5 ALTER TABLE

3.3.6 ALTER TABLESPACE

3.3.7 ALTER USER

3.3.10 CREATE DATABASE

3.3.11 CREATE [PUBLIC] DATABASE LINK

3.3.12 CREATE DIRECTORY

3.3.13 CREATE FUNCTION

3.3.14 CREATE INDEX

3.3.15 CREATE PACKAGE

3.3.16 CREATE PACKAGE BODY

3.3.17 CREATE PROCEDURE

3.3.18 CREATE ROLE

3.3.19 CREATE SCHEMA

3.3.20 CREATE SEQUENCE

3.3.21 CREATE SYNONYM

3.3.22 CREATE TABLE

3.3.23 CREATE TABLE AS

3.3.24 CREATE TRIGGER

3.3.25 CREATE TYPE

3.3.26 CREATE USER

3.3.27 CREATE VIEW

3.3.29 DROP DATABASE LINK

3.3.30 DROP FUNCTION

3.3.31 DROP INDEX

3.3.32 DROP PACKAGE

3.3.33 DROP PROCEDURE

3.3.34 DROP SYNONYM

3.3.35 DROP ROLE

3.3.36 DROP SEQUENCE

3.3.37 DROP TABLE

3.3.38 DROP TABLESPACE

3.3.39 DROP TRIGGER

3.3.40 DROP TYPE

3.3.41 DROP USER

3.3.42 DROP VIEW

3.3.44 GRANT on Database Objects

3.3.45 GRANT on Roles

3.3.49 ROLLBACK

3.3.50 ROLLBACK TO SAVEPOINT

3.3.51 SAVEPOINT

3.3.53 SET CONSTRAINTS

3.3.54 SET ROLE

3.3.55 SET TRANSACTION

3.3.56 TRUNCATE

3.4 Optimizer Hints

3.4.1 Default Optimization Modes

3.4.2 Access Method Hints

3.4.3 Joining Relations Hints

3.4.4 Global Hints

3.4.5 Conflicting Hints

3.5 Functions and Operators

3.5.1 Logical Operators

3.5.2 Comparison Operators

3.5.3 Mathematical Functions and Operators

3.5.4 String Functions and Operators

3.5.5 Pattern Matching Using the LIKE Operator

3.5.6 Data Type Formatting Functions

3.5.7 Date/Time Functions and Operators

3.5.8 Sequence Manipulation Functions

3.5.9 Conditional Expressions

3.5.10 Aggregate Functions

3.5.11 Subquery Expressions

4 Stored Procedure Language

4.1 Basic SPL Elements

4.1.1 Character Set

4.1.2 Case Sensitivity

4.1.3 Identifiers

4.1.4 Qualifiers

4.1.5 Constants

4.2 SPL Programs

4.2.1 SPL Block Structure

4.2.2 Anonymous Blocks

4.2.3 Procedures Overview

4.2.4 Functions Overview

4.2.5 Procedure and Function Parameters

4.2.6 Program Security

4.3 Variable Declarations

4.3.1 Declaring a Variable

4.3.2 Using %TYPE in Variable Declarations

4.3.3 Using %ROWTYPE in Record Declarations

4.3.4 User-Defined Record Types and Record Variables

4.4 Basic Statements

4.4.2 Assignment

4.4.3 SELECT INTO

4.4.7 Using the RETURNING INTO Clause

4.4.8 Obtaining the Result Status

4.5 Control Structures

4.5.1 IF Statement

4.5.2 RETURN Statement

4.5.3 GOTO Statement

4.5.4 CASE Expression

4.5.5 CASE Statement

4.5.7 Exception Handling

4.5.8 User-defined Exceptions

4.5.9 PRAGMA EXCEPTION_INIT

4.5.10 RAISE_APPLICATION_ERROR

4.6 Transaction Control

4.7 Dynamic SQL

4.8 Static Cursors

4.8.1 Declaring a Cursor

4.8.2 Opening a Cursor

4.8.3 Fetching Rows From a Cursor

4.8.4 Closing a Cursor

4.8.5 Using %ROWTYPE With Cursors

4.8.6 Cursor Attributes

4.8.7 Cursor FOR Loop

4.8.8 Parameterized Cursors

4.9 REF CURSORs and Cursor Variables

4.9.1 REF CURSOR Overview

4.9.2 Declaring a Cursor Variable

4.9.3 Opening a Cursor Variable

4.9.4 Fetching Rows From a Cursor Variable

4.9.5 Closing a Cursor Variable

4.9.6 Usage Restrictions

4.9.8 Dynamic Queries With REF CURSORs

4.10 Collections

4.10.1 Associative Arrays

4.10.2 Nested Tables

4.11 Collection Methods

4.11.10 Using the MULTISET UNION Operator

4.11.11 Using the FORALL Statement

4.11.12 Using the BULK COLLECT Clause

4.12 Errors and Messages

5.2 Types of Triggers

5.3 Creating Triggers

5.4 Trigger Variables

5.5 Transactions and Exceptions

5.6 Trigger Examples

5.6.1 Before Statement-Level Trigger

5.6.2 After Statement-Level Trigger

5.6.3 Before Row-Level Trigger

5.6.4 After Row-Level Trigger

6.1 Package Components

6.1.1 Package Specification Syntax

6.1.2 Package Body Syntax

6.2 Creating Packages

6.2.1 Creating the Package Specification

6.2.2 Creating the Package Body

6.3 Referencing a Package

6.4 Using Packages With User Defined Types

6.5 Dropping a Package

7 Built-In Packages

7.1.3 REMOVEALL

7.1.7 Comprehensive Example

7.2 DBMS_OUTPUT

7.2.5 GET_LINES

7.2.9 SERVEROUTPUT

7.3.1 CREATE_PIPE

7.3.2 NEXT_ITEM_TYPE

7.3.3 PACK_MESSAGE

7.3.5 RECEIVE_MESSAGE

7.3.6 REMOVE_PIPE

7.3.7 RESET_BUFFER

7.3.8 SEND_MESSAGE

7.3.9 UNIQUE_SESSION_NAME

7.3.10 UNPACK_MESSAGE

7.3.11 Comprehensive Example

7.4.2 FCLOSE_ALL

7.4.10 NEW_LINE

7.4.12 PUT_LINE

7.5.1 BIND_VARIABLE

7.5.2 BIND_VARIABLE_CHAR

7.5.3 BIND VARIABLE RAW

7.5.4 CLOSE_CURSOR

7.5.5 COLUMN_VALUE

7.5.6 COLUMN_VALUE_CHAR

7.5.7 COLUMN VALUE RAW

7.5.8 DEFINE_COLUMN

7.5.9 DEFINE_COLUMN_CHAR

7.5.10 DEFINE COLUMN RAW

7.5.12 EXECUTE_AND_FETCH

7.5.13 FETCH_ROWS

7.5.15 LAST_ROW_COUNT

7.5.16 OPEN_CURSOR

7.6.4 NEXT_DATE

7.7.3 CONVERTTOBLOB

7.7.4 CONVERTTOCLOB

7.7.7 GET_STORAGE_LIMIT

7.7.8 GETLENGTH

7.7.14 WRITEAPPEND

7.8 DBMS_UTILITY

7.8.1 LNAME_ARRAY

7.8.2 UNCL_ARRAY

7.8.3 ANALYZE_DATABASE, ANALYZE SCHEMA and ANALYZE PART_OBJECT

7.8.4 CANONICALIZE

7.8.5 COMMA_TO_TABLE

7.8.6 DB_VERSION

7.8.7 EXEC_DDL_STATEMENT

7.8.8 FORMAT_CALL_STACK

7.8.9 GET_CPU_TIME

7.8.10 GET_DEPENDENCY

7.8.11 GET_HASH_VALUE

7.8.12 GET_PARAMETER_VALUE

7.8.13 GET_TIME

7.8.14 NAME_TOKENIZE

7.8.15 TABLE_TO_COMMA

7.9.2 SEND_ATTACH_RAW

7.9.3 SEND_ATTACH_VARCHAR2

7.10.1 CONNECTION

7.10.2 REPLY/REPLIES

7.10.3 CLOSE_DATA

7.10.5 COMMAND_REPLIES

7.10.12 OPEN_CONNECTION

7.10.13 OPEN_DATA

7.10.18 WRITE_DATA

7.10.19 Comprehensive Example

8 Object Types and Objects

8.1 Object Type Components

8.1.1 Object Type Specification Syntax

8.2 Creating Object Types

8.3 Creating Object Instances

8.4 Referencing an Object

8.5 Dropping an Object Type

9 Open Client Library

9.1 Comparison with Oracle Call Interface

9.2 OCL Reference

9.2.1 Connect, Authorize and Initialize Functions

9.2.2 Handle and Descriptor Functions

9.2.3 Bind, Define and Describe Functions

9.2.4 Statement Functions

9.2.5 Transaction Functions

9.2.6 Date and Datetime Functions

9.2.7 Interval Functions

9.2.8 Number Functions

9.2.9 String Functions

9.2.10 Cartridge Services and File I/O Interface Functions

9.2.11 Miscellaneous Functions

9.2.12 Supported Data Types

10 Oracle Catalog Views

10.1 ALL_ALL_TABLES

10.2 ALL_CONS_COLUMNS

10.3 ALL_CONSTRAINTS

10.4 ALL_DB_LINKS

10.5 ALL_IND_COLUMNS

10.6 ALL_INDEXES

10.7 ALL_OBJECTS

10.8 ALL_SOURCE

10.9 ALL_SYNONYMS

10.10 ALL_TAB_COLUMNS

10.11 ALL_TABLES

10.12 ALL_TRIGGERS

10.13 ALL_TYPES

10.14 ALL_USERS

10.15 ALL_VIEW_COLUMNS

10.16 ALL_VIEWS

10.17 DBA_ALL_TABLES

10.18 DBA_CONS_COLUMNS

10.19 DBA_CONSTRAINTS

10.20 DBA_DB_LINKS

10.21 DBA_IND_COLUMNS

10.22 DBA_INDEXES

10.24 DBA_OBJECTS

10.25 DBA_ROLE_PRIVS

10.26 DBA_ROLES

10.27 DBA_SOURCE

10.28 DBA_SYNONYMS

10.29 DBA_TABLES

10.30 DBA_TRIGGERS

10.31 DBA_TYPES

10.32 DBA_USERS

10.33 DBA_VIEWS

10.34 USER_ALL_TABLES

10.35 USER_CONS_COLUMNS

10.36 USER_CONSTRAINTS

10.37 USER_DB_LINKS

10.38 USER_IND_COLUMNS

10.39 USER_INDEXES

10.40 USER_JOBS

10.41 USER_OBJECTS

10.42 USER_SOURCE

10.43 USER_SYNONYMS

10.44 USER_TAB_COLUMNS

10.45 USER_TABLES

10.46 USER_TRIGGERS

10.47 USER_TYPES

10.48 USER_USERS

10.49 USER_VIEW_COLUMNS

10.50 USER_VIEWS

11.1.1 Starting EDB*Plus

11.1.2 Command Summary

11.2 EDB*Loader

11.2.1 The EDB*Loader Control File

11.2.2 Invoking EDB*Loader

11.3.1 Using EDB*Wrap to Obfuscate Source Code

11.4 Dynamic Runtime Instrumentation Tools Architecture (DRITA)

11.4.1 Initialization Parameters

11.4.2 Setting up and Using DRITA

11.5 DRITA Functions

11.5.1 get_snaps()

11.5.2 sys_rpt()

11.5.3 sess_rpt()

11.5.4 sessid_rpt()

11.5.5 sesshist_rpt()

11.5.6 purgesnap()

11.5.7 truncsnap()

11.6 Simulating Statspack AWR Reports

11.6.1 edbreport()

11.6.2 stat_db_rpt()

11.6.3 stat_tables_rpt()

11.6.4 statio_tables_rpt()

11.6.5 stat_indexes_rpt()

11.6.6 statio_indexes_rpt()

11.7 Performance Tuning Recommendations

11.8 Event Descriptions

11.9 Catalog Views

11.9.1 edb$system_waits

11.9.2 edb$session_waits

11.9.3 edb$session_wait_history

12.1 System Catalog Tables

12.1.2 pg_synonym

12.1.3 edb_variable

12.1.4 product_component_version

13 Acknowledgements

Copyright © 2013 EnterpriseDB Corporation. All rights reserved.