티스토리 뷰

WEB TIP/Oracle

VARCHAR 제한 해제 + ASP

제프 2006. 7. 5. 11:52

MS-SQL는 TEXT가 있어서 괜찮은데, ORACLE은 4000자가 제한이라서;;;

INSERT
시퀀시 가져오기
sql = "select SQ_SEQ.nextval as seq from dual"
Set rs = conn.Execute(sql)
seq= rs("seq")
rs.Close

' INSERT 하기전 DB 초기화: empty_clob()
strSQL = "INSERT INTO MAILING_SP_TAB (SEQ, CONTENT) VALUES (" & SEQ & " & empty_clob())"
conn.Execute(strSQL)

' UPDATE
strSQL = "update 테이블 이름 set CONTENT=? where SEQ=?"
Set cmd = Server.CreateObject("ADODB.Command")

With cmd
.ActiveConnection = conn
.CommandType = adCmdText
.CommandText = strSQL

.Parameters.Append .CreateParameter("@CONTENT", adLongVarChar, adParamInput, lenB(CONTENT), CONTENT)
.Parameters.Append .CreateParameter("@SEQ", adInteger, adParamInput, , SEQ)
.Execute
End With


내용 보기
qr = "select NVL(dbms_lob.getlength(SP_MEMBER),0) as CLOBLength from MAILING_SP_TAB where SEQ="&seq
rs.open qr, conn
ClobLength = CLng(trim(rs("CLOBLength")))

rs.close


VIEW
'내용 가져오기
Dim packet_size, packet_count

packet_size = 3000

If ClobLength Mod packet_size = 0 Then
  packet_count = ClobLength/packet_size
Else
  packet_count = ClobLength/packet_size + 1
End If

qr = "SELECT seq, groupnm, enroll_id "

For i = 1 To packet_count
  qr = qr & ", dbms_lob.substr(SP_MEMBER, 3000, " & ((i-1)*packet_size + 1) & ") as group_sp "
Next

qr = qr & "FROM MAILING_SP_TAB WHERE SEQ = " & seq
rs.open qr, conn

if not rs.eof then
  For c = 1 To rs.Fields.count - 1
   qr = qr & rs(c)
  Next
  seq = trim(rs("seq"))
  groupnm = trim(rs("groupnm"))
  Bgroupnm = trim(rs("groupnm"))
  enroll_id = trim(rs("enroll_id"))
  gMember = replace(trim(rs("group_sp")),"""","'")
else
  response.write "<script type=""text/javascript"">" &_
         "alert (""필수값이 전송되지 않았습니다."");" &_
         "history.go(-2);" &_
         "</script>"
end if

'WEB TIP > Oracle' 카테고리의 다른 글

랭귀지 설정  (0) 2006.08.28
중복값 제거  (0) 2006.08.21
VARCHAR 제한 해제 + ASP  (0) 2006.07.05
Schedule  (0) 2006.05.10
기간내 날짜 추출  (0) 2006.04.26
CASE  (0) 2006.04.25
댓글
댓글쓰기 폼