java - Callablestatement 错误:索引::1 处缺少 IN 或 OUT 参数

Question

我正在尝试运行 Oracle 的 CREATE JAVA 语句。

我们必须将 CallableStatement.setEscapeProcessing 设置为 false 以避免出现问号问题。这对我们的大多数语句都适用，但在 Java switch 语句的情况下，我们会遇到以下异常:

java.sql.SQLException: Missing IN or OUT parameter at index:: 1

I suppose it has something to do with the colons used in the switch statement.

Here's an example of the problem :

JDBCTest.java

package jdbctest;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

public class JDBCTest {

    public static void main(String[] args) throws Exception {
        String data = readScript("/tmp/Demo.sql");        

        Connection conn = null;
        CallableStatement callStat = null;
        try {
            String driver = "oracle.jdbc.driver.OracleDriver";
            String url = "jdbc:oracle:thin:@193.53.40.220:1521:ora11";
            String username = "";
            String password = "";

            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
            conn.setAutoCommit(false);
            callStat = conn.prepareCall(data);
            callStat.setEscapeProcessing(false);
            callStat.execute();
        } finally {
            try {callStat.close();} catch (Exception ex){}
            try {conn.close();} catch (Exception ex){}
        }

    }

    private static String readScript(String filename) throws IOException
    {
        StringBuilder stringBuilder = new StringBuilder();
        BufferedReader reader = null;
        try {
            String currentLine;
            reader = new BufferedReader(new InputStreamReader(
                    new FileInputStream(filename), "ISO-8859-1"));

            while ((currentLine = reader.readLine()) != null) {
                currentLine = currentLine.replaceAll("^\\s+$", "");
                currentLine = currentLine.replaceAll("\\s+$", "");
                stringBuilder.append(currentLine).append("\n");
            }
        } finally {
            try { reader.close();} catch (Exception ex) {}            
        }

        return stringBuilder.toString();
    }

}

Demo.sql

CREATE OR REPLACE JAVA SOURCE NAMED "jdbctest" AS

package jdbctest;

public class Demo {
    public void test()
    {
        int a = 3;
        switch (a)
        {
            case 1: System.out.println("1"); break;
            case 2: System.out.println("2"); break;
            case 3: System.out.println("3"); break;
        }
    }
}

这是在 11gR2 Oracle 数据库上使用 ojdbc6.jar(作为 JDBC 驱动程序)运行的。

Answer 1

如果你使用 Statement 它应该可以工作而不是 CallableStatement .

您应该对所有 DDL 使用 Statement，对 DML 使用 PreparedStatement，对过程调用使用 CallableStatement。创建过程是 DDL。

CallableStatement 将尝试检测并绑定(bind)由冒号 : 标识的变量，所以这可能就是您的代码无法正常工作的原因。