SQL92字符串在位置5处格式错误。期望”call”?

huangapple go评论92阅读模式
英文:

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

问题

以下是翻译好的内容:

在标题中,当我试图从我的Java代码中调用Oracle函数时,我遇到了一个异常。我尝试了一些方法,但都没有起作用。此时我不确定该怎么办。对任何帮助或建议将不胜感激。提前谢谢。

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;

英文:

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;

答案1

得分: 6

A) 你正在使用 SQL92 语法(带有花括号{})。我认为对于这个选项,“call” 需要小写。

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

B) Oracle SQL 语句 CALL 可用于函数,但您需要使用 INTO 子句。由于您的 OUT 参数现在放在最后,重新排列参数语句。

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);

C) 与 CALL 不同,您可以使用 PL/SQL 匿名块,就像这个示例

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");
英文:

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");

huangapple
  • 本文由 发表于 2020年9月3日 00:30:41
  • 转载请务必保留本文链接:https://go.coder-hub.com/63709772.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定