Malformed SQL92 string at position: 5. Expecting "call"?

user_12758963 0
Viewed 1392

As of the title I am getting an exception while trying to call an Oracle function from my Java code. I tried few things but didn't worked. At this point I am not sure what to do. Any help or suggestions will be appreciated. Thanks in advance.

Java

CallableStatement cstmt = p_con.prepareCall("{? = CALL FXRATE_ENTITY(?, ?, ?, ?)}");
         cstmt.registerOutParameter(1, java.sql.Types.INTEGER);

         cstmt.setString(2, "SCR");
         cstmt.setString(3,l_srccur);
         cstmt.setString(4, l_tfcurr);
         cstmt.setString(5, "B001");

         cstmt.execute();
         res = cstmt.getString(1);

Oracle Function

FUNCTION FXRATE_ENTITY(CODCURRBASE IN VARCHAR, LCODCURRFROM IN VARCHAR, LCODCURRTO IN VARCHAR, LIDENTITY IN VARCHAR)
  RETURN NUMBER IS
  CODCUR_L VARCHAR2(3);
BEGIN
  IF (LCODCURRTO = '*') THEN
    RETURN NVL(FXRATE(CODCURRBASE, LCODCURRFROM, CODCURRBASE, LIDENTITY),
               0);
  ELSE
    RETURN NVL(FXRATE(CODCURRBASE, LCODCURRFROM, LCODCURRTO, LIDENTITY), 0);
  END IF;
END;
jdbc oracle java
1 Answers

You have several options:

A) You're using SQL92 syntax (with the curly brackets {}). I think for this option, "call" needs to be in lower case.

CallableStatement cstmt = p_con.prepareCall("{? = call FXRATE_ENTITY(?, ?, ?, ?)}")

B) The Oracle SQL statement CALL can be used for functions, but you need the INTO clause. Since your OUT parameter now goes at the end, reorder your parameter statements.

CallableStatement cstmt = p_con.prepareCall("CALL FXRATE_ENTITY(?, ?, ?, ?) INTO ?");
cstmt.setString(1, "SCR");
cstmt.setString(2,l_srccur);
cstmt.setString(3, l_tfcurr);
cstmt.setString(4, "B001");
cstmt.registerOutParameter(5, java.sql.Types.INTEGER);

B) Instead of CALL, you can use a PL/SQL anonymous block, like this example:

CallableStatement cstmt = p_con.prepareCall("begin ? := FXRATE_ENTITY(?, ?, ?, ?); end;");
cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
cstmt.setString(2, "SCR");
cstmt.setString(3,l_srccur);
cstmt.setString(4, l_tfcurr);
cstmt.setString(5, "B001");
user_30618520
Related