ORA-01000: 超出打开游标的最大数

[rawhand]

O2MConverter::run 的for循环中pre未及时close，当表数量多时会产生“ORA-01000: 超出打开游标的最大数”的异常。

[rawhand]

`

public void run(String url, String username, String password) {

    Connection con = null;
    // 创建预编译语句对象，一般都是用这个而不用Statement
    PreparedStatement pre = null;
    PreparedStatement pre2 = null;
    ResultSet result = null;
    ResultSet result2 = null;
    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        System.out.println("Trying to connect ... ！");
        con = DriverManager.getConnection(url, username, password);
        System.out.println("Connected！");
        // step1 获取每张表的名称和注释
        pre = con.prepareStatement("select a.TABLE_NAME as TABLE_NAME, b.COMMENTS as COMMENTS " +
                "from user_tables a " +
                "left join user_tab_comments b on a.TABLE_NAME= b.TABLE_NAME " +
                "order by table_name asc");
        result = pre.executeQuery();
        int allTableCount = result.getMetaData().getColumnCount();

        // System.out.println("一共有" + allTableCount + "张表");
        List<Table> allTable = new ArrayList<Table>(allTableCount);
        int i = 1;
        while (result.next()) {
            Table oracleTable = new Table();
            String tableName = result.getString("TABLE_NAME");
            String comment = result.getString("COMMENTS");
            // System.out.println("第" + i + "个表名称为： " + tableName + " 注释为：" + comment);
            oracleTable.name = tableName;
            oracleTable.comment = comment;
            allTable.add(oracleTable);
            i++;
        }
        result.close();
        pre.close();

        // 看看中文都占几个字节
        // 如果显示SIMPLIFIED CHINESE_CHINA.ZHS16GBK，一个汉字占用两个字节;
        // 如果显示SIMPLIFIED CHINESE_CHINA.AL32UTF8，一个汉字占用三个字节.
        pre = con.prepareStatement("select userenv('language') as LANGUAGE from dual");
        result = pre.executeQuery();
        String character = "SIMPLIFIED CHINESE_CHINA.AL32UTF8";
        Integer divideCharater = 3;
        while (result.next()) {
            character = result.getString("LANGUAGE");
        }
        System.out.println(character);
        if("SIMPLIFIED CHINESE_CHINA.ZHS16GBK".equals(character)){
            divideCharater = 2;
        }
        result.close();
        pre.close();

        //step2 找到每张表的信息进行DML拼凑和转换
        for(Table oracleTable: allTable){
            // 查询表结构
            pre = con.prepareStatement("select a.TABLE_NAME, a.COLUMN_NAME, a.DATA_TYPE, a.DATA_LENGTH, a.DATA_PRECISION, a.DATA_SCALE, a.NULLABLE, b.COMMENTS " +
                    "from user_tab_columns a " +
                    "inner join user_col_comments b on a.COLUMN_NAME = b.COLUMN_NAME and a.TABLE_NAME=b.TABLE_NAME " +
                    "where a.TABLE_NAME = ? " +
                    "order by a.COLUMN_ID asc");
            pre.setString(1, oracleTable.name);
            result = pre.executeQuery();

            // 查询主键
            pre2 = con.prepareStatement("select col.column_name " +
                    "from user_constraints con,user_cons_columns col " +
                    "where con.constraint_name=col.constraint_name and con.constraint_type='P' " +
                    "and col.table_name= ? ");
            pre2.setString(1, oracleTable.name);
            result2 = pre2.executeQuery();

            String primaryKey = "ID";
            while (result2.next()){
                primaryKey = result2.getString("COLUMN_NAME");
            }
            // System.out.println("主键是 " + primaryKey);

            List<TableColumn> oracleColumns = new ArrayList<TableColumn>();

            while (result.next()) {
                TableColumn oracleColumn = new TableColumn();
                oracleColumn.name = result.getString("COLUMN_NAME");
                oracleColumn.comment = result.getString("COMMENTS");
                oracleColumn.dataLength = result.getString("DATA_LENGTH");
                oracleColumn.dataPrecision = result.getString("DATA_PRECISION");
                oracleColumn.dataScale = result.getString("DATA_SCALE");
                oracleColumn.dataType = result.getString("DATA_TYPE");
                oracleColumn.defaultValue = "";
                oracleColumn.nullable = result.getString("NULLABLE");
                oracleColumn.divideChineseCharater = divideCharater;

                if(oracleColumn.name.equalsIgnoreCase(primaryKey)){
                    oracleColumn.primaryKey = true;
                    oracleTable.primaryKeyColumnName = oracleColumn.name;
                    oracleTable.primaryKeyDataType = oracleColumn.dataType;
                } else {
                    oracleColumn.primaryKey = false;
                }
                oracleColumns.add(oracleColumn);
            }
            oracleTable.columns = oracleColumns;
            result.close();
            pre.close();
            result2.close();
            pre2.close();
        }

        //step3 进行转换
        StringBuilder sb = new StringBuilder();
        for(Table oracleTable: allTable){
            // System.out.println("converting ...... " + oracleTable.name);
            sb.append(oracleTable.toMySQLScript());
        }

        //step4 save to file
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String scriptComment = "-- " + dateFormat.format(new Date()) + "  Oracle 2 MySQL \n" +
                "-- Database Info: \n" +
                "--     " + url + "\n" +
                "--     " + username + "\n\n\n";
        sb.insert(0, scriptComment);
        FileUtils.saveSqlScript(sb.toString());

        System.out.println("get table data finished!");
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            // 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
            if (result != null) {
                result.close();
            }
            if (pre != null) {
                pre.close();
            }
            if (result2 != null) {
                result2.close();
            }
            if (pre2 != null) {
                pre2.close();
            }
            if (con != null) {
                con.close();
            }
            System.out.println("Connection to database closed!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

`

[codeliuyang]

表的数量是......

[rawhand]

表的数量是......

1000+