오라클 CLOB 타입에 대용량의 문자열을 넣는 프로시저 예제
PROCEDURE ProcMasterBoardUpdate ( p_title IN MasterBoard.title%TYPE, p_html IN MasterBoard.html%TYPE, p_absoluteno IN MasterBoard.absoluteno%TYPE, p_content IN long, p_errorcode OUT NUMBER ) AS v_clob_selected clob; v_write_amt number; v_write_offset number := 1; v_buf long; BEGIN p_errorcode := 1; UPDATE masterboard SET title=p_title, html=p_html, content=EMPTY_CLOB() where absoluteno=p_absoluteno RETURNING content INTO v_clob_selected; if p_content is Not Null then v_buf := p_content; v_write_amt := length(v_buf); DBMS_LOB.WRITE(v_clob_selected, v_write_amt, v_write_offset, v_buf); end if; commit; EXCEPTION WHEN OTHERS THEN p_errorcode := 100; End ProcMasterBoardUpdate; PROCEDURE ProcMasterBoardInsert ( p_owner IN MasterBoard.owner%TYPE, p_board_no IN MasterBoard.board_no%TYPE, p_title IN MasterBoard.title%TYPE, p_writer IN MasterBoard.writer%TYPE, p_password IN MasterBoard.password%TYPE, p_addr IN MasterBoard.addr%TYPE, p_no IN MasterBoard.no%TYPE, p_indexedno IN MasterBoard.indexedno%TYPE, p_series IN MasterBoard.series%TYPE, p_connectno IN MasterBoard.connectno%TYPE, p_html IN MasterBoard.html%TYPE, p_content IN long, p_errorcode OUT NUMBER ) AS v_clob_selected clob; v_write_amt number; v_write_offset number := 1; v_buf long; Begin p_errorcode := 1; if p_connectno>0 then UPDATE masterboard SET indexedNo= indexedNo-1 WHERE no=p_no and indexedNo < p_indexedno; commit; INSERT INTO MasterBoard (in_date,owner,board_no,title,writer,password, addr, no, indexedno, series, connectno, html, absoluteno) VALUES (sysdate,p_owner,p_board_no,p_title,p_writer,p_password, p_addr, p_no, p_indexedno-1, p_series+1, p_connectno, p_html, 0) RETURNING content INTO v_clob_selected; else INSERT INTO MasterBoard (in_date,owner,board_no,title,writer,password, addr, no, indexedno, series, connectno, html, absoluteno) VALUES (sysdate,p_owner,p_board_no,p_title,p_writer,p_password, p_addr, p_no, p_indexedno, p_series, p_connectno, p_html, 0) RETURNING content INTO v_clob_selected; end if; if p_content is Not Null then v_buf := p_content; v_write_amt := length(v_buf); DBMS_LOB.WRITE(v_clob_selected, v_write_amt, v_write_offset, v_buf); end if; commit; EXCEPTION WHEN OTHERS THEN p_errorcode := 100; End ProcMasterBoardInsert;

* asp 코드

set objDB = Server.CreateObject("Netiami.DB")
set writeMail=server.createobject("adodb.command")
    with writeMail
         .ActiveConnection=objDB.GetConnectionString()
         .CommandText = "ProcMailInsert"
         .CommandType = 4
         .Parameters.Append = writeMail.CreateParameter("p_id", 200, 1, 36, session("mst_id"))
         .Parameters.Append = writeMail.CreateParameter("p_title", 200, 1, 1000, title)
         .Parameters.Append = writeMail.CreateParameter("p_content", 201, 1, Lenb(Body), Body)
         .Parameters.Append = writeMail.CreateParameter("p_errorcode", 3, 2)
         .Execute()
    end with
    result = Cint(writeMail.Parameters("p_errorcode"))
    set writeMail=nothing
   set objDB=nothing

* 저장 테이블

CREATE TABLE mailing_info (
      no NUMBER DEFAULT 0 PRIMARY KEY NOT NULL,
      id VARCHAR2(36) NOT NULL,
      title VARCHAR2(1000) NOT NULL,
      content CLOB DEFAULT EMPTY_CLOB(),
      state CHAR(1) DEFAULT 'R' NOT NULL,
      regday DATE DEFAULT SYSDATE NOT NULL
)

* 프로시저

CREATE OR REPLACE PROCEDURE ProcMailInsert (
      p_id IN mailing_info.id%TYPE,
      p_title IN mailing_info.title%TYPE,
      p_content IN long,
      p_errorcode OUT NUMBER
)
IS
      v_clob_selected clob;
      v_write_amt number;
      v_write_offset number := 1;
      v_buf long;
Begin
      p_errorcode := 0;
      INSERT INTO mailing_info (id, title) VALUES (p_id, p_title) RETURNING content INTO v_clob_selected;

      if p_content is Not Null then
           v_buf := p_content;
           v_write_amt := length(v_buf);
           DBMS_LOB.WRITE(v_clob_selected, v_write_amt, v_write_offset, v_buf);
      end if;
      commit;
End ProcMailInsert;
/

* 프로시저 end 구문 위에 EXCEPTION -- 예외 처리(에러) 를 추가가능

#
# PHP4, ORACLE BLOB TYPE INSERT, SELECT, SEARCH
#

 create table temp01 (
 absno number(11),
 tf1 blob,
 tf2 blob
 );

<?
 $v1="가나다라마바사";
 $v2="abcdefghijklmn";

#입력부분

 //새로운 빈 descriptor LOB를 초기화
 $blob1 = OCINewDescriptor($connect, OCI_D_LOB);
 $blob2 = OCINewDescriptor($connect, OCI_D_LOB);

 $strSQL="insert into temp01 (absno,tf1,tf2) values (1,EMPTY_BLOB(),EMPTY_BLOB()) returning tf1,tf2 into :the_blob1,:the_blob2";
 $stmt=OCIParse($connect,$strSQL);

 OCIBindByName($stmt,":the_blob1",&$blob1,-1,OCI_B_BLOB);
 OCIBindByName($stmt,":the_blob2",&$blob2,-1,OCI_B_BLOB);

 $result=OCIExecute($stmt,OCI_DEFAULT);

 $result=$blob1->save($v1);
 $blob1->free();
 $result.=$blob2->save($v2);
 $blob2->free();

 OCIcommit($connect);

 if ($result) {
 echo "YES :) ";
 } else {
 echo "NOT :( ";
 }

#셀렉트 부분

 $strSQL = "select absno,tf1,tf2 from temp01";
 $stmt = OCIParse($connect,$strSQL);

 OCIDefineByName($stmt,"ABSNO",&$absno);
 OCIDefineByName($stmt,"TF1",&$tf1);
 OCIDefineByName($stmt,"TF2",&$tf2);

 OCIExecute($stmt,OCI_DEFAULT);
 //$result=OCIFetch($stmt);

 while ($result=OCIFetch($stmt)) {

 # LOB data는 Object를 다음과 같이 풀어준다.
 $tf1 = $tf1->load();
 $tf2 = $tf2->load();

 echo "#absno: ".$absno." ------------------------------------------------- ";
 echo "#tf1: ".$tf1." ------------------------------------------------- ";
 echo "#tf2: ".$tf2." ------------------------------------------------- ";
 }

#UPDATE

 //새로운 빈 descriptor LOB를 초기화
 $blob1 = OCINewDescriptor($connect,OCI_D_LOB);
 $hex=bin2hex('가나다라');
 $strSQL = "update temp01 set absno=2,tf1=empty_blob() where dbms_lob.instr(tf1,hextoraw('$hex')) <> 0 returning tf1 into :the_blob1";

 $stmt = OCIParse($connect,$strSQL);

 OCIBindByName($stmt,":the_blob1",&$blob1,-1,OCI_B_BLOB);
 OCIExecute($stmt,OCI_DEFAULT);

 $blob1->save("수정한 내용입니다.");
 $blob1->free();

#검색 부분

 $hex=bin2hex('asdf');
 $strSQL=" select absno from temp01 where dbms_lob.instr(tf1,hextoraw('$hex')) <> 0 ";
 $stmt=OCIParse($connect,$strSQL);
 OCIDefineByName($stmt,"absno",&$absno);
 $result=OCIFetch($stmt);

 OCIFreeStatement($stmt);
 OCILogoff($connect);
?>