Recent posts

2 posts tagged with "database"

View All Tags

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

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.

Stored Procedures in Oracle vs PostgreSQL: Syntax & Usage Differences#

Diagram showing stored procedures architecture in Oracle and PostgreSQL

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#

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#

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#

Illustration representing debugging SQL procedures in Oracle vs PostgreSQL

Oracle Debugging: DBMS_OUTPUT and SQL Developer#

Example: Debugging with DBMS_OUTPUT#

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

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

Final Thoughts: Choosing and Debugging Stored Procedures Across Databases#

Visual summary of Oracle and PostgreSQL stored procedure differences

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

PostgreSQL Cursor Tutorial in Java: How to Use JDBC CallableStatement for Large Data

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 Is a Cursor in PostgreSQL? Understanding the Basics#

Illustration representing a database cursor concept with a question mark

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.

How to Create a Cursor Function in PostgreSQL#

PostgreSQL cursor function setup with SQL code snippet example

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.

Using JDBC CallableStatement to Retrieve PostgreSQL Cursor 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();
}
}
}

Java Code Explanation: How PostgreSQL Cursor Works with JDBC#

Java code walkthrough for fetching PostgreSQL cursor using JDBC CallableStatement

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.

Why and When to Use PostgreSQL Cursors in Java Applications#

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.

Conclusion: Best Practices for Using PostgreSQL Cursors in Java#

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.