Issue while executing PostgreSQL Stored Procedure

I am facing an issue while running executing the Postgresql StoredProcedure. When i am running Function , i am getting proper response but when i am running Stored Procedure i am getting this exception.

SQL State: 42883  ERROR: function abc_acclvl_proc(character varying, character varying, character varying, character varying) does not exist  Hint: No function matches the given name and argument types. You might need to add explicit type casts.  Position: 15org.postgresql.util.PSQLException: ERROR: function abc_acclvl_proc(character varying, character varying, character varying, character varying) does not exist  Hint: No function matches the given name and argument types. You might need to add explicit type casts.  Position: 15  at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)  at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)  at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)  at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)  at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)  at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)  at org.postgresql.jdbc.PgCallableStatement.executeWithFlags(PgCallableStatement.java:83)  at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:153)  at macd.bean.MacdApp.FunctionReturnString.main(FunctionReturnString.java:37)  

My Java code is :

public class FunctionReturnString {    public static void main(String[] args) {        String runFunction ="{call abc_acclvl_proc(?,?,?,?,?,?)}";        try (Connection conn = DriverManager.getConnection(              "jdbc:postgresql://localhost:5432/maald?currentSchema=abc", "maald", "maald");           Statement statement = conn.createStatement();           CallableStatement callableStatement = conn.prepareCall(runFunction)) {                              callableStatement.setString(1, "Middletown");          callableStatement.setString(2, "NJ");          callableStatement.setString(3, "");          callableStatement.setString(4, "Empty");          callableStatement.registerOutParameter(5, Types.INTEGER);          callableStatement.registerOutParameter(6, Types.INTEGER);          boolean hasResultSet = callableStatement.execute();                    int result = callableStatement.getInt(5);                  System.out.println(result);        } catch (SQLException e) {          System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());          e.printStackTrace();      } catch (Exception e) {          e.printStackTrace();      }    }  

}

My question is in the exception why i am getting "function macd_acclvl_proc(character varying, character varying, character varying, character varying) does not exist" . But it is a stored procedure in PostgreSQL server. PostgreSQL server version is 12.3 and as per PostgreSQL blog version higher than 11 does support Stored Procedure also. I am using postgresql-42.2.18 jar for connectivity.

My stored procedure definition is as belows :

CREATE OR REPLACE PROCEDURE abc.abc_acclvl_proc(  icity character,  istate character,  prodtype character,  ordgrp character,  INOUT cclvl bigint,  INOUT prlvl bigint)   LANGUAGE 'plpgsql'   

Any help will be appreciable. Thanks !!!

https://stackoverflow.com/questions/65811580/issue-while-executing-postgresql-stored-procedure January 20, 2021 at 10:32PM