Recent posts

2 posts tagged with "database"

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 Handle PostgreSQL Cursors in Java: A Practical Guide

Nida Sahar

It could be a little difficult to use PostgreSQL cursors, especially if you need to use them in Java applications. If you've worked with relational databases and experimented with PL/SQL (Oracle's procedural language), you might recognise cursors. On the other hand, PostgreSQL handles and returns cursors in a different way.

This blog post will show you how to programmatically retrieve cursor data, interact with PostgreSQL cursors in Java, and give some real-world examples.

What's a Cursor Anyway?#

An illustration of a computer cursor with a question mark, representing the concept

Using a cursor, which is essentially a pointer, you can get rows from a query one at a time or in batches without putting the entire result set into memory all at once. Think of it as a way to handle large datasets without overtaxing your computer.

In a database, you often get all of the results at once when you run a query. By chunking the data or fetching rows at a time, a cursor can handle large amounts of data, improving performance and resource management.

It becomes interesting when you want to handle it in Java since a PostgreSQL method can return a cursor.

Setting Up a Cursor in PostgreSQL#

Step-by-step guide on setting up a cursor in PostgreSQL with example code

Lets start with a PostgreSQL function that returns a cursor. Well assume you have a table called employees with columns like employee_id, first_name, and salary. Heres a basic function that opens a cursor for this table:

CREATE OR REPLACE FUNCTION get_employee_cursor()
RETURNS REFCURSOR AS $$
DECLARE
emp_cursor REFCURSOR;
BEGIN
OPEN emp_cursor FOR
SELECT employee_id, first_name, salary
FROM employees;
RETURN emp_cursor;
END;
$$ LANGUAGE plpgsql;

This function get_employee_cursor opens a cursor for a simple SELECT query on the employees table and returns it.

How to Fetch the Cursor Data in Java#

To communicate with the database in Java, we can utilize JDBC (Java Database Connectivity). Because the function that returns the cursor is a callable function, you must use a CallableStatement when working with cursors in PostgreSQL. Here's how to accomplish that:

import java.sql.*;
public class CursorExample {
public static void main(String[] args) {
// Database connection details
String url = "jdbc:postgresql://localhost:5432/your_database";
String user = "your_user";
String password = "your_password";
try (Connection connection = DriverManager.getConnection(url, user, password)) {
// Enable transactions (required for cursors in PostgreSQL)
connection.setAutoCommit(false);
// Step 1: Call the function that returns a cursor
try (CallableStatement callableStatement = connection.prepareCall("{ ? = call get_employee_cursor() }")) {
callableStatement.registerOutParameter(1, Types.OTHER); // Cursor is of type "OTHER"
callableStatement.execute();
// Step 2: Retrieve the cursor
ResultSet resultSet = (ResultSet) callableStatement.getObject(1);
// Step 3: Iterate through the cursor and display results
while (resultSet.next()) {
int employeeId = resultSet.getInt("employee_id");
String firstName = resultSet.getString("first_name");
double salary = resultSet.getDouble("salary");
System.out.printf("Employee ID: %d, Name: %s, Salary: %.2f%n", employeeId, firstName, salary);
}
// Close the ResultSet
resultSet.close();
}
// Commit the transaction
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

Breaking Down the Code#

A step-by-step breakdown of code logic with annotations

Connection Setup#

  • We connect to PostgreSQL using the DriverManager.getConnection() method.
  • connection.setAutoCommit(false) is crucial because cursors in PostgreSQL work within a transaction. By disabling auto-commit, we ensure the transaction is handled properly.

Calling the Cursor-Returning Function#

  • We use a CallableStatement to execute the function get_employee_cursor(), which returns a cursor. This is similar to calling a stored procedure in other databases.
  • We register the output parameter (the cursor) using registerOutParameter(1, Types.OTHER). In JDBC, cursors are treated as Types.OTHER.

Fetching Data from the Cursor#

  • Once the cursor is returned, we treat it like a ResultSet. The cursor essentially acts like a pointer that we can iterate over.
  • We loop through the result set using resultSet.next() and retrieve the data (like employee_id, first_name, and salary).

Commit the Transaction#

  • Since the cursor is part of a transaction, we commit the transaction after were done fetching and processing the data.

When Would You Use Cursors in Java?#

Managing Big Data Sets#

It could take a lot of memory to load all of your records at once if you have a lot of them—millions, for instance. By retrieving the data in chunks via a cursor, you may conserve memory.

Performance Optimization#

For large result sets, it is usually more efficient to fetch data in batches or row by row, which lessens the strain on your database and application.

Streaming Data#

Using cursors to get and process data in real time is a smart strategy when working with streams.

Final Thoughts#

Although using Java cursors in PostgreSQL might seem a bit more difficult than in Oracle, massive data sets can be efficiently managed with the right approach. By utilising CallableStatement to obtain the cursor and iterating over the result set, you may make full use of Java's cursors without encountering memory or performance issues.

Regardless of whether you're working with large datasets or need more exact control over how data is pulled from the database, cursors are a helpful addition to any PostgreSQL toolbox. Just be aware that, unlike Oracle, PostgreSQL requires the explicit retrieval of cursor data, but it is easy to comprehend and effective once you do.

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.

For more details, check out the official PostgreSQL documentation on Cursors.