Call pl/sql stored function from java

Question

There is a stored function:

FUNCTION EXECUTE_PAYMENT(P_REQUEST IN XMLTYPE, P_RESPONSE OUT XMLTYPE)
RETURN NUMBER IS

I want to call it from Java.

   final String xml = "<COLLECTION>\n" +
            "    <OWNER>RETAIL</OWNER>\n" +
            "    <REMITTER_TYPE>ACCOUNT</REMITTER_TYPE>\n" +
            "    <SRC_AMOUNT>10000</SRC_AMOUNT>\n" +
            "    <TRG_RATE>224</TRG_RATE>\n" +
            "</COLLECTION>\n";
    
    Connection connection = expressJdbcTemplate.getDataSource().getConnection();

    CallableStatement pstmt = connection
            .prepareCall("{?=call pkg_mb_sbr_operations.EXECUTE_PAYMENT(?,?)}");


     pstmt.setString(2, xml );
    pstmt.setString(3, "");
    pstmt.registerOutParameter(1, Types.NUMERIC);
    pstmt.execute();

But I get this error :

    Servlet.service() for servlet [dispatcherServlet] in context with path [/api/sberbank] threw exception [Request processing failed;

nested exception is java.sql.SQLException: ORA-06550: line 1, column 13: PLS-00306: wrong number or types of arguments in call to 'EXECUTE_PAYMENT' ORA-06550: line 1, column 13: PLS-00306: wrong number or types of arguments in call to 'EXECUTE_PAYMENT' ORA-06550: line 1, column 7: PL/SQL: Statement ignored ] with root cause

oracle.jdbc.OracleDatabaseException: ORA-06550: line 1, column 13: PLS-00306: wrong number or types of arguments in call to 'EXECUTE_PAYMENT' ORA-06550: line 1, column 13: PLS-00306: wrong number or types of arguments in call to 'EXECUTE_PAYMENT' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:632) ~[ojdbc11-21.1.0.0.jar:21.1.0.0.0]
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:562) ~[ojdbc11-21.1.0.0.jar:21.1.0.0.0]
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1207) ~[ojdbc11-21.1.0.0.jar:21.1.0.0.0]

...

Please help to define problem

Answer 1

• An XMLTYPE is not a string (and vice versa, a string is not an XMLTYPE).
• The function signature has an IN and an OUT parameter and not two IN parameters.

You may be able to use:

CallableStatement pstmt = connection
            .prepareCall("
DECLARE
  v_out XMLTYPE;
BEGIN
  ? := pkg_mb_sbr_operations.EXECUTE_PAYMENT(XMLTYPE(?),v_out);
  ? := vout.getStringVal();
END;");

pstmt.registerOutParameter(1, Types.NUMERIC);
pstmt.setString(2, xml );
pstmt.registerOutParameter(3, Types.VARCHAR2);
pstmt.execute();