4 posts tagged with "debugging"

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

April 8, 2025 · 4 min read

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#

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#

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

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#

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.

Handling Errors in C# the Easy Way

March 17, 2025 · 5 min read

Nida Sahar

You are aware that things don't always go as planned if you have ever dealt with C# or any type of online API. There are instances when you get strange JSON, when a field is missing, and when—well—things just break. The good news is that you don't have to let your app crash and burn because of such problems. We can apprehend them, record them, and continue on.

I'll demonstrate how to use a custom error response object to handle errors in C# in this post. It's similar to building a safety net for your software so that it doesn't go into full panic mode when something goes wrong.

Why Do We Care About Custom Error Responses?#

It's not always sufficient to simply log or print an error to the console when it occurs in your application. You may want to provide more information about the issue, track several faults that occur simultaneously, or simply deliver a kind, easy-to-read message to the user. A custom error answer can help with that.

With a custom error response object, you can:

Track different types of errors.
Organize your errors into categories (so you know if it's a JSON issue, a database issue, etc.).
Handle the error, log it, and then move on without crashing the app.

Setting Up Our Custom Error Object#

Let's start by setting up a basic error response object. This will hold our error messages in a dictionary, so we can track multiple types of errors.

Here's how you can do it:

public class ErrResponse
{
    public string Message { get; set; }
    public Dictionary<string, List<string>> Errors { get; set; }
}

Message: This is just a generic message about what went wrong.
Errors: This is a dictionary that'll hold all the different errors. Each key will represent an error type (like "JsonError" or "GeneralError"), and the value will be a list of error messages. This way, we can keep things organized.

Deserializing JSON and Handling Errors#

Let's say you're deserializing some JSON data, but there's a chance it could fail. Instead of letting the program crash, we can catch that error, store the details in our custom error response, and continue running. Here's how to do it:

using Newtonsoft.Json;
using System;
using System.Collections.Generic;

public class Program
{
    public static void Main()
    {
        string jsonContent = /* your JSON string here */;
        ErrResponse errResponse;

        try
        {
            // Try to deserialize the JSON
            errResponse = JsonConvert.DeserializeObject<ErrResponse>(jsonContent);

            if (errResponse != null)
            {
                Console.WriteLine("Deserialization successful.");
                Console.WriteLine($"Message: {errResponse.Message}");

                if (errResponse.Errors != null)
                {
                    foreach (var error in errResponse.Errors)
                    {
                        Console.WriteLine($"Error Key: {error.Key}, Values: {string.Join(", ", error.Value)}");
                    }
                }
            }
            else
            {
                Console.WriteLine("Deserialization resulted in a null response.");
            }
        }
        catch (JsonException ex)
        {
            // If JSON deserialization fails, log it
            errResponse = new ErrResponse
            {
                Message = "There was an issue with the JSON.",
                Errors = new Dictionary<string, List<string>>()
            };

            // Add the error to the "JsonError" category
            AddError(errResponse, "JsonError", ex.Message);
            AddError(errResponse, "JsonError", ex.StackTrace);

            Console.WriteLine($"JSON Deserialization error: {ex.Message}");
        }
        catch (Exception ex)
        {
            // Catch any other errors that might happen
            errResponse = new ErrResponse
            {
                Message = "Something unexpected went wrong.",
                Errors = new Dictionary<string, List<string>>()
            };

            // Log the general error
            AddError(errResponse, "GeneralError", ex.Message);
            AddError(errResponse, "GeneralError", ex.StackTrace);

            Console.WriteLine($"Unexpected error: {ex.Message}");
        }

        // Continue running the app, no matter what
        Console.WriteLine("The program keeps on running...");
    }

    // Utility to add errors to the response
    private static void AddError(ErrResponse errResponse, string key, string message)
    {
        if (string.IsNullOrEmpty(message)) return;

        if (errResponse.Errors.ContainsKey(key))
        {
            errResponse.Errors[key].Add(message);
        }
        else
        {
            errResponse.Errors[key] = new List<string> { message };
        }
    }
}

What's Going On Here?#

Deserialization: We attempt to create our ErrResponse object from the JSON. Fantastic if it does. If not, the error is detected.
Catching JSON Errors: If the JSON is incorrect, we detect it and use a JsonError value to add it to our Errors dictionary. The error notice and stack trace are then displayed for simpler debugging.
General Error Handling: We detect and record any unexpected events (such as database problems or network failures) under the GeneralError key.
Program Doesn't Crash: The software continues to operate after the problem has been handled. Thus, without ruining anything, you can log issues, alert someone, or simply go on.

Why This Is Useful#

It Keeps Things Neat: We store errors in an organised manner that makes it simple to see what's wrong, as opposed to simply throwing them around.
Multiple Errors? No Problem: We don't have to overwrite or overlook anything when we use a dictionary to track numerous faults at once.
No App Crashes: In the event that something breaks, the program continues to operate. You recognise the mistake, correct it, and move on with your life.

Conclusion#

Error management doesn't have to be difficult. You may effortlessly handle failures, record crucial information, and maintain the functionality of your program by utilising a custom error response object in C#. There are ways to deal with issues like a broken JSON string or an unplanned crash without everything exploding.

Therefore, bear in mind to identify the mistake, manage it politely, and continue working on your program the next time something goes wrong.

If you're looking for cutting-edge features for cloud deployment, check out what Oikos by Nife has to offer.

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

March 12, 2025 · 2 min read

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.

How to Debug PostgreSQL Stored Procedures: A Practical Guide

March 7, 2025 · 5 min read

Nida Sahar

Illustration of a secure database with a shield, cloud storage icons, and two people interacting with servers and files

When dealing with PostgreSQL, debugging stored procedures can be particularly challenging. The debugging process can initially seem intimidating, regardless of whether you have experience with Oracle or PostgreSQL. Don't worry, though; we'll explain it in a straightforward and useful manner that you may use for your own purposes.

Using a generic example of a PostgreSQL stored procedure, let's go over some possible problems you can run across and how to effectively debug them.

Step 1: Understanding the Example Stored Procedure#

Assume for the moment that you are working on a stored procedure that determines and returns the total sales for a specific product over a given period of time. This is a basic PostgreSQL stored procedure:

CREATE OR REPLACE FUNCTION calculate_sales(
    p_product_id INT,
    p_start_date DATE,
    p_end_date DATE,
    OUT total_sales NUMERIC
)
RETURNS NUMERIC AS $$
BEGIN
    -- Initialize the total_sales to 0
    total_sales := 0;

    -- Calculate total sales
    SELECT SUM(sale_amount) INTO total_sales
    FROM sales
    WHERE product_id = p_product_id
    AND sale_date BETWEEN p_start_date AND p_end_date;

    -- If no sales found, raise a notice
    IF total_sales IS NULL THEN
        RAISE NOTICE 'No sales found for the given parameters.';
        total_sales := 0;  -- Set total_sales to 0 if no sales found
    END IF;

    -- Return the result
    RETURN total_sales;
END;
$$ LANGUAGE plpgsql;

This stored procedure:

Takes in a product_id, start_date, and end_date as input parameters.
Returns the total sales for that product within the date range.
Uses the SUM() function to get the total sales from the sales table.
If no sales are found, it raises a notice and sets total_sales to 0.

Step 2: Common Issues and Errors in Stored Procedures#

Illustration of database analysis with two people working on laptops, large data charts, and a database stack in the background.

Some issues you might encounter include:

Null or Incorrect Parameter Values: Passing null or erroneous values for parameters can cause errors or unexpected results.
Incorrect Data Types: Ensure that parameters match the expected data types. Example: '2024-11-32' is an invalid date.
No Data Found: If there are no sales records for the given product ID and date range, SUM() will return NULL.
Cursors and Result Sets: Not handling cursors properly might result in memory issues when dealing with large datasets.

Step 3: Debugging Strategy#

Isometric illustration of a database server with a businessman retrieving a red book from a drawer filled with files, symbolizing data management.

1. Use `RAISE NOTICE` to Log Debugging Information#

Adding RAISE NOTICE statements helps log variable values and pinpoint issues.

CREATE OR REPLACE FUNCTION calculate_sales(
    p_product_id INT,
    p_start_date DATE,
    p_end_date DATE,
    OUT total_sales NUMERIC
)
RETURNS NUMERIC AS $$
BEGIN
    -- Log the input parameters
    RAISE NOTICE 'Product ID: %, Start Date: %, End Date: %', p_product_id, p_start_date, p_end_date;

    -- Initialize total_sales
    total_sales := 0;

    -- Calculate total sales
    SELECT SUM(sale_amount) INTO total_sales
    FROM sales
    WHERE product_id = p_product_id
    AND sale_date BETWEEN p_start_date AND p_end_date;

    -- Log the result
    RAISE NOTICE 'Total Sales: %', total_sales;

    -- Handle null case
    IF total_sales IS NULL THEN
        RAISE NOTICE 'No sales found for the given parameters.';
        total_sales := 0;
    END IF;

    -- Return the result
    RETURN total_sales;
END;
$$ LANGUAGE plpgsql;

2. Test the Function with Sample Data#

Run the following query with known data:

SELECT calculate_sales(123, '2024-01-01'::DATE, '2024-11-30'::DATE);

If the function fails, check the logs for RAISE NOTICE messages to identify issues.

3. Handle NULLs and Edge Cases#

Ensure SUM() correctly handles cases where no rows are found. We addressed this in the function by checking IF total_sales IS NULL THEN.

4. Validate Data Types#

p_product_id should be an integer.
p_start_date and p_end_date should be of type DATE.
Use explicit type conversions where necessary.

5. Monitor Performance#

If the function is slow, analyze the execution plan:

EXPLAIN ANALYZE
SELECT SUM(sale_amount)
FROM sales
WHERE product_id = 123
AND sale_date BETWEEN '2024-01-01' AND '2024-11-30';

This reveals whether PostgreSQL is utilizing indexes efficiently.

Step 4: Check the Logs#

Enable log in PostgreSQL by setting these in postgresql.conf:

log_statement = 'all'
log_duration = on

This helps in identifying slow queries and execution issues.

Conclusion#

Debugging PostgreSQL stored procedures doesn't have to be difficult. By following structured debugging techniques, testing with actual data, handling edge cases, and monitoring performance, you can quickly identify and fix issues.

Follow these steps:

Track values and verify inputs.
Test using known reliable data.
Handle special cases like NULLs.
Optimize queries using EXPLAIN ANALYZE.

By applying these strategies, you'll be able to debug PostgreSQL stored procedures efficiently.

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.

Recent posts

4 posts tagged with "debugging"

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

Syntax Differences#

Oracle (ORA):#

PostgreSQL (PG):#

Example: A Generic Stored Procedure#

Oracle (ORA) Example#

PostgreSQL (PG) Example#

Key Differences in Syntax#

2. Debugging Stored Procedures in ORA vs. PG#

Oracle (ORA) Debugging#

Example: Debugging with DBMS_OUTPUT#

PostgreSQL (PG) Debugging#

Example: Debugging with RAISE NOTICE#

3. Conclusion#

Further Reading:#

Why Do We Care About Custom Error Responses?#

Setting Up Our Custom Error Object#

Deserializing JSON and Handling Errors#

What's Going On Here?#

Why This Is Useful#

Conclusion#

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

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

Step 3: Get Interactive with TOAD's Debugger#

Step 4: Check Execution Plan#

Step 5: Logs, Logs, and More Logs#

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

TL;DR: Debugging Like a Boss#

Step 1: Understanding the Example Stored Procedure#

Step 2: Common Issues and Errors in Stored Procedures#

Step 3: Debugging Strategy#

1. Use RAISE NOTICE to Log Debugging Information#

2. Test the Function with Sample Data#

3. Handle NULLs and Edge Cases#

4. Validate Data Types#

5. Monitor Performance#

Step 4: Check the Logs#

Conclusion#

Example: Debugging with `DBMS_OUTPUT`#

Example: Debugging with `RAISE NOTICE`#

1. Use `RAISE NOTICE` to Log Debugging Information#