java - 如何使用问号PreparedStatement (Java)？

Question

我想通过 JDBC 在 oracle 数据库中创建一个 java 源对象，PreparedStatement。但是，在java源文件中，有几个问号。执行后，我遇到了如下错误消息。

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).

我更改了我的代码以使其更易于理解。

private void installOS_COMMAND() {
        Connection targetDBconn = null;
        PreparedStatement pstmt = null;
        Statement stmt = null;

        try {
            String SQL = "create or replace java source named \"FILE_TYPE_JAVA\" as\n"
                    + "public class FileType {\n"
                    + "    public static String getFileTypeOwner(Connection con) throws Exception {\n"
                    + "    String sFileTypeOwner = null;\n"
                    + "    CallableStatement stmt = con.prepareCall(\"begin dbms_utility.name_resolve(?,?,?,?,?,?,?,?); end;\");\n"
                    + "    stmt.setString(1, \"FILE_TYPE\");\n"
                    + "    stmt.setInt(2, 7);\n"
                    + "    stmt.registerOutParameter(3, java.sql.Types.VARCHAR);\n"
                    + "    stmt.registerOutParameter(4, java.sql.Types.VARCHAR);\n"
                    + "    stmt.registerOutParameter(5, java.sql.Types.VARCHAR);\n"
                    + "    stmt.registerOutParameter(6, java.sql.Types.VARCHAR);\n"
                    + "    stmt.registerOutParameter(7, oracle.jdbc.OracleTypes.NUMBER);\n"
                    + "    stmt.registerOutParameter(8, oracle.jdbc.OracleTypes.NUMBER);\n"
                    + "    stmt.execute();\n"
                    + "    sFileTypeOwner = stmt.getString(3);\n"
                    + "    stmt.close();\n"
                    + "    return sFileTypeOwner;\n"
                    + "  }\n"
                    + "}";
            targetDBconn = globalTargetConn.connect();
            pstmt = targetDBconn.prepareStatement(SQL);
            pstmt.executeUpdate();
        } catch (SQLException ex) { logWriter.writeLogs(logTextArea, LogWriter.ERROR, ex.getMessage());
        } finally {
            if (pstmt != null ) try {pstmt.close();} catch(SQLException ex) {}
            if (targetDBconn != null ) try {targetDBconn.close();} catch(SQLException ex) {}
        }
    }

有人可以解决这个问题吗？

Answer 1

不要使用准备好的语句:

private void installOS_COMMAND() {
    Connection targetDBconn = null;
    Statement stmt = null;

    try {
        String SQL = "create or replace java source named \"FILE_TYPE_JAVA\" as\n"
                + "public class FileType {\n"
                + "    public static String getFileTypeOwner(Connection con) throws Exception {\n"
                + "    String sFileTypeOwner = null;\n"
                + "    CallableStatement stmt = con.prepareCall(\"begin dbms_utility.name_resolve(?,?,?,?,?,?,?,?); end;\");\n"
                + "    stmt.setString(1, \"FILE_TYPE\");\n"
                + "    stmt.setInt(2, 7);\n"
                + "    stmt.registerOutParameter(3, java.sql.Types.VARCHAR);\n"
                + "    stmt.registerOutParameter(4, java.sql.Types.VARCHAR);\n"
                + "    stmt.registerOutParameter(5, java.sql.Types.VARCHAR);\n"
                + "    stmt.registerOutParameter(6, java.sql.Types.VARCHAR);\n"
                + "    stmt.registerOutParameter(7, oracle.jdbc.OracleTypes.NUMBER);\n"
                + "    stmt.registerOutParameter(8, oracle.jdbc.OracleTypes.NUMBER);\n"
                + "    stmt.execute();\n"
                + "    sFileTypeOwner = stmt.getString(3);\n"
                + "    stmt.close();\n"
                + "    return sFileTypeOwner;\n"
                + "  }\n"
                + "}";
        targetDBconn = globalTargetConn.connect();
        stmt = targetDBconn.createStatement();
        stmt.setEscapeProcessing(false);
        stmt.executeUpdate(SQL);
    } catch (SQLException ex) { logWriter.writeLogs(logTextArea, LogWriter.ERROR, ex.getMessage());
    } finally {
        if (targetDBconn != null ) try {targetDBconn.close();} catch(SQLException ex) {}
    }
}