Recent posts

3 posts tagged with "oracle"

View All Tags

Comparing and Debugging ORA vs. PG Stored Procedures: A Generic Example

Nida Sahar

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.

1. Stored Procedures in Oracle vs. PostgreSQL: Key Differences#

Stored Procedures in Oracle

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 Differences#

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 (ORA) Example#

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 (PG) Example#

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;
$$;

Key Differences in Syntax#

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

2. Debugging Stored Procedures in ORA vs. PG#

Image representing debugging of stored procedures

Oracle (ORA) Debugging#

Example: Debugging with DBMS_OUTPUT#

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

PostgreSQL (PG) Debugging#

  • 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#

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

3. Conclusion#

conclusion

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:#

How to Debug Stored Procedures Like a Pro (Without Pulling Your Hair Out)

Nida Sahar

Illustration of a person working on a computer with a large database stack in the background, representing database management and optimization.

Step 1: Catch the Error (Without Losing Your Cool)#

Oracle includes built-in features for error detection. Use RAISE NOTICE to print error messages.

EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'Error in procedure %: %', SQLERRM;
RETURN 'F'; -- Failure indicator

This prints errors directly to TOAD's Output window.

Step 2: Test with Real Data (Not Just Theoretical Inputs)#

Illustration of a blue database icon with a circular synchronization symbol, representing database backup, restore, or synchronization.

Use actual data, including edge cases like invalid IDs or NULL values, to properly debug your stored procedure.

Step 3: Get Interactive with TOAD's Debugger#

TOAD provides a powerful interactive debugger:

  • Open SQL Editor: Load your stored procedure.
  • Set Breakpoints: Click on line numbers where issues might exist.
  • Start Debugging: Right-click the procedure name and select Debug.
  • Watch Variables: Monitor values in real time in the Watch window.

Step 4: Check Execution Plan#

For performance issues, use TOAD's Explain Plan feature:

EXPLAIN PLAN FOR
SELECT * FROM users WHERE status = 'Active';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

This helps identify bottlenecks and optimize queries.

Step 5: Logs, Logs, and More Logs#

Ensure important details are logged for future debugging:

DBMS_OUTPUT.PUT_LINE('Procedure started for user: ' || user_id);

Step 6: Have a Code Review (or Just Ask Google)#

If stuck, seek help from Google, Stack Overflow, or a colleague for fresh perspectives.

TL;DR: Debugging Like a Boss#

Isometric illustration of a computer setup with a cloud database, security icons, and a tablet on a futuristic purple-themed desk, representing cloud computing and data management.
  • Use RAISE NOTICE to print errors.
  • Test with real data.
  • Step through the code using TOAD Debugger.
  • Analyze the execution plan for slow queries.
  • Log errors for detailed tracking.
  • Google it (really, it works!).

Debugging stored procedures may not be fun, but with these tips, you'll solve issues faster and with less frustration.

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.

From ORA to PG: A Casual Guide to Converting Stored Procedures

Nida Sahar

From ORA to PG

Changing to PostgreSQL (PG) from Oracle (ORA)? One of those things that can be annoying, but rewarding when done correctly, is converting stored processes. It's similar to untangling your earbuds. Don't worry if you're new to translating from PL/SQL to PL/pgSQL; I've got you covered. We'll discuss how to do it, what to look out for, and how to maintain your sanity.

Why the Conversion?#

Let's address the question of why this even exists before we get started. You might be switching to an open-source stack. Or perhaps you've finally fallen in love with PostgreSQL because of its cost-effectiveness, flexibility, and performance. For whatever reason, the true challenge is to bridge the gap between the PL/pgSQL world of PostgreSQL and the peculiarities of Oracle's PL/SQL.

The Oracle-to-PostgreSQL "Language Barrier"#

Consider PostgreSQL and Oracle as two cousins who were raised in different countries. Despite speaking different dialects, they share a lot of similarities. However, you'll encounter the following significant differences:

1. Syntax Tweaks#

  • Oracle's %TYPE? Nope, PostgreSQL doesn't do that. You'll need to replace it with DECLARE variable_name variable_type;.
  • PL/SQL's BEGIN…END? Slightly different in PostgreSQL, where you'll use DO $$ ... $$ for anonymous code blocks.

2. Cursors and Loops#

  • SYS_REFCURSOR: If you love SYS_REFCURSOR in Oracle, prepare for a little re-learning. PostgreSQL has cursors too, but they work differently. Loops? Still there, just with a different flavor.

3. Exception Handling#

  • Exception Blocks: Oracle uses EXCEPTION blocks, while PostgreSQL uses EXCEPTION WHEN. Same idea, different syntax.

4. Data Types#

  • Data Types: Oracle's NUMBER, VARCHAR2, and CLOB all need PostgreSQL translations like NUMERIC, TEXT, etc. PostgreSQL is more particular, so be ready for type mismatches.

The Conversion Playbook#

Here's the game plan for converting an Oracle stored procedure to PostgreSQL:

1.Break It Down:#

Start by breaking the procedure into smaller pieces. Look for cursors, loops, and exception blocks—they usually need the most attention.

2. Map the Data Types:#

Check every variable and parameter for type differences. Got an OUT parameter in Oracle? PostgreSQL's got OUT too—it's just slightly different in usage.

3. Rewrite the Syntax:#

Replace Oracle-specific features with their PostgreSQL equivalents. For example, swap %TYPE for explicit type declarations, or convert IF … THEN structures to PostgreSQL's flavor.

4.Debug Like a Pro:#

PostgreSQL isn't shy about throwing errors. Use RAISE NOTICE to log variable values and track execution flow during debugging.

Tools to Save Your Day#

Everything doesn't have to be done by hand! A large portion of the conversion can be automated with programs like Ora2Pg. They will get you started, but they won't do everything, particularly for complicated processes.

You might also consider other tools, like:

Debugging: Your New Best Friend#

Debugging is your lifeline when things go wrong, which they will. The RAISE NOTICE feature in PostgreSQL is ideal for monitoring internal operations. Record everything, including dynamic SQL statements, loop counts, and variables.

To help you get started, here is an example snippet:

DO $$
DECLARE
counter INTEGER := 0;
BEGIN
FOR counter IN 1..10 LOOP
RAISE NOTICE 'Counter value: %', counter;
END LOOP;
END $$;

Testing for Functional Equivalence#

Are you curious as to whether your PostgreSQL method is acting similarly to the Oracle one? Create a couple of test cases. Construct input scenarios and contrast Oracle with PostgreSQL's outcomes. Comparing two maps to make sure you're not lost is analogous to that.

Performance Pitfalls#

Test the performance after conversion. Although PostgreSQL has a strong query planner, indexing or query modifications may be necessary to match Oracle's speed. Remember to evaluate and adjust your PG queries. check out the PostgreSQL Performance Tips Guide.

Wrapping It Up#

It takes more than just copying and pasting to convert Oracle stored procedures to PostgreSQL. It's about recognizing the distinctions, accepting PostgreSQL's peculiarities, and ensuring that the code functions flawlessly. It's a learning curve, certainly, but it's also a chance to develop your abilities and appreciate PostgreSQL's vast ecosystem. Are you stuck somewhere? I enjoy debugging a good stored procedure mess, so let me know!