oracle 超出最大的游标数？怎么解决？

sqlFather 2010-05-07 10:04:05



public void insertTraitStrTrees(List<TraitStrTrees> list, String tableName) {



        connect();

        sql = "insert into " + tableName + " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        try {

            conn.setAutoCommit(false);

            for (int i = 0; i < list.size(); i++) {

                System.out.println(tableName+"   "+i);

                ps = conn.prepareStatement(sql);

                ps.setInt(1, list.get(i).getDg_id());

                ps.setInt(2, list.get(i).getId());

                ps.setString(3, list.get(i).getTrait_value());

                ps.setInt(4, list.get(i).getParent_id());

                ps.setInt(5, list.get(i).getChild_id());

                ps.setInt(6, list.get(i).getLeft_id());

                ps.setInt(7, list.get(i).getRight_id());



                ps.setBlob(8, BLOB.getEmptyBLOB());

                ps.setBlob(9, BLOB.getEmptyBLOB());

                ps.setBlob(10, BLOB.getEmptyBLOB());

                ps.setBlob(11, BLOB.getEmptyBLOB());

                ps.setBlob(12, BLOB.getEmptyBLOB());

                ps.setBlob(13, BLOB.getEmptyBLOB());

                ps.setBlob(14, BLOB.getEmptyBLOB());



                ps.setInt(15,list.get(i).isFrist());

                ps.setInt(16,list.get(i).isHasChild());

                ps.executeUpdate();

                String sql1="select articles,pictures,tables,videos,audios,ads,pages from "+tableName+" where dg_id="+list.get(i).getDg_id()+" and id="+list.get(i).getId()+" FOR UPDATE";

                System.out.println(sql1);

                ps=conn.prepareStatement(sql1);



                ResultSet rs=ps.executeQuery();

                rs.next();

                BLOB articles=(BLOB)rs.getBlob(1);

                BLOB pictures=(BLOB)rs.getBlob(2);

                BLOB tables=(BLOB)rs.getBlob(3);

                BLOB videos=(BLOB)rs.getBlob(4);

                BLOB audios=(BLOB)rs.getBlob(5);

                BLOB ads=(BLOB)rs.getBlob(6);

                BLOB pages=(BLOB)rs.getBlob(7);



                OutputStream out1=articles.getBinaryOutputStream();

                OutputStream out2=pictures.getBinaryOutputStream();

                OutputStream out3=tables.getBinaryOutputStream();

                OutputStream out4=videos.getBinaryOutputStream();

                OutputStream out5=audios.getBinaryOutputStream();

                OutputStream out6=ads.getBinaryOutputStream();

                OutputStream out7=pages.getBinaryOutputStream();

                try {

                     out1.write(list.get(i).getArticles());

                     out1.close();

                     out2.write(list.get(i).getPictures());

                     out2.close();

                     out3.write(list.get(i).getTables());

                     out3.close();

                     out4.write(list.get(i).getVideos());

                     out4.close();

                     out5.write(list.get(i).getAudios());

                     out5.close();

                     out6.write(list.get(i).getAds());

                     out6.close();

                     out7.write(list.get(i).getPages());

                     out7.close();

                } catch (IOException ex) {

                     Logger.getLogger(MysqlDB.class.getName()).log(Level.SEVERE, null, ex);

                }

                

                String sql2="update "+tableName+" set articles=? , pictures=? , tables=? , videos=? , audios=? , ads=? , pages=? where dg_id="+list.get(i).getDg_id()+" and id="+list.get(i).getId();

                System.out.println(sql2);

                ps=conn.prepareStatement(sql2);

                ps.setBlob(1, articles);

                ps.setBlob(2, pictures);

                ps.setBlob(3, tables);

                ps.setBlob(4, videos);

                ps.setBlob(5, audios);

                ps.setBlob(6, ads);

                ps.setBlob(7, pages);

                

                ps.executeUpdate();

                ps.addBatch();

            }

            ps.executeBatch();

            conn.commit();

        } catch (SQLException e) {

            e.printStackTrace();

        } finally {

            close();

        }



    }

...全文

789 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

zhangwonderful 2010-05-07

打赏
举报

[Quote=引用 1 楼 iihero 的回复:]

1. 修改open_cursors
SQL>conn sys as sysdba
SQL>alter system set open_cursors=1500 scope=both;
2. 或者：
List<TraitStrTrees> list它的元素个数太多，你不用枚举完它再commit，而是每隔比如50条commit一次。
[/Quote]
支持此方法

tangren 2010-05-07

打赏
举报

使用BLOB导致游标打开没有关闭?
看一下数据库的游标数参数是多少
show parameter open_cursors

iihero_ 2010-05-07

打赏
举报

1. 修改open_cursors
SQL>conn sys as sysdba
SQL>alter system set open_cursors=1500 scope=both;
2. 或者：
List<TraitStrTrees> list它的元素个数太多，你不用枚举完它再commit，而是每隔比如50条commit一次。

ojuju10 2010-05-07

打赏
举报

修改Open_cursors参数值

alter system set open_cursors=1500 scope=both;

碧水幽幽泉 2010-05-07

打赏
举报

[Quote=引用 1 楼 iihero 的回复:]
1. 修改open_cursors
SQL>conn sys as sysdba
SQL>alter system set open_cursors=1500 scope=both;
2. 或者：
List<TraitStrTrees> list它的元素个数太多，你不用枚举完它再commit，而是每隔比如50条commit一次。
[/Quote]
up!