Oracle vs PostgreSQL Stored Procedures: Syntax, Debugging & Examples Compared

Stored procedures are an essential component of relational databases that help with logic encapsulation, performance improvement, and process automation. Both Oracle (ORA) and PostgreSQL (PG) include stored procedure functionality, however they are very different from each other. The distinctions between PG and ORA stored procedures as well as debugging techniques will be covered in this blog post. A simple general example will be provided to demonstrate these distinctions.

Stored Procedures in Oracle vs PostgreSQL: Syntax & Usage Differences

Despite being compatible with both ORA and PG, stored procedures differ significantly in terms of syntax, functionality, and debugging techniques. Let's look at the primary differences:

Syntax Comparison: PL/SQL vs PL/pgSQL

Oracle (ORA):

Oracle stored procedures are typically created using the CREATE PROCEDURE command and utilize PL/SQL, a procedural extension of SQL. They explicitly use IN, OUT, and IN OUT parameters and are wrapped in a BEGIN...END block.

PostgreSQL (PG):

PostgreSQL uses PL/pgSQL for stored procedures and functions, which is similar to Oracles PL/SQL but differs in syntax and capabilities. In PG:

• Stored procedures are created using CREATE PROCEDURE (introduced in version 11).
• Functions are created using CREATE FUNCTION.
• Unlike Oracle, PG does not support IN OUT parameters.

Example: A Generic Stored Procedure

The following example determines whether a case belongs to a particular receiver type and sets an output flag appropriately.

Oracle Example: Procedure Using PL/SQL

CopyCREATE OR REPLACE PROCEDURE check_case_in_fips_othp(
    p_case_id IN VARCHAR,
    p_flag OUT CHAR,
    p_msg OUT VARCHAR
) AS
BEGIN
    SELECT 'S' INTO p_flag
    FROM disbursements
    WHERE case_id = p_case_id
      AND recipient_type IN ('FIPS', 'OTHP');
  
    IF p_flag IS NULL THEN
        p_flag := 'N';
        p_msg := 'No records found';
    END IF;
  
EXCEPTION
    WHEN OTHERS THEN
        p_flag := 'F';
        p_msg := 'Error: ' || SQLERRM;
END check_case_in_fips_othp;

PostgreSQL Example: Procedure Using PL/pgSQL

CopyCREATE OR REPLACE PROCEDURE check_case_in_fips_othp(
    IN p_case_id VARCHAR,
    OUT p_flag CHAR,
    OUT p_msg VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Check if case exists
    SELECT 'S' INTO p_flag
    FROM disbursements
    WHERE case_id = p_case_id
      AND recipient_type IN ('FIPS', 'OTHP')
    LIMIT 1;
  
    IF NOT FOUND THEN
        p_flag := 'N';
        p_msg := 'No records found';
    END IF;
  
EXCEPTION
    WHEN OTHERS THEN
        p_flag := 'F';
        p_msg := 'Error: ' || SQLERRM;
END;
$$;

Syntax & Behavior Differences: ORA vs PG

• Procedure Declaration: Oracle explicitly defines IN, OUT, IN OUT parameter modes, whereas PostgreSQL only uses IN or OUT.
• Exception Handling: Oracle uses EXCEPTION blocks with WHEN OTHERS THEN SQLERRM to capture errors, while PostgreSQL mainly relies on RAISE EXCEPTION.
• Logic for No Data: Oracle explicitly checks for NULL, while PostgreSQL uses the FOUND condition.

Debugging Stored Procedures in Oracle and PostgreSQL

Oracle Debugging: DBMS_OUTPUT and SQL Developer

• Use DBMS_OUTPUT.PUT_LINE to output debugging messages.
• Handle exceptions using WHEN OTHERS THEN and log errors to custom tables.
• Use Oracle SQL Developer for interactive debugging.

Example: Debugging with DBMS_OUTPUT

CopyDBMS_OUTPUT.PUT_LINE('The case flag is: ' || p_flag);

PostgreSQL Debugging: RAISE NOTICE and Logging

• Use RAISE NOTICE for debugging output.
• Handle exceptions using RAISE EXCEPTION and log errors to a dedicated table.
• PostgreSQL lacks an integrated debugger like Oracle SQL Developer, so debugging relies on logging and manual testing.

Example: Debugging with RAISE NOTICE

CopyRAISE NOTICE 'The case flag is: %', p_flag;

Final Thoughts: Choosing and Debugging Stored Procedures Across Databases

Despite having strong stored procedure functionality, Oracle and PostgreSQL differ greatly in syntax, error management, and debugging techniques. Heres a quick recap:

• Syntax: Oracle explicitly defines IN OUT, OUT modes; PostgreSQL only uses IN and OUT.
• Exception Handling: Oracle uses SQLERRM, while PostgreSQL relies on RAISE EXCEPTION.
• Debugging: Oracle has more integrated tools like DBMS_OUTPUT, whereas PostgreSQL depends on RAISE NOTICE and logging.

By understanding these differences and using effective debugging techniques, you can become a more productive developer when working with Oracle or PostgreSQL stored procedures.

For deploying and managing databases efficiently, check out Nife.io, a cutting-edge platform that simplifies database deployment and scaling.

learn more about Database deployment Guide.

Further Reading:

• Oracle PL/SQL Documentation
• PostgreSQL PL/pgSQL Documentation
• Oracle SQL Developer Debugging Guide
• PostgreSQL Error Handling

Deploy on Nife

Ready to deploy? Check out the Nife documentation:

• Deploy PostgreSQL with nifectl