procedure myProcedure (text, text) does not exist Hint: No procedure matches the given name and argument types

whenever trying calling any stored procedure in PostgreSQL 11.4 from my java application but getting this issue procedure pkg$my_procedure(text, text) does not exist. Note that I’m able to call the SP from DB.

im using PostgreSQL JDBC version 42.2.16

the SP declaration

create procedure pkg$my_procedure(i_param_name text, i_param_2 text, INOUT o_object refcursor)
    language plpgsql
as
$$

BEGIN
// myLogic

Java code to call the SP

        Connection con = null;
        CallableStatement callableStatement = null;
        ResultSet rs = null;
        Object obj = null;
        try {
            con = eRestaurantConnection.getConnetion();
            callableStatement = con.prepareCall("call pkg$my_procedure(cast(? as text),cast(? as text),?)");
            callableStatement.setString(1, string1);
            callableStatement.setString(2, string2);
            callableStatement.registerOutParameter(3, Types.REF_CURSOR);
            callableStatement.execute();
            rs = (ResultSet) callableStatement.getObject(3);
            obj = fillObjectInfo(rs);
        } catch (Exception ex) {
            LOG.error(ex.getLocalizedMessage(), ex);
        } finally {
            if (rs != null)
                rs.close();
            if(callableStatement!=null)callableStatement.close();
            if(con!=null)con.close();
        }
        return obj;

the Exception

ERROR: procedure pkg$my_procedure(text, text) does not exist
  Hint: No procedure matches the given name and argument types. You might need to add explicit type casts.
  Position: 6
org.postgresql.util.PSQLException: ERROR: procedure pkg$my_procedure(text, text) does not exist
  Hint: No procedure matches the given name and argument types. You might need to add explicit type casts.
  Position: 6
    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 com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
    at com.zaxxer.hikari.pool.HikariProxyCallableStatement.execute(HikariProxyCallableStatement.java)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at net.ttddyy.dsproxy.proxy.StatementProxyLogic.performQueryExecutionListener(StatementProxyLogic.java:310)
    at net.ttddyy.dsproxy.proxy.StatementProxyLogic.access$700(StatementProxyLogic.java:36)
    at net.ttddyy.dsproxy.proxy.StatementProxyLogic$1.execute(StatementProxyLogic.java:122)
    at net.ttddyy.dsproxy.listener.MethodExecutionListenerUtils.invoke(MethodExecutionListenerUtils.java:41)
    at net.ttddyy.dsproxy.proxy.StatementProxyLogic.invoke(StatementProxyLogic.java:119)
    at net.ttddyy.dsproxy.proxy.jdk.CallableStatementInvocationHandler.invoke(CallableStatementInvocationHandler.java:36)
    ...

the call from PostgreSQL

do $$
declare
    result refcursor = 'generated_result_cursor';
    rec record;
begin
    open result for call pkg$my_procedure(i_param_name  := 'name', i_param_2 := 'param', o_object := null);
    LOOP
        FETCH from result into rec;
        EXIT WHEN NOT FOUND;
        raise notice 're: %',rec;
        EXIT;
    END LOOP;
end
$$;

Answer

You created the stored procedure without doublequoting the name, so it is stored in lowercase.

The error message reports a functioname containing an uppercase. Since PG is case sensitive, the stored procedure is not found.

–> use a lower case function name

callableStatement = con.prepareCall("call myprocedure(cast(? as text),cast(? as text),?)");

Advertisement

Answer