英文:
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");
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论