java - 在Oracle上调用存储过程会引发ORA-06550异常

我已经使用jdbc连接编写了一个简单的测试，并且试图了解失败和可能不正确的Oracle语法。据我了解，这应该可行，并且在互联网上搜索几个小时并没有反驳这一点。

简单的存储过程：

CREATE OR REPLACE PROCEDURE printHelloWorld
    (in_param_one IN VARCHAR2, out_param_one OUT VARCHAR2)
    AUTHID CURRENT_USER IS
BEGIN
    out_param_one := 'Hello World';
END;

测试1：

@Test
public void testOracleStoredProcedureWithIndexes() throws SQLException {
    ...
    Connection con = DriverManager.getConnection(host, props);

    java.sql.CallableStatement cstmt = con.prepareCall("BEGIN printHelloWorld(in_param_one => ?, out_param_one => ?); END;");

    ((oracle.jdbc.internal.OracleCallableStatement)cstmt).setString(1, "Test");
    ((oracle.jdbc.internal.OracleCallableStatement)cstmt).registerOutParameter(2, java.sql.Types.VARCHAR);

    ((java.sql.PreparedStatement)cstmt).execute();
    ...
}

测试2：

@Test
public void testOracleStoredProcedureWithNamedParameters() throws SQLException {
    ...
    Connection con = DriverManager.getConnection(host, props);

    java.sql.CallableStatement cstmt = con.prepareCall("BEGIN printHelloWorld(in_param_one => ?, out_param_one => ?); END;");

    ((oracle.jdbc.internal.OracleCallableStatement)cstmt).setString("in_param_one", "Test");
    ((oracle.jdbc.internal.OracleCallableStatement)cstmt).registerOutParameter("out_param_one", java.sql.Types.VARCHAR);

    ((java.sql.PreparedStatement)cstmt).execute();
}

运行这两个测试，“ Test1”通过，“ Test2”失败。我从“ Test2”获得的失败如下：

Caused by: Error : 6550, Position : 55, Sql = BEGIN procPrintHelloWorld2(in_param_one => IN_PARAM_ONE=>:0, out_param_one => OUT_PARAM_ONE=>:1); END;, OriginalSql = BEGIN procPrintHelloWorld2(in_param_one => ?, out_param_one => ?); END;, Error Msg = ORA-06550: line 1, column 56:
PLS-00103: Encountered the symbol ">" when expecting one of the following:

   . ( ) , * @ % & = - + < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec between || multiset member submultiset
ORA-06550: line 1, column 92:
PLS-00103: Encountered the symbol ">" when expecting one of the following:

   . ( ) , * @ % & = - + < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec between || multiset member submultiset

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)

我在某处读到了我不应该使用“：VAR”语法的信息，但是更改为该命令确实可以使它工作。。。更熟悉Oracle的人可以指出我在做什么错吗？

谢谢！

最佳答案

您在输出中看到了吗？

procPrintHelloWorld2(in_param_one => IN_PARAM_ONE=>:0, out_param_one => OUT_PARAM_ONE=>:1)

它会将您的命名参数加倍，在原始SQL中使用?并将其替换为IN_PARAM_ONE=>:0以获得in_param_one => IN_PARAM_ONE=>:0。

如果从SQL字符串中删除那些字符串，我认为无论哪种方式都可以正常工作。

java.sql.CallableStatement cstmt = con.prepareCall("BEGIN printHelloWorld(?, ?); END;");

关于java - 在Oracle上调用存储过程会引发ORA-06550异常，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57615528/