Jump to content

Table of Contents

Oracle Compatibility Developer’s Guide

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 SQL Syntax

3.1.1 Lexical Structure

3.1.2 Identifiers and Key Words

3.1.3 Constants

3.2 Data Types

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 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 PUBLIC SYNONYM

3.3.19 CREATE ROLE

3.3.20 CREATE SCHEMA

3.3.21 CREATE SEQUENCE

3.3.22 CREATE TABLE

3.3.23 CREATE TABLE AS

3.3.24 CREATE TRIGGER

3.3.25 CREATE USER

3.3.26 CREATE VIEW

3.3.28 DROP DATABASE LINK

3.3.29 DROP FUNCTION

3.3.30 DROP INDEX

3.3.31 DROP PACKAGE

3.3.32 DROP PROCEDURE

3.3.33 DROP PUBLIC SYNONYM

3.3.34 DROP ROLE

3.3.35 DROP SEQUENCE

3.3.36 DROP TABLE

3.3.37 DROP TABLESPACE

3.3.38 DROP TRIGGER

3.3.39 DROP USER

3.3.40 DROP VIEW

3.3.45 ROLLBACK

3.3.46 ROLLBACK TO SAVEPOINT

3.3.47 SAVEPOINT

3.3.49 SET CONSTRAINTS

3.3.50 SET ROLE

3.3.51 SET TRANSACTION

3.3.52 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 CASE Expression

4.5.3 CASE Statement

4.5.5 Exception Handling

4.5.6 Raise Application Error

4.6 Dynamic SQL

4.7 Static Cursors

4.7.1 Declaring a Cursor

4.7.2 Opening a Cursor

4.7.3 Fetching Rows From a Cursor

4.7.4 Closing a Cursor

4.7.5 Using %ROWTYPE With Cursors

4.7.6 Cursor Attributes

4.7.7 Cursor FOR Loop

4.7.8 Parameterized Cursors

4.8 REF CURSORs and Cursor Variables

4.8.1 REF CURSOR Overview

4.8.2 Declaring a Cursor Variable

4.8.3 Opening a Cursor Variable

4.8.4 Fetching Rows From a Cursor Variable

4.8.5 Closing a Cursor Variable

4.8.6 Usage Restrictions

4.8.8 Dynamic Queries With REF CURSORs

4.9 Collections

4.9.1 Associative Arrays

4.9.2 Collection Methods

4.9.3 Using the FORALL Statement

4.9.4 Using the BULK COLLECT Clause

4.10 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 DBMS_ALERT

7.1.3 REMOVEALL

7.1.7 Comprehensive Example

7.2 DBMS_OUTPUT

7.2.5 GET_LINES

7.2.9 SERVEROUTPUT

7.3 DBMS_PIPE

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

8 Open Client Library

9 Oracle Catalog Views

9.1 ALL_OBJECTS

9.2 ALL_SOURCE

9.3 ALL_SYNONYMS

9.4 ALL_TAB_COLUMNS

9.5 ALL_TABLES

9.6 ALL_USERS

9.7 ALL_VIEW_COLUMNS

9.8 ALL_VIEWS

9.9 DBA_ROLE_PRIVS

9.10 DBA_ROLES

9.11 USER_OBJECTS

9.12 USER_SOURCE

9.13 USER_SYNONYMS

9.14 USER_TAB_COLUMNS

9.15 USER_TABLES

9.16 USER_VIEW_COLUMNS

9.17 USER_VIEWS

10.1 EDB*Plus

10.1.1 Starting EDB*Plus

10.1.2 Command Summary

10.2 EDB*Loader

10.2.1 Features of EDB*Loader 8.3

10.2.2 Features not supported in this release:

10.2.3 Starting EDB*Loader from the command Line

10.2.4 EDB*Loader Examples

10.3 Dynamic Runtime Instrumentation

10.3.1 Enabling DRI

10.3.2 Catalog views

10.3.3 Using Included DRITA Scripts

10.3.4 Individual Event descriptions

10.3.5 Tuning recommendations

10.3.6 Additional functions and tables added for 8.3 release

11.1 Acknowledgements

Powered by Transit

Copyright 2010 by EnterpriseDB Corporation, All Rights Reserved.

EnterpriseDB is the enterprise Postgres and open source database company, offering productized distributions of the PostgreSQL database, also referred to as the Postgres database. EnterpriseDB has two distributions of PostgreSQL. Postgres Plus Standard Server is often used in MySQL migration efforts, and Postgres Plus Advanced Server is often used in Oracle migration projects and includes Oracle compatibility to cut Oracle costs. Visit the EnterpriseDB website to download Postgres, to download PostgreSQL, or to inquire about Postgres services or PostgreSQL services. EnterpriseDB, who employs Postgres leaders including Bruce Momjian and Dave Page, is the leader in supporting Postgres data and PostgreSQL data applications.