Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

1 Introduction

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

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.7 Updates

2.1.8 Deletions

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

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.1.4 Comments

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.2.6 XML 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.8 COMMENT

3.3.9 COMMIT

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.28 DELETE

3.3.29 DROP DATABASE LINK

3.3.30 DROP DIRECTORY

3.3.31 DROP FUNCTION

3.3.32 DROP INDEX

3.3.33 DROP PACKAGE

3.3.34 DROP PROCEDURE

3.3.35 DROP SYNONYM

3.3.36 DROP ROLE

3.3.37 DROP SEQUENCE

3.3.38 DROP TABLE

3.3.39 DROP TABLESPACE

3.3.40 DROP TRIGGER

3.3.41 DROP TYPE

3.3.42 DROP USER

3.3.43 DROP VIEW

3.3.44 GRANT

3.3.45 GRANT on Database Objects

3.3.46 GRANT on Roles

3.3.47 GRANT on System Privileges

3.3.48 INSERT

3.3.49 LOCK

3.3.50 REVOKE

3.3.51 ROLLBACK

3.3.52 ROLLBACK TO SAVEPOINT

3.3.53 SAVEPOINT

3.3.54 SELECT

3.3.55 SET CONSTRAINTS

3.3.56 SET ROLE

3.3.57 SET TRANSACTION

3.3.58 TRUNCATE

3.3.59 UPDATE

3.4 Optimizer Hints

3.4.1 Default Optimization Modes

3.4.2 Access Method Hints

3.4.3 Specifying a Join Order

3.4.4 Joining Relations Hints

3.4.5 Global Hints

3.4.6 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.1 NULL

4.4.2 Assignment

4.4.3 SELECT INTO

4.4.4 INSERT

4.4.5 UPDATE

4.4.6 DELETE

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.6 Loops

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.6.1 COMMIT

4.6.2 ROLLBACK

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.7 Examples

4.9.8 Dynamic Queries With REF CURSORs

4.10 Collections

4.10.1 Associative Arrays

4.10.2 Nested Tables

4.10.3 Varrays

4.11 Collection Methods

4.11.1 COUNT

4.11.2 DELETE

4.11.3 EXISTS

4.11.4 EXTEND

4.11.5 FIRST

4.11.6 LAST

4.11.7 LIMIT

4.11.8 NEXT

4.11.9 PRIOR

4.11.10 TRIM

4.11.11 Using the MULTISET UNION Operator

4.11.12 Using the FORALL Statement

4.11.13 Using the BULK COLLECT Clause

4.12 Errors and Messages

5 Triggers

5.1 Overview

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 Packages

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.1 REGISTER

7.1.2 REMOVE

7.1.3 REMOVEALL

7.1.4 SIGNAL

7.1.5 WAITANY

7.1.6 WAITONE

7.1.7 Comprehensive Example

7.2 DBMS_JOB

7.2.1 BROKEN

7.2.2 CHANGE

7.2.3 INTERVAL

7.2.4 NEXT_DATE

7.2.5 REMOVE

7.2.6 RUN

7.2.7 SUBMIT

7.2.8 WHAT

7.3 DBMS_LOB

7.3.1 APPEND

7.3.2 COMPARE

7.3.3 CONVERTTOBLOB

7.3.4 CONVERTTOCLOB

7.3.5 COPY

7.3.6 ERASE

7.3.7 GET_STORAGE_LIMIT

7.3.8 GETLENGTH

7.3.9 INSTR

7.3.10 READ

7.3.11 SUBSTR

7.3.12 TRIM

7.3.13 WRITE

7.3.14 WRITEAPPEND

7.4 DBMS_OUTPUT

7.4.1 CHARARR

7.4.2 DISABLE

7.4.3 ENABLE

7.4.4 GET_LINE

7.4.5 GET_LINES

7.4.6 NEW_LINE

7.4.7 PUT

7.4.8 PUT_LINE

7.4.9 SERVEROUTPUT

7.5 DBMS_PIPE

7.5.1 CREATE_PIPE

7.5.2 NEXT_ITEM_TYPE

7.5.3 PACK_MESSAGE

7.5.4 PURGE

7.5.5 RECEIVE_MESSAGE

7.5.6 REMOVE_PIPE

7.5.7 RESET_BUFFER

7.5.8 SEND_MESSAGE

7.5.9 UNIQUE_SESSION_NAME

7.5.10 UNPACK_MESSAGE

7.5.11 Comprehensive Example

7.6 DBMS_PROFILER

7.6.1 FLUSH_DATA

7.6.2 GET_VERSION

7.6.3 INTERNAL_VERSION_CHECK

7.6.4 PAUSE_PROFILER

7.6.5 RESUME_PROFILER

7.6.6 START_PROFILER

7.6.7 STOP_PROFILER

7.7 DBMS_RLS

7.7.1 ADD_POLICY

7.7.2 DROP_POLICY

7.7.3 ENABLE_POLICY

7.8 DBMS_SQL

7.8.1 BIND_VARIABLE

7.8.2 BIND_VARIABLE_CHAR

7.8.3 BIND VARIABLE RAW

7.8.4 CLOSE_CURSOR

7.8.5 COLUMN_VALUE

7.8.6 COLUMN_VALUE_CHAR

7.8.7 COLUMN VALUE RAW

7.8.8 DEFINE_COLUMN

7.8.9 DEFINE_COLUMN_CHAR

7.8.10 DEFINE COLUMN RAW

7.8.11 DESCRIBE COLUMNS

7.8.12 EXECUTE

7.8.13 EXECUTE_AND_FETCH

7.8.14 FETCH_ROWS

7.8.15 IS_OPEN

7.8.16 LAST_ROW_COUNT

7.8.17 OPEN_CURSOR

7.8.18 PARSE

7.9 DBMS_UTILITY

7.9.1 LNAME_ARRAY

7.9.2 UNCL_ARRAY

7.9.3 ANALYZE_DATABASE, ANALYZE SCHEMA and ANALYZE PART_OBJECT

7.9.4 CANONICALIZE

7.9.5 COMMA_TO_TABLE

7.9.6 DB_VERSION

7.9.7 EXEC_DDL_STATEMENT

7.9.8 FORMAT_CALL_STACK

7.9.9 GET_CPU_TIME

7.9.10 GET_DEPENDENCY

7.9.11 GET_HASH_VALUE

7.9.12 GET_PARAMETER_VALUE

7.9.13 GET_TIME

7.9.14 NAME_TOKENIZE

7.9.15 TABLE_TO_COMMA

7.10 UTL_FILE

7.10.1 FCLOSE

7.10.2 FCLOSE_ALL

7.10.3 FCOPY

7.10.4 FFLUSH

7.10.5 FOPEN

7.10.6 FREMOVE

7.10.7 FRENAME

7.10.8 GET_LINE

7.10.9 IS_OPEN

7.10.10 NEW_LINE

7.10.11 PUT

7.10.12 PUT_LINE

7.10.13 PUTF

7.11 UTL_MAIL

7.11.1 SEND

7.11.2 SEND_ATTACH_RAW

7.11.3 SEND_ATTACH_VARCHAR2

7.12 UTL_SMTP

7.12.1 CONNECTION

7.12.2 REPLY/REPLIES

7.12.3 CLOSE_DATA

7.12.4 COMMAND

7.12.5 COMMAND_REPLIES

7.12.6 DATA

7.12.7 EHLO

7.12.8 HELO

7.12.9 HELP

7.12.10 MAIL

7.12.11 NOOP

7.12.12 OPEN_CONNECTION

7.12.13 OPEN_DATA

7.12.14 QUIT

7.12.15 RCPT

7.12.16 RSET

7.12.17 VRFY

7.12.18 WRITE_DATA

7.12.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 Ref Cursor Support

9.3 OCL Reference

9.3.1 Connect, Authorize and Initialize Functions

9.3.2 Handle and Descriptor Functions

9.3.3 Bind, Define and Describe Functions

9.3.4 Statement Functions

9.3.5 Transaction Functions

9.3.6 Date and Datetime Functions

9.3.7 Interval Functions

9.3.8 Number Functions

9.3.9 String Functions

9.3.10 Cartridge Services and File I/O Interface Functions

9.3.11 Miscellaneous Functions

9.3.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_POLICIES

10.9 ALL_SOURCE

10.10 ALL_SYNONYMS

10.11 ALL_TAB_COLUMNS

10.12 ALL_TABLES

10.13 ALL_TRIGGERS

10.14 ALL_TYPES

10.15 ALL_USERS

10.16 ALL_VIEW_COLUMNS

10.17 ALL_VIEWS

10.18 DBA_ALL_TABLES

10.19 DBA_CONS_COLUMNS

10.20 DBA_CONSTRAINTS

10.21 DBA_DB_LINKS

10.22 DBA_IND_COLUMNS

10.23 DBA_INDEXES

10.24 DBA_JOBS

10.25 DBA_OBJECTS

10.26 DBA_POLICIES

10.27 DBA_ROLE_PRIVS

10.28 DBA_ROLES

10.29 DBA_SOURCE

10.30 DBA_SYNONYMS

10.31 DBA_TABLES

10.32 DBA_TRIGGERS

10.33 DBA_TYPES

10.34 DBA_USERS

10.35 DBA_VIEWS

10.36 USER_ALL_TABLES

10.37 USER_CONS_COLUMNS

10.38 USER_CONSTRAINTS

10.39 USER_DB_LINKS

10.40 USER_IND_COLUMNS

10.41 USER_INDEXES

10.42 USER_JOBS

10.43 USER_OBJECTS

10.44 USER_POLICIES

10.45 USER_SOURCE

10.46 USER_SYNONYMS

10.47 USER_TAB_COLUMNS

10.48 USER_TABLES

10.49 USER_TRIGGERS

10.50 USER_TYPES

10.51 USER_USERS

10.52 USER_VIEW_COLUMNS

10.53 USER_VIEWS

11 Utilities

11.1 EDB*Plus

11.1.1 Starting EDB*Plus

11.1.2 Command Summary

11.2 EDB*Loader

11.2.1 Data Loading Methods

11.2.2 General Usage

11.2.3 EDB*Loader Control File

11.2.4 Invoking EDB*Loader

11.2.5 Direct Path Load

11.2.6 Parallel Direct Path Load

11.2.7 Remote Loading

11.3 EDB*Wrap

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 Table Partitioning

12.1 CREATE TABLE…PARTITION BY

12.2 ALTER TABLE...ADD PARTITION

12.3 ALTER TABLE… ADD SUBPARTITION

12.4 ALTER TABLE...DROP PARTITION

12.5 ALTER TABLE...DROP SUBPARTITION

12.6 ALTER TABLE...SPLIT PARTITION

12.7 ALTER TABLE...SPLIT SUBPARTITION

12.8 ALTER TABLE… TRUNCATE PARTITION

12.9 ALTER TABLE… TRUNCATE SUBPARTITION

12.10 ALTER TABLE… EXCHANGE PARTITION

12.11 ALTER TABLE… MOVE PARTITION

12.12 ALTER TABLE… RENAME PARTITION

12.13 Table Partitioning Views

12.13.1 ALL_PART_TABLES

12.13.2 ALL_TAB_PARTITIONS

12.13.3 ALL_TAB_SUBPARTITIONS

12.13.4 ALL_PART_KEY_COLUMNS

12.13.5 ALL_SUBPART_KEY_COLUMNS

13 ECPGPlus

13.1 C-preprocessor Directives

13.2 Supported C Data Types

13.3 Type Codes

13.4 The SQLDA Structure

13.5 ECPGPlus Statements

13.5.1 ALLOCATE DESCRIPTOR

13.5.2 CALL

13.5.3 CLOSE

13.5.4 COMMIT

13.5.5 CONNECT

13.5.6 DEALLOCATE DESCRIPTOR

13.5.7 DECLARE CURSOR

13.5.8 DECLARE DATABASE

13.5.9 DECLARE STATEMENT

13.5.10 DELETE

13.5.11 DESCRIBE

13.5.12 DESCRIBE DESCRIPTOR

13.5.13 DISCONNECT

13.5.14 EXECUTE

13.5.15 EXECUTE DESCRIPTOR

13.5.16 EXECUTE...END EXEC

13.5.17 EXECUTE IMMEDIATE

13.5.18 FETCH

13.5.19 FETCH DESCRIPTOR

13.5.20 GET DESCRIPTOR

13.5.21 INSERT

13.5.22 OPEN

13.5.23 OPEN DESCRIPTOR

13.5.24 PREPARE

13.5.25 ROLLBACK

13.5.26 SAVEPOINT

13.5.27 SELECT

13.5.28 SET CONNECTION

13.5.29 SET DESCRIPTOR

13.5.30 UPDATE

13.5.31 WHENEVER

14 System Catalog Tables

14.1.1 edb_dir

14.1.2 pg_synonym

14.1.3 edb_variable

14.1.4 product_component_version

15 Acknowledgements