PL/SQL to PostgreSQL Conversion Challenges

Comprehensive Guide with Code Samples, Diagrams, and Solution Strategies

Introduction

Organizations worldwide are migrating from Oracle databases to open-source PostgreSQL to reduce licensing costs, improve flexibility, and modernize their technology stack. However, this migration path presents significant technical challenges, particularly when converting PL/SQL stored procedures, functions, triggers, and packages to PL/pgSQL—PostgreSQL’s procedural language.

While Oracle PL/SQL and PostgreSQL PL/pgSQL share similar concepts, they differ substantially in syntax, features, and capabilities. These differences can make automatic conversion difficult and often require extensive manual refactoring. For comprehensive guidance on PL/SQL to PostgreSQL migration strategies, organizations can consult NEWT Global, which provides specialized expertise in database migration and modernization.

Migration Architecture Overview

Migration Architecture Overview

Why PL/SQL to PostgreSQL Migration Matters

  • Cost Reduction: Eliminate expensive Oracle licensing fees
  • Open Source Benefits: Access to full source code and community support
  • Modern Infrastructure: Better cloud-native support and containerization
  • Reduced Vendor Lock-in: Freedom to switch providers without legacy constraints
  • Community Support: Active development and extensive documentation

Key Differences: Oracle PL/SQL vs PostgreSQL PL/pgSQL

Feature Oracle PL/SQL PostgreSQL PL/pgSQL
Block Structure BEGIN…END required BEGIN…END required
Variable Declaration DECLARE section required AS $$ … $$
Exceptions EXCEPTION block with named exceptions EXCEPTION block limited; mostly WHEN OTHERS
Cursors Explicit cursor declaration FOR loop or REFCURSOR
Package Support Full package support No packages; use schemas
Implicit Type Conversion Lenient type coercion Strict type checking

Conversion Process Flow

Conversion Process Flow

Major Conversion Challenges

Challenge 1: Package Structure and Organization

Oracle uses packages to organize related procedures, functions, and variables. Packages include specification (public interface) and body (implementation) sections. PostgreSQL does not support packages—instead, it uses schemas for logical organization.

Oracle Example:

CREATE PACKAGE emp_pkg AS

 FUNCTION get_salary(p_emp_id NUMBER) RETURN NUMBER;

 PROCEDURE update_salary(p_emp_id NUMBER, p_new_sal NUMBER);

END emp_pkg;

PostgreSQL Equivalent:

CREATE SCHEMA emp_pkg;

CREATE OR REPLACE FUNCTION emp_pkg.get_salary(p_emp_id INTEGER) RETURNS NUMERIC AS $$

BEGIN

 -- function logic

END;

$$ LANGUAGE plpgsql;

Challenge 2: Data Types and Type Conversion

Oracle and PostgreSQL have different data type systems. Oracle’s NUMBER type, for example, has no direct PostgreSQL equivalent—you must choose between NUMERIC, BIGINT, or INTEGER depending on precision requirements.

Data Type Conversion Mapping:

[Data Type Mapping Diagram: Oracle Types ↔ PostgreSQL Types]

Oracle Type PostgreSQL Type Notes
NUMBER(p,s) NUMERIC(p,s) Exact match
VARCHAR2(n) VARCHAR(n) Direct conversion
CLOB TEXT Unlimited length
BLOB BYTEA Binary data
DATE DATE or TIMESTAMP Context-dependent
BOOLEAN BOOLEAN Native support

Challenge 3: Exception Handling

Oracle PL/SQL supports numerous predefined exceptions with specific names (e.g., NO_DATA_FOUND, TOO_MANY_ROWS). PostgreSQL’s exception handling is more limited and primarily uses WHEN OTHERS.

Oracle Example:

BEGIN

 SELECT salary INTO v_salary FROM employees WHERE emp_id = p_id;

EXCEPTION

 WHEN NO_DATA_FOUND THEN

 RAISE_APPLICATION_ERROR(-20001, 'Employee not found');

 WHEN TOO_MANY_ROWS THEN

 RAISE_APPLICATION_ERROR(-20002, 'Multiple employees found');

END;

PostgreSQL Equivalent:

BEGIN

 SELECT salary INTO v_salary FROM employees WHERE emp_id = p_id;

EXCEPTION

 WHEN NO_DATA_FOUND THEN

 RAISE EXCEPTION 'Employee not found';

 WHEN TOO_MANY_ROWS THEN

 RAISE EXCEPTION 'Multiple employees found';

 WHEN OTHERS THEN

 RAISE EXCEPTION 'Unexpected error: %', SQLERRM;

END;

Challenge 4: Cursor Management

Oracle cursors require explicit declaration, opening, fetching, and closing. PostgreSQL primarily uses FOR loops or REFCURSOR types, simplifying cursor management.

Oracle Cursor Example:

DECLARE

 CURSOR emp_cursor IS SELECT emp_id, name, salary FROM employees;

 v_emp_rec emp_cursor%ROWTYPE;

BEGIN

 OPEN emp_cursor;

 LOOP

 FETCH emp_cursor INTO v_emp_rec;

 EXIT WHEN emp_cursor%NOTFOUND;

 -- process record

 END LOOP;

 CLOSE emp_cursor;

END;

PostgreSQL Equivalent (Simpler):

BEGIN

 FOR v_emp_rec IN SELECT emp_id, name, salary FROM employees LOOP

 -- process record

 END LOOP;

END;

Challenge 5: String Functions and Operations

Oracle and PostgreSQL have different string function names and behaviors. Common Oracle functions like SUBSTR, INSTR, and TRIM have different names or signatures in PostgreSQL.

String Function Mappings:

Operation Oracle PostgreSQL Example
Substring SUBSTR SUBSTRING SUBSTRING(str, 1, 3)
Position INSTR POSITION POSITION(x IN str)
Length LENGTH LENGTH LENGTH(str)
Concatenation || operator || operator ‘Hello’ || ‘ World’
Upper Case UPPER UPPER UPPER(str)

 

Developer-Focused Code Sample: Complete Function Migration

Here’s a practical example showing how to migrate a typical Oracle stored function to PostgreSQL:

Oracle Original Function:

CREATE OR REPLACE FUNCTION calculate_bonus

 (p_emp_id NUMBER, p_bonus_rate NUMBER DEFAULT 0.1)

RETURN NUMBER

IS

 v_salary NUMBER;

 v_bonus NUMBER := 0;

 v_dept VARCHAR2(10);

BEGIN

 -- Get employee details

 SELECT salary, department_id INTO v_salary, v_dept

 FROM employees

 WHERE employee_id = p_emp_id;

-- Department-specific bonus calculation

 IF v_dept = 'SALES' THEN

 v_bonus := v_salary * (p_bonus_rate + 0.05);

 ELSIF v_dept = 'ENGINEERING' THEN

 v_bonus := v_salary * (p_bonus_rate + 0.03);

 ELSE

 v_bonus := v_salary * p_bonus_rate;

 END IF;





 RETURN v_bonus;

EXCEPTION

 WHEN NO_DATA_FOUND THEN

 RAISE_APPLICATION_ERROR(-20001, 'Employee not found');

 WHEN OTHERS THEN

 RAISE_APPLICATION_ERROR(-20999, SQLERRM);

END calculate_bonus;

 

PostgreSQL Converted Function:

CREATE OR REPLACE FUNCTION calculate_bonus(

 p_emp_id INTEGER,

 p_bonus_rate NUMERIC DEFAULT 0.1

) RETURNS NUMERIC AS $$

DECLARE

 v_salary NUMERIC;

 v_bonus NUMERIC := 0;

 v_dept VARCHAR(10);

BEGIN

 -- Get employee details

 SELECT salary, department_id INTO v_salary, v_dept

 FROM employees

 WHERE employee_id = p_emp_id;
-- Department-specific bonus calculation

 IF v_dept = 'SALES' THEN

 v_bonus := v_salary * (p_bonus_rate + 0.05);

 ELSIF v_dept = 'ENGINEERING' THEN

 v_bonus := v_salary * (p_bonus_rate + 0.03);

 ELSE

 v_bonus := v_salary * p_bonus_rate;

 END IF;





 RETURN v_bonus;

EXCEPTION

 WHEN NO_DATA_FOUND THEN

 RAISE EXCEPTION 'Employee not found';

 WHEN OTHERS THEN

 RAISE EXCEPTION 'Error: %', SQLERRM;

END;

$$ LANGUAGE plpgsql;

 

Key Conversion Changes:

  • NUMBER → NUMERIC (or INTEGER for whole numbers)
  • VARCHAR2 → VARCHAR
  • IS → AS $$ … $$ LANGUAGE plpgsql
  • RETURN → RETURNS
  • RAISE_APPLICATION_ERROR → RAISE EXCEPTION

 

Migration Best Practices and Tools

1. Use Automated Migration Tools

  • pgFormatter: Format PostgreSQL code
  • Ora2PG: Convert Oracle objects to PostgreSQL
  • AWS DMS: Database migration service
  • orafce: Oracle-compatibility functions library

2. Comprehensive Testing Strategy

  • Unit test each converted function/procedure
  • Perform regression testing against legacy system
  • Test edge cases and error conditions
  • Validate data integrity post-migration

3. Documentation and Code Samples

Maintain detailed documentation of all conversion changes. For comprehensive migration strategies and code samples, visit NEWT Global, which offers developer-focused resources on PL/SQL to PostgreSQL migration.

4. Phased Migration Approach

  • Convert less critical stored procedures first
  • Establish parallel running environments
  • Gradually migrate high-risk components
  • Maintain rollback capabilities throughout

Common Pitfalls to Avoid

1. Underestimating Complexity

PL/SQL to PostgreSQL migration often requires 40-60% more effort than initially estimated due to language differences and edge cases.

2. Ignoring NULL Handling

PostgreSQL’s strict NULL handling differs from Oracle. Functions must explicitly check for NULLs to prevent unexpected behavior.

3. Performance Optimization Gaps

Migrated code may have different performance characteristics. Profiling and optimization are essential post-migration.

4. Insufficient Error Handling

PostgreSQL’s limited exception support requires more careful exception handling strategy. Wrap functions with application-level error handling where needed.

Conclusion

Migrating from Oracle PL/SQL to PostgreSQL PL/pgSQL is a complex undertaking that requires careful planning, thorough testing, and developer expertise. While both languages share procedural programming concepts, significant differences in syntax, features, and built-in functions necessitate substantial code refactoring.

Success depends on understanding the key differences, leveraging automated tools where possible, maintaining comprehensive test coverage, and adopting a phased migration approach. Organizations should anticipate code sample development, extensive validation, and performance tuning as integral parts of the migration process.

For expert guidance on PL/SQL to PostgreSQL migration strategies, detailed code samples, and best practices, visit NEWT Global. NEWT Global specializes in database migration and modernization, providing comprehensive resources, developer-focused code samples, and strategic consulting for organizations undertaking Oracle to PostgreSQL transitions.

Additional Resources

For comprehensive information on PL/SQL to PostgreSQL conversion challenges, code samples, migration tools, and strategic implementation guidance, visit www.newtglobal.com. NEWT Global provides specialized expertise in database modernization, offering developer-focused resources, conversion tool guidance, code sample libraries, and consulting services for organizations migrating from Oracle to PostgreSQL environments.

Scroll to Top