Stored procedures are an essential component of relational databases that help with logic encapsulation, performance improvement, and process automation. Both [Oracle (ORA)](https://www.oracle.com/database/) and [PostgreSQL (PG)](https://www.postgresql.org/) 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 <img src={require('./img/7495401.jpg').default} alt="Diagram showing stored procedures architecture in Oracle and PostgreSQL" width="500" height="350"/> <br/> 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/index.html), 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](https://www.postgresql.org/docs/current/plpgsql.html) 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 ```sql CREATE 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 ```sql CREATE 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 <img src={require('./img/11617929.jpg').default} alt="Illustration representing debugging SQL procedures in Oracle vs PostgreSQL" width="500" height="350"/> <br/> ### Oracle Debugging: DBMS_OUTPUT and SQL Developer - Use [`DBMS_OUTPUT.PUT_LINE`](https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_output.htm) to output debugging messages. - Handle exceptions using `WHEN OTHERS THEN` and log errors to custom tables. - Use [Oracle SQL Developer](https://www.oracle.com/database/sqldeveloper/) for interactive debugging. #### **Example:** Debugging with `DBMS_OUTPUT` ```sql DBMS_OUTPUT.PUT_LINE('The case flag is: ' || p_flag); ``` ### PostgreSQL Debugging: RAISE NOTICE and Logging - Use [`RAISE NOTICE`](https://www.postgresql.org/docs/current/plpgsql-errors-and-messages.html) 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` ```sql RAISE NOTICE 'The case flag is: %', p_flag; ``` ## Final Thoughts: Choosing and Debugging Stored Procedures Across Databases <img src={require('./img/20943495.jpg').default} alt="Visual summary of Oracle and PostgreSQL stored procedure differences" width="500" height="350"/> <br/> 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](https://nife.io/), a cutting-edge platform that simplifies database deployment and scaling. learn more about [Database deployment Guide](https://docs.nife.io/docs/UI-Guide/database). ### **Further Reading:** - [Oracle PL/SQL Documentation](https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/index.html) - [PostgreSQL PL/pgSQL Documentation](https://www.postgresql.org/docs/current/plpgsql.html) - [Oracle SQL Developer Debugging Guide](https://docs.oracle.com/en/database/oracle/sql-developer/) - [PostgreSQL Error Handling](https://www.postgresql.org/docs/current/ecpg-errors.html)
