1

I am working on DB migration from Oracle to Postgres and came across a situation where a legacy Java code is calling a stored procedure from Oracle. It is a simple call without parentheses like this : Mgr.AdmSP.myproc = ad.ctlpk.proc where ad is schema name, ctlpk is Package name and proc is Stored Procedure in Oracle

we have migrated procedure proc to Postgres but when it comes to calling, Postgres expects () for procedure or function calls even if it does not take any parameters. So we tried to run it like this : Mgr.AdmSP.myproc = ad_ctlpk.proc(). Here ad_ctlpk is postgres schema name. After executing we are getting error : syntax error at or near "(". Since this is a database migration only, we are not expected to make any changes to other components except database objects. Is there a way to mimic the Oracle like procedure calling style in Postgres? Like Postgres have function now() and current_timestamp which perform same functionality but with different syntax. I am using Postgres Ver 11.11.

Adding some details as per comments for more details. In Oracle, it is defined as FUNCTION Proc RETURN INT and as of now we converted it into Postgres function as CREATE FUNCTION ad_ctlpk.proc() RETURNS INTEGER

I am not sure about the Framework and other java code details since I dont have access to Java code. We have access to executables only, which when executed gives below error

[[AdmThread 1] [WARN:AbstractThreadedPublisherManager$ExceptionHandlingThreadGroup:48] Error in thread AdmThread 1 : Problem in doWork
  Caused by: Problem running proc procedures
  Caused by: ERROR: syntax error at or near "("
  Position: 38, State: 42601, ErrorCode: 0
com.ubs.datait.common.distribution.exception.DistributionRuntimeException: Problem in doWork
        at common.distribution.manager.AdmThread.doWork(AdmThread.java:72)
        at common.distribution.common.AbstractDistributionThread.run(AbstractDistributionThread.java:30)
Caused by: common.distribution.exception.DistributionRuntimeException: Problem running proc procedures
        at common.distribution.manager.AdmThread.runAdminProc(AdmThread.java:100)
        at common.distribution.manager.AdmThread.doWork(AdmThread.java:67)
        ... 1 more
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "("                                                                                         
  Position: 38
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)

Update: :

Since Postgres does not support creating/calling functions without braces (), we finally had to ask for changes in java code to add () to the function calls where required.

aashoo
  • 404
  • 4
  • 13
  • Are you calling the procedure from Java client code via JDBC or from a PL/Java stored procedure? – Laurenz Albe Sep 01 '21 at 08:19
  • @LaurenzAlbe We have jar file created for java component which is calling the procedure via JDBC – aashoo Sep 01 '21 at 08:30
  • That doesn't look like JDBC code. Please tag the API and framework that you are using and edit the question to show some more of your code for context. – Laurenz Albe Sep 01 '21 at 08:33
  • `ad_ctlpk.proc()` can't be a procedure because the way you use it, suggests that it's actually a function. But no, there is no way to call a Postgres function (or procedure) without the parentheses. –  Sep 01 '21 at 12:19
  • Thanks @a_horse_with_no_name. Yes you are correct. Proc was a function in Oracle, defined as FUNCTION Proc RETURN INT . As of now we converted it into Postgres function as CREATE FUNCTION ad_ctlpk.proc() RETURNS INTEGER. But if required we can convert it into procedure – aashoo Sep 01 '21 at 12:34
  • 1
    A function is the correct thing to use if you want to return something. But it's impossible to call it without the parentheses. You will have to change your code –  Sep 01 '21 at 12:35
  • To address the question in the title: it *is* possible by using "attribute notation". See: https://stackoverflow.com/a/11166268/939860 But that's probably not applicable to your case. (And I wouldn't advise to use it either way.) – Erwin Brandstetter Sep 02 '21 at 01:18

0 Answers0