오서비네 이글루

안녕하세요.

저는 경상대학교에 재학중인 박진만이라고 합니다.

다름이 아니라, 클럽/동호회를 만드는데 생성시

데이터를 오라클 DB에 insert하고 Create하는 부분인데,,

무결성을 위해.. 12개를 처리하는데 이때 트랜젝션 처리를 합니다.

그러면 10개정도를 생성하고 Drop시킨후 다시 한 10개쯤 만들면

DB가 이상해집니다. 결국 15~6개 정도 만들면 아래와 같은 에러메세지가 나오고

결국은 오라클을 재부팅해야되는 결과가 나옵니다.

다음과 같은 에러가 발생하는데 어떻게 해결해야 할런지요.

java.sql.SQLException: ORA-00604: 순환 SQL 레벨 1 에 오류가 발생했습니다 ORA-01000: 최대 열기 커서 수를 초과했습니다 .

많은 도움을 부탁드립니다.

그리고 DB에 입력하는 부분의 소스를 첨부합니다.

몇일째 해결이 안되서 걱정입니다.

그럼 좋은 하루되시길바라며...,

< % @ page contentType="text/html; charset=euc-kr" import="java.sql.*, java.util.*" % >

< % @ page import="com.worlsys.lib.*" % >

< %

String ip = request.getRemoteAddr();

String name = request.getParameter("name");

String url = request.getParameter("url");

int category = Integer.parseInt(request.getParameter("category"));

String desc = request.getParameter("desc");

String open = request.getParameter("open");

//////////////////////////////////////////sysop_id , sysop 가져오기

String sysop = request.getParameter("sysop");

String sysop_id = "jmpark";

String logo = name + " 클럽 입니다.";

String welcome = name + " 클럽에 오신걸 환영합니다.";

DBConnectionManager dbMgr = null;

Connection conn = null;

Statement stmt = null;

ResultSet rs = null;

PreparedStatement pstmt = null;

String tableName ="";

String sqlStr = "";

try{

dbMgr = DBConnectionManager.getInstance();

conn = dbMgr.getConnection("jspDB");

// 자동 커밋을 안되게

conn.setAutoCommit(false);

stmt = conn.createStatement();

//// Table [suw900tl] - 클럽정보 테이블

tableName = " SUW900TL ";

sqlStr = "insert into " + tableName + " ( suw900_no, suw900_name, suw900_url, suw900_category, suw900_date, suw900_sysop, suw900_membernum, suw900_open, suw900_desc, suw900_status, suw900_logo, suw900_welcome, suw900_grade, suw900_capacity, suw900_ip, suw900_curcapacity, suw900_updatedate ) ";

sqlStr = sqlStr + " values ( suw900.nextval , ?, ?, ?, sysdate, ?, 1, ?, ?, '1', ?, ?, 4, 5, ?, 0, sysdate) ";

pstmt = conn.prepareStatement(sqlStr);

pstmt.setString(1, name);

pstmt.setString(2, url);

pstmt.setInt(3, category);

pstmt.setString(4, sysop);

pstmt.setString(5, open);

pstmt.setString(6, desc);

pstmt.setString(7, logo);

pstmt.setString(8, welcome);

pstmt.setString(9, ip);

pstmt.executeUpdate();

pstmt.close();

//// Table [suw920tl] - 클럽 가입 회원 테이블

// 클럽번호 가져오기

sqlStr = " select suw900_no from " + tableName + " where suw900_name='" + name + "'";

rs = stmt.executeQuery(sqlStr);

rs.next();

int clubno = rs.getInt("suw900_no");

rs.close();

stmt.close();

tableName = " SUW920TL ";

sqlStr = "insert into " + tableName + " ( suw920_no, suw920_clubno, suw920_id, suw920_nickname, suw920_grade, suw920_open, suw920_status, suw920_date, suw920_ip, suw920_updatedate ) ";

sqlStr = sqlStr + " values ( suw920.nextval , ?, ?, ?, 1, '1', '1', sysdate, ?, sysdate) ";

pstmt = conn.prepareStatement(sqlStr);

pstmt.setInt(1, clubno);

pstmt.setString(2, sysop_id);

pstmt.setString(3, sysop);

pstmt.setString(4, ip);

pstmt.executeUpdate();

pstmt.close();

//// Table [suw921tl] - 각 클럽 회원 등급 테이블

tableName = " SUW921TL ";

sqlStr = "insert into " + tableName + " ( suw921_no, suw921_name, suw921_clubno, suw921_date, suw921_updatedate ) ";

sqlStr = sqlStr + " values ( 1, '시삽', "+ clubno +", sysdate, sysdate) ";

pstmt = conn.prepareStatement(sqlStr);

pstmt.executeUpdate();

pstmt.close();

sqlStr = "insert into " + tableName + " ( suw921_no, suw921_name, suw921_clubno, suw921_date, suw921_updatedate ) ";

sqlStr = sqlStr + " values ( 2, '부시삽', "+ clubno +", sysdate, sysdate) ";

pstmt = conn.prepareStatement(sqlStr);

pstmt.executeUpdate();

pstmt.close();

sqlStr = "insert into " + tableName + " ( suw921_no, suw921_name, suw921_clubno, suw921_date, suw921_updatedate ) ";

sqlStr = sqlStr + " values ( 3, '정회원', "+ clubno +", sysdate, sysdate) ";

pstmt = conn.prepareStatement(sqlStr);

pstmt.executeUpdate();

pstmt.close();

sqlStr = "insert into " + tableName + " ( suw921_no, suw921_name, suw921_clubno, suw921_date, suw921_updatedate ) ";

sqlStr = sqlStr + " values ( 4, '준회원', "+ clubno +", sysdate, sysdate) ";

pstmt = conn.prepareStatement(sqlStr);

pstmt.executeUpdate();

pstmt.close();

//// Table [suw910tl] - 클럽 공지사항/게시판/자료실 정보(레이아웃) 등급 테이블

tableName = " SUW910TL ";

sqlStr = "insert into " + tableName + " ( suw910_no, suw910_clubno, suw910_name, suw910_class, suw910_seq, suw910_read, suw910_write, suw910_date, suw910_updatedate ) ";

sqlStr = sqlStr + " values ( suw910.nextval, "+ clubno +", '공지사항', '1', 1, 4, 4,sysdate, sysdate) ";

pstmt = conn.prepareStatement(sqlStr);

pstmt.executeUpdate();

pstmt.close();

sqlStr = "insert into " + tableName + " ( suw910_no, suw910_clubno, suw910_name, suw910_class, suw910_seq, suw910_read, suw910_write, suw910_date, suw910_updatedate ) ";

sqlStr = sqlStr + " values ( suw910.nextval, "+ clubno +", '자유게시판', '2', 1, 4, 4,sysdate, sysdate) ";

pstmt = conn.prepareStatement(sqlStr);

pstmt.executeUpdate();

pstmt.close();

sqlStr = "insert into " + tableName + " ( suw910_no, suw910_clubno, suw910_name, suw910_class, suw910_seq, suw910_read, suw910_write, suw910_date, suw910_updatedate ) ";

sqlStr = sqlStr + " values ( suw910.nextval, "+ clubno +", '자료실', '3', 1, 4, 4,sysdate, sysdate) ";

pstmt = conn.prepareStatement(sqlStr);

pstmt.executeUpdate();

pstmt.close();

//// Table [suw911tl] - 전체 및 클럽 공지사항/게시판/자료실 테이블

tableName = " SUW911TL ";

sqlStr = "create sequence suw911_" + Integer.toString(clubno);

pstmt = conn.prepareStatement(sqlStr);

pstmt.executeUpdate();

pstmt.close();

sqlStr = "create table suw911tl_" + Integer.toString(clubno);

sqlStr = sqlStr + " ( suw911_no number primary key not null, suw911_rel number not null, ";

sqlStr = sqlStr + " suw911_ord number not null, suw911_depth number not null, ";

sqlStr = sqlStr + " suw911_subject varchar2(50) not null, suw911_id varchar2(10), ";

sqlStr = sqlStr + " suw911_name varchar2(20), suw911_email varchar2(40), ";

sqlStr = sqlStr + " suw911_passwd varchar2(10), suw911_content1 varchar2(4000) not null, ";

sqlStr = sqlStr + " suw911_content2 varchar2(4000), suw911_savefile varchar2(100), ";

sqlStr = sqlStr + " suw911_realfile varchar2(100), suw911_date date default sysdate not null, ";

sqlStr = sqlStr + " suw911_readnum number default 0 not null, suw911_ip varchar2(15) not null, ";

sqlStr = sqlStr + " suw911_emailyesno char(1), suw911_htmlyesno char(1), ";

sqlStr = sqlStr + " suw911_filesize number default 0, suw911_updatedate date default sysdate, ";

sqlStr = sqlStr + " suw911_boardno number default 0 not null )";

pstmt = conn.prepareStatement(sqlStr);

pstmt.executeUpdate();

pstmt.close();

conn.commit();

}catch(SQLException ex){

if( conn != null ) try { conn.rollback(); } catch(SQLException ex1) {}

out.println(ex.toString());

}catch(Exception ex){

if( conn != null ) try { conn.rollback(); } catch(SQLException ex1) {}

out.println(ex.toString());

}finally{

if ( conn != null ) try { conn.setAutoCommit(true); } catch(SQLException ex1) {}

if ( rs != null ) try { rs.close(); } catch(SQLException ex1) {}

if ( stmt != null ) try { stmt.close(); } catch(SQLException ex1) {}

if ( pstmt != null ) try { pstmt.close(); } catch(SQLException ex1) {}

if ( conn != null ) dbMgr.freeConnection("jspDB", conn);

// out.print("<meta http-equiv='refresh' content=0;URL=../index.jsp>");

}

% >