https://www.migops.com/blog/2021/10/14/stored-procedure-out-parameters-in-postgresql-14/

Skip to content
[MigOps-logo-120x]

  * Home
  * Blog
  * Announcements
  * ServicesMenu Toggle
      + Migration Assessment
      + Migration to PostgreSQL
      + Oracle to PostgreSQL Migration
      + PostgreSQL Trainings
      + Remote Database Administrator
  * About Us
  * Careers
  * Contact Us

[MigOps-logo-120x]
Main Menu

Stored Procedure OUT Parameters in PostgreSQL 14

Migrations to PostgreSQL, Oracle to PostgreSQL, PostgreSQL / By Akhil
Reddy Banappagari / October 14, 2021 October 14, 2021 / Oracle to
PostgreSQL, PostgreSQL, PostgreSQL 14 / Leave a Comment

PostgreSQL global development group generally announces a new release
every year, with several new features. With an increase in the number
of contributions and contributors, the new version PostgreSQL 14
released on September 30th, 2021, with several important features. 
As a developer and a database migration expert, I am happy to say
that there are several developer friendly features with PostgreSQL 14
. In this article, I would like to discuss and demonstrate about
Stored Procedure OUT parameters in PostgreSQL 14. Developers
migrating databases to PostgreSQL can utilize this feature to reduce
the overall time involved in making application level changes
instead. Let us now understand stored procedure out parameters in
PostgreSQL 14. 

Parameters supported with previous releases of PostgreSQL

In earlier versions of PostgreSQL, PROCEDURE only supported (1) IN,
(2)IN OUT parameters. While migrating from Oracle to PostgreSQL, we
had to convert all the OUT parameters in Oracle as IN OUT parameters
in PostgreSQL. In addition to that. we had to set the argument value
while calling the procedure from a Java application. For this reason,
in earlier releases of PostgreSQL, we had to not only perform changes
to a PostgreSQL PROCEDURE definition but also some application code
level changes.

PostgreSQL 14 adds support for OUT parameters in Stored Procedures

Starting from PostgreSQL 14, the OUT parameter support has been added
for Stored procedures. This is a good news for one of our customers
migrating from Oracle to PostgreSQL, as we don't have to make any
huge changes to the procedure definition or the application code.
PostgreSQL 14 supports OUT parameters for Stored Procedures

The original requirement of our customer was to reduce the
application level changes while porting to PostgreSQL. While this
feature appears to satisfy such requirements, one may come across an
error while calling the procedure with OUT parameters from a Java
application.

org.postgresql.util.PSQLException: ERROR: invalid input syntax for
type integer: "null" Where: unnamed portal parameter $1 = '...'

Before discussing the solution to the above error, let me demonstrate
a scenario to explain the behaviour in Oracle and upon conversion to
PostgreSQL. Consider an example Oracle procedure as following - 

CREATE OR replace PROCEDURE Test_proc(a OUT INT)
IS
  result INT;
BEGIN
    result := 24;
    a := result;
END;

The application code used to call this Oracle procedure appears as
following.

CallableStatement cs = connection.prepareCall("call test_proc(?)");
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
System.out.println(cs.getInt(1));

Following is the output we see upon calling this Oracle procedure
with OUT parameter. 

Oracle Output
-------------
24

Now, let us convert the above mentioned Oracle procedure to
PostgreSQL. As I am using PostgreSQL 14, i can specify the OUT
procedure. Thus, the converted PostgreSQL stored procedure appears as
follows. 

CREATE OR replace PROCEDURE test_proc(a OUT INT)
AS
$
DECLARE
    result INT;
BEGIN
    result:= 24;
    a:=result;
END;
$ LANGUAGE plpgsql;

Following is the application code that is used to call the PostgreSQL
(14) procedure.

CallableStatement cs = connection.prepareCall("call test_proc(?)");
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
System.out.println(cs.getInt(1));

We now see an error in the output as seen in the following log. 

PostgreSQL Output
-----------------
org.postgresql.util.PSQLException: ERROR: invalid input syntax for type integer: "null"
Where: unnamed portal parameter $1 = '...'

If you notice carefully, we have used the same application code
against PostgreSQL and it resulting in an error. This is because, the
application is calling the procedure via PrepareCall by not passing
any arguments to it. When I tested the PostgreSQL behavior by using
an anonymous code block as seen in the following code block, it works
as expected. 

postgres=# DO
$
DECLARE
result int;
BEGIN
CALL test_proc(result);
RAISE NOTICE '%', result;
END
$;
NOTICE:  24
DO

But to achieve the same behavior in the application, we also need to
pass some value as an argument value. As the parameter is an OUT
parameter, we do not need to pass any variable, so we can simply pass
a null value as argument. So, we can call the procedure using CALL
test_proc(null), which should also be giving the desired results.

As seen in the following log, I have set the 1st argument value as
null in the application code and I could now see the desired Oracle
behavior.

CallableStatement cs = connection.prepareCall("call test_proc(?)");
cs.setNull(1,Types.NULL);
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
System.out.println(cs.getInt(1));

Following is the output as expected.

PostgreSQL Output
-----------------
24

Yay! Finally, we got the desired output as seen with Oracle but with
a minor change in the application code but without any special
changes to the converted PostgreSQL stored procedure.

Conclusion

While migrating an application from Oracle to PostgreSQL, it may not
be always possible to avoid application code level changes.
PostgreSQL 14 has definitely addressed some of the major challenges
for developers and admins. But an important point to note is that the
code that worked for Oracle may not directly work for PostgreSQL.
Sometimes, we have to follow the PostgreSQL style which could lead
into modifying or adding a few lines of code to the application for
portability to PostgreSQL. However, this should never be considered
as huge challenge when you have experienced migration experts
supporting you during migrations. If you are looking to migrate to
PostgreSQL or get an estimated amount of time and complexity involved
in migrating your databases, please contact us today or send us an
email to sales@migops.com.

If you are looking for any of the following services offered by 
MigOps, please submit the following form today and one of our experts
will be in touch with you. 

Please enable JavaScript in your browser to complete this form.

  * [ ]Need an experts advice while migrating to PostgreSQL ?
  * [ ]Need support in Optimizing your PostgreSQL databases ?
  * [ ]Looking to get your team trained in PostgreSQL ?
  * [ ]Are you looking for a Remote Database Adminitrator to setup
    your PostgreSQL databases for Production ?

Please share your email address and we will contact you soon *
[                    ]
Submit
Post navigation

- Previous Post

About The Author

[74e3db72b2f76]

Akhil Reddy Banappagari

Akhil Reddy is currently working as a Developer at MigOps. He is a
skilled programmer with expertise in developing applications in Java
and Python. Before joining MigOps, Akhil worked on developing Android
and Web applications and some more development projects. With a
passion towards designing efficient algorithms and robust tools, he
joined MigOps and started developing tools to smoothen database
migration journey. Furthermore, Akhil's expertise on PL/SQL and
migrations has helped several customers migrate from Oracle to
PostgreSQL.

Leave a Comment Cancel Reply

Your email address will not be published.

           [                                             ] 
           [                                             ] 
           [                                             ] 
           [                                             ] 
           [                                             ] 
           [                                             ] 
           [                                             ] 
Type here..[                                             ] 
[                    ]

Name*[                              ]

Email*[                              ]

Website[                              ]

[ ] Save my name, email, and website in this browser for the next
time I comment.

[Post Comment >>] 

Exploring options for migrating Oracle or other databases to
PostgreSQL ?

Migration Assessment

Looking to Optimize PostgreSQL Performance ?

Performance Tuning

Simple parameter tuning can reduce your PostgreSQL bills on Cloud.

Schedule a call to know how ?

Looking for a few hours of Consulting or Support during Migrations ?

Purchase Consulting Hours

Want to get connected on PostgreSQL latest updates, Performance tips
and Migrations to PostgreSQL, subscribe to our notifications.

Subscribe to notifications

WE ARE HIRING!!

Interested PostgreSQL database administrators & developers can apply
now.

Apply Now

Need help? Drop an email to sales@migops.com

[MigOps-logo-120x121]

MigOps provides services for Migrations to PostgreSQL and Cloud.

Services

  * Migration Assessment
  * Migration To PostgreSQL
  * Oracle to PostgreSQL
  * Lift/Shift To Cloud
  * Remote DBA Support
  * PostgreSQL Trainings

Address

PO Box 1126
Durham, NC, 27702-1126
USA
+1-902-221-5976

(c) 2021 All rights reserved

Copyright (c) 2021 MigOps

  * Home
  * Blog
  * Announcements
  * ServicesMenu Toggle
      + Migration Assessment
      + Migration to PostgreSQL
      + Oracle to PostgreSQL Migration
      + PostgreSQL Trainings
      + Remote Database Administrator
  * About Us
  * Careers
  * Contact Us

Scroll to Top