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

问题描述

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

I am trying to run Oracle's CREATE JAVA statement.

我们必须将CallableStatement.setEscapeProcessing设置为false，以避免出现问号麻烦.这对于我们的大多数语句都可以正常工作，但是在Java switch语句的情况下，我们在下面得到了异常:

We have had to set CallableStatement.setEscapeProcessing to false to avoid troubles with question marks. This works fine for most of our statements, but in the case of a Java switch statement, we are getting the exception below :

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

我想这与switch语句中使用的冒号有关.

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

这是问题的一个例子:

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

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

This was run using ojdbc6.jar (as the JDBC driver) on a 11gR2 Oracle database.

推荐答案

如果您使用 Statement 而不是 CallableStatement .

It should work if you use a Statement instead of a CallableStatement.

对于所有DDL，应使用Statement，对于DML，应使用PreparedStatement，对于过程调用，应使用CallableStatement.创建过程是DDL.

You should use Statement for all DDL, PreparedStatement for DML and CallableStatement for procedure calls. Creating a procedure is DDL.

CallableStatement会尝试检测并绑定由冒号:标识的变量，因此这可能就是您的代码无法正常工作的原因.

A CallableStatement will try to detect and bind variables identified by a colon : so this is probably why your code didn't work.

这篇关于Callablestatement错误:索引处缺少IN或OUT参数:1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！