EDB Postgres Advanced Server → 12 → User Guides → Database Compatibility for Oracle® Developer’s Guide Other versions of this page: 12 · 11 · 10 · 9.6 · 9.5
Subscribe to get advanced Postgres how-tos.
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