急！向oracle中clob字段插入数据，insert的时候如何循环？

Question

向oracle中clob字段插入数据，insert的时候如何循环？
存储过程应该怎么样写？
这个是我写的循环，各位看看有什么问题吗？
while(i<=ceil(length(buffer)/200))
      loop   
        offset:= (i-1)* 200 +1;
        v_buffer:=substr(msg_detail_content,offset,200);
        DBMS_LOB.write(loclob,amount,offset,v_buffer);
        i:=i+1;
      end loop;

Answer 1

一般使用流的方式在程序（其它语言C#,Delphi等）中来实现，对于oracle，插入超过4000长度的字符都有麻烦！

Answer 2

我是用的pl/sql写的存储过程，从外部传入一个大于32767的字符串，插入到oracle数据库中，查资料说要写一个循环来处理，现在写了一个循环，发现传入的字符串过长的话还是不行。是我外部传传入的时候出错呢，还是存储过程循环写的有问题呢？现在搞不清楚。
不过还是谢谢一楼！

Answer 3

没看出这个循环有什么作用
楼主能否给出上下文
还有需求
报的是什么错误？
show errors一下看看

Answer 4

WHILE (i <= ceil(length(buffer)/200)) 
LOOP  
  offset := (i - 1)* 200 + 1; 
  v_buffer := substr(msg_detail_content, offset, 200); 
  DBMS_LOB.write(loclob, 200, 1, v_buffer); 
  i := i + 1; 
END LOOP;

Answer 5

我也不熟悉BLOB, CLOB的用法，你的函数使用应该有问题。应该如下才行：
first_direction := 'Follow I-75 across the Mackinac Bridge.';
amount := LENGTH(first_direction); --number of characters to write
offset := 1; --begin writing to the first character of the CLOB
DBMS_LOB.WRITE(directions, amount, offset, first_direction); 

Answer 6

如果不是拆分数据，第一个应该使用Write，后面的应该使用Writeappend才对！
   --Use DBMS_LOB.WRITE to begin
   first_direction := 'Follow I-75 across the Mackinac Bridge.';
   amount := LENGTH(first_direction);  --number of characters to write
   offset := 1; --begin writing to the first character of the CLOB
   DBMS_LOB.WRITE(directions, amount, offset, first_direction);    --Add some more directions using DBMS_LOB.WRITEAPPEND
   more_directions := ' Take US-2 west from St. Ignace to Blaney Park.'
                   || ' Turn north on M-77 and drive to Seney.'
                   || ' From Seney, take M-28 west to Munising.';
   DBMS_LOB.WRITEAPPEND(directions,
                        LENGTH(more_directions), more_directions); 

Answer 7

 procedure examinant_msg(msg_title varchar2,
                               msg_display_title varchar2,
                               msg_type_id int,
                               msg_date varchar2,
                               msg_level_id int,
                               msg_from_person_id varchar2,
                               msg_from_person_name varchar2,
                               msg_examinant_id varchar,
                               msg_copywrite_name varchar2,
                               msg_back_idea varchar,
                               msg_detail_content varchar2,
                               msg_release_date varchar2,
                               --09.08.10新添参数
                               msg_defaultgroup varchar2,--例如"XinXiZhongXinXiTongJianSheChu"
                               zs_dept varchar2 default '',
                               cs_dept varchar2 default '', 
                               --09.08.17新添参数
                               msg_out_id out number   
                               ) as
       msg_id number;
       msg_dept varchar2(2000);
       loclob clob;
       buffer   VARCHAR2(32766);     
       amount   NUMBER:=32766;     
       offset   NUMBER:=1; 
       v_buffer varchar2(2000); --接收截取的字符串
       i int:=1;
       
       begin
            select department_name into msg_dept from msg_department where lower(user_group_name) = lower(msg_defaultgroup);
            
            buffer:= msg_detail_content;
            --amount:= length(buffer);
            if msg_type_id = 2 then     
            insert into MESSAGE_NEW(MESSAGE_ID,
                                MESSAGE_TITLE,
                                DISPLAY_TITLE,
                                MESSAGE_TYPE_ID,
                                MESSAGE_DATE,
                                LEVEL_ID,
                                FROM_PERSON_ID,
                                FROM_PERSON_NAME,
                                EXAMINANT_ID,
                                COPYWRITER_NAME,
                                BACK_IDEA,
                                DETAIL_CONTENT,
                                RELEASE_DATE,
                                MESSAGE_STATE,
                                WHETHER_EXAMINANT,
                                --09.08.10新添参数
                                MESSAGE_DEPARTMENT,
                                ZS_DEPARTMENT,
                                CS_DEPARTMENT
                                ) 
            values(MSG_DIRECT_INSERT.Nextval,
                   msg_title,
                   msg_display_title,
                   msg_type_id,
                   to_date(msg_date,'yyyy-MM-dd'),
                   msg_level_id,
                   msg_from_person_id,
                   msg_from_person_name,
                   msg_examinant_id,
                   msg_copywrite_name,
                   msg_back_idea,
                   empty_clob(),
                   to_date(msg_release_date,'yyyy-mm-dd'),
                   0,
                   0,
                   
                   msg_dept,
                   zs_dept,
                   cs_dept
                   );
                  select MSG_DIRECT_INSERT.CURRVAL into msg_out_id from dual;
                  --为空的clob字段赋值（详细内容）
                     select detail_content into loclob from message_new 
                   where message_id=msg_out_id for update;      
                   DBMS_LOB.write(loclob,amount,offset,buffer);   
            else
                    
                   insert into MESSAGE_NEW(MESSAGE_ID,
                                MESSAGE_TITLE,
                                DISPLAY_TITLE,
                                MESSAGE_TYPE_ID,
                                MESSAGE_DATE,
                                LEVEL_ID,
                                FROM_PERSON_ID,
                                FROM_PERSON_NAME,
                                EXAMINANT_ID,
                                COPYWRITER_NAME,
                                BACK_IDEA,
                                DETAIL_CONTENT,
                                RELEASE_DATE,
                                MESSAGE_STATE,
                                WHETHER_EXAMINANT,
                                --09.08.10新添参数
                                MESSAGE_DEPARTMENT
                                ) 
                   values(MSG_DIRECT_INSERT.Nextval,
                                msg_title,
                                msg_display_title,
                                msg_type_id,
                                to_date(msg_date,'yyyy-MM-dd'),
                                msg_level_id,msg_from_person_id,
                                msg_from_person_name,
                                msg_examinant_id,
                                msg_copywrite_name,
                                msg_back_idea,
                                empty_clob(),
                                to_date(msg_release_date,'yyyy-mm-dd'),
                                0,
                                0,
                                --09.08.10新添参数
                                msg_dept     
                                );
                                select MSG_DIRECT_INSERT.CURRVAL into msg_out_id from dual;
                                --为空的clob字段赋值（详细内容）
                                select detail_content into loclob from message_new where message_id=msg_out_id for update;     
                                       
                                       while(i<=ceil(length(buffer)/200))
                                          loop   
                                               offset:= (i-1)* 200 +1;
                                               v_buffer:=substr(msg_detail_content,offset,200);
                                               amount:=length(v_buffer);
                                               DBMS_LOB.write(loclob,amount,offset,v_buffer);
                                               i:=i+1;
                                          end loop;
                                 --DBMS_LOB.write(loclob,amount,offset,buffer);
            end if;这个是存储过程的源码，在存入小于32k字符串时可以运行通过，如果字符串再长的话就会出错：
09/11/10 16:02:14 java.sql.SQLException: ORA-06502: PL/SQL: 数字或值错误 
ORA-06512: 在 line 1

Answer 8

你需要采用我上面说的使用write, writeappend来做，等等我修改一下你的代码。

Answer 9

PROCEDURE EXAMINANT_MSG(MSG_TITLE            VARCHAR2,
                        MSG_DISPLAY_TITLE    VARCHAR2,
                        MSG_TYPE_ID          INT,
                        MSG_DATE             VARCHAR2,
                        MSG_LEVEL_ID         INT,
                        MSG_FROM_PERSON_ID   VARCHAR2,
                        MSG_FROM_PERSON_NAME VARCHAR2,
                        MSG_EXAMINANT_ID     VARCHAR,
                        MSG_COPYWRITE_NAME   VARCHAR2,
                        MSG_BACK_IDEA        VARCHAR,
                        MSG_DETAIL_CONTENT   VARCHAR2,
                        MSG_RELEASE_DATE     VARCHAR2,
                        --09.08.10新添参数 
                        MSG_DEFAULTGROUP VARCHAR2, --例如"XinXiZhongXinXiTongJianSheChu" 
                        ZS_DEPT          VARCHAR2 DEFAULT '',
                        CS_DEPT          VARCHAR2 DEFAULT '',
                        --09.08.17新添参数 
                        MSG_OUT_ID OUT NUMBER) AS
  MSG_ID   NUMBER;
  MSG_DEPT VARCHAR2(2000);
  LOCLOB   CLOB;
  BUFFER   VARCHAR2(32766);
  AMOUNT   NUMBER := 32766;
  OFFSET   NUMBER := 1;
  V_BUFFER VARCHAR2(2000); --接收截取的字符串 
  I        INT := 1;
  B        BOOLEAN := FALSE;BEGIN
  SELECT DEPARTMENT_NAME
    INTO MSG_DEPT
    FROM MSG_DEPARTMENT
   WHERE LOWER(USER_GROUP_NAME) = LOWER(MSG_DEFAULTGROUP);  BUFFER := MSG_DETAIL_CONTENT;
  --amount:= length(buffer); 
  IF MSG_TYPE_ID = 2 THEN
    INSERT INTO MESSAGE_NEW
      (MESSAGE_ID,
       MESSAGE_TITLE,
       DISPLAY_TITLE,
       MESSAGE_TYPE_ID,
       MESSAGE_DATE,
       LEVEL_ID,
       FROM_PERSON_ID,
       FROM_PERSON_NAME,
       EXAMINANT_ID,
       COPYWRITER_NAME,
       BACK_IDEA,
       DETAIL_CONTENT,
       RELEASE_DATE,
       MESSAGE_STATE,
       WHETHER_EXAMINANT,
       --09.08.10新添参数 
       MESSAGE_DEPARTMENT,
       ZS_DEPARTMENT,
       CS_DEPARTMENT)
    VALUES
      (MSG_DIRECT_INSERT.NEXTVAL,
       MSG_TITLE,
       MSG_DISPLAY_TITLE,
       MSG_TYPE_ID,
       TO_DATE(MSG_DATE, 'yyyy-MM-dd'),
       MSG_LEVEL_ID,
       MSG_FROM_PERSON_ID,
       MSG_FROM_PERSON_NAME,
       MSG_EXAMINANT_ID,
       MSG_COPYWRITE_NAME,
       MSG_BACK_IDEA,
       EMPTY_CLOB(),
       TO_DATE(MSG_RELEASE_DATE, 'yyyy-mm-dd'),
       0,
       0,
       
       MSG_DEPT,
       ZS_DEPT,
       CS_DEPT);
    SELECT MSG_DIRECT_INSERT.CURRVAL INTO MSG_OUT_ID FROM DUAL;
    --为空的clob字段赋值（详细内容） 
    SELECT DETAIL_CONTENT INTO LOCLOB FROM MESSAGE_NEW WHERE MESSAGE_ID = MSG_OUT_ID FOR UPDATE;
    DBMS_LOB.WRITE(LOCLOB, AMOUNT, OFFSET, BUFFER);
  ELSE
  
    INSERT INTO MESSAGE_NEW
      (MESSAGE_ID,
       MESSAGE_TITLE,
       DISPLAY_TITLE,
       MESSAGE_TYPE_ID,
       MESSAGE_DATE,
       LEVEL_ID,
       FROM_PERSON_ID,
       FROM_PERSON_NAME,
       EXAMINANT_ID,
       COPYWRITER_NAME,
       BACK_IDEA,
       DETAIL_CONTENT,
       RELEASE_DATE,
       MESSAGE_STATE,
       WHETHER_EXAMINANT,
       --09.08.10新添参数 
       MESSAGE_DEPARTMENT)
    VALUES
      (MSG_DIRECT_INSERT.NEXTVAL,
       MSG_TITLE,
       MSG_DISPLAY_TITLE,
       MSG_TYPE_ID,
       TO_DATE(MSG_DATE, 'yyyy-MM-dd'),
       MSG_LEVEL_ID,
       MSG_FROM_PERSON_ID,
       MSG_FROM_PERSON_NAME,
       MSG_EXAMINANT_ID,
       MSG_COPYWRITE_NAME,
       MSG_BACK_IDEA,
       EMPTY_CLOB(),
       TO_DATE(MSG_RELEASE_DATE, 'yyyy-mm-dd'),
       0,
       0,
       --09.08.10新添参数 
       MSG_DEPT);
    SELECT MSG_DIRECT_INSERT.CURRVAL INTO MSG_OUT_ID FROM DUAL;
    --为空的clob字段赋值（详细内容） 
    SELECT DETAIL_CONTENT INTO LOCLOB FROM MESSAGE_NEW WHERE MESSAGE_ID = MSG_OUT_ID FOR UPDATE;
  
    WHILE (I <= CEIL(LENGTH(BUFFER) / 200)) LOOP
      OFFSET   := (I - 1) * 200 + 1;
      V_BUFFER := SUBSTR(MSG_DETAIL_CONTENT, OFFSET, 200);
      AMOUNT   := LENGTH(V_BUFFER);
      IF B THEN
        DBMS_LOB.WRITE(LOCLOB, AMOUNT, OFFSET, V_BUFFER);
      ELSE
        DBMS_LOB.WRITEAPPEND(LOCLOB, AMOUNT, V_BUFFER);
      END IF;
      I := I + 1;
    END LOOP;
    --DBMS_LOB.write(loclob,amount,offset,buffer); 
  END IF;

Answer 10

对不起，上面的错了。试试这个 ^_^ 一个用户只允许连续回复3次。什么新规定，好久（至少2年）没来CSDN了
PROCEDURE EXAMINANT_MSG(MSG_TITLE            VARCHAR2,
                        MSG_DISPLAY_TITLE    VARCHAR2,
                        MSG_TYPE_ID          INT,
                        MSG_DATE             VARCHAR2,
                        MSG_LEVEL_ID         INT,
                        MSG_FROM_PERSON_ID   VARCHAR2,
                        MSG_FROM_PERSON_NAME VARCHAR2,
                        MSG_EXAMINANT_ID     VARCHAR,
                        MSG_COPYWRITE_NAME   VARCHAR2,
                        MSG_BACK_IDEA        VARCHAR,
                        MSG_DETAIL_CONTENT   VARCHAR2,
                        MSG_RELEASE_DATE     VARCHAR2,
                        --09.08.10新添参数 
                        MSG_DEFAULTGROUP VARCHAR2, --例如"XinXiZhongXinXiTongJianSheChu" 
                        ZS_DEPT          VARCHAR2 DEFAULT '',
                        CS_DEPT          VARCHAR2 DEFAULT '',
                        --09.08.17新添参数 
                        MSG_OUT_ID OUT NUMBER) AS
  MSG_ID   NUMBER;
  MSG_DEPT VARCHAR2(2000);
  LOCLOB   CLOB;
  BUFFER   VARCHAR2(32766);
  AMOUNT   NUMBER := 32766;
  OFFSET   NUMBER := 1;
  V_BUFFER VARCHAR2(2000); --接收截取的字符串 
  I        INT := 1;
  B        BOOLEAN := TRUE;BEGIN
  SELECT DEPARTMENT_NAME
    INTO MSG_DEPT
    FROM MSG_DEPARTMENT
   WHERE LOWER(USER_GROUP_NAME) = LOWER(MSG_DEFAULTGROUP);  BUFFER := MSG_DETAIL_CONTENT;
  --amount:= length(buffer); 
  IF MSG_TYPE_ID = 2 THEN
    INSERT INTO MESSAGE_NEW
      (MESSAGE_ID,
       MESSAGE_TITLE,
       DISPLAY_TITLE,
       MESSAGE_TYPE_ID,
       MESSAGE_DATE,
       LEVEL_ID,
       FROM_PERSON_ID,
       FROM_PERSON_NAME,
       EXAMINANT_ID,
       COPYWRITER_NAME,
       BACK_IDEA,
       DETAIL_CONTENT,
       RELEASE_DATE,
       MESSAGE_STATE,
       WHETHER_EXAMINANT,
       --09.08.10新添参数 
       MESSAGE_DEPARTMENT,
       ZS_DEPARTMENT,
       CS_DEPARTMENT)
    VALUES
      (MSG_DIRECT_INSERT.NEXTVAL,
       MSG_TITLE,
       MSG_DISPLAY_TITLE,
       MSG_TYPE_ID,
       TO_DATE(MSG_DATE, 'yyyy-MM-dd'),
       MSG_LEVEL_ID,
       MSG_FROM_PERSON_ID,
       MSG_FROM_PERSON_NAME,
       MSG_EXAMINANT_ID,
       MSG_COPYWRITE_NAME,
       MSG_BACK_IDEA,
       EMPTY_CLOB(),
       TO_DATE(MSG_RELEASE_DATE, 'yyyy-mm-dd'),
       0,
       0,
       
       MSG_DEPT,
       ZS_DEPT,
       CS_DEPT);
    SELECT MSG_DIRECT_INSERT.CURRVAL INTO MSG_OUT_ID FROM DUAL;
    --为空的clob字段赋值（详细内容） 
    SELECT DETAIL_CONTENT INTO LOCLOB FROM MESSAGE_NEW WHERE MESSAGE_ID = MSG_OUT_ID FOR UPDATE;
    DBMS_LOB.WRITE(LOCLOB, AMOUNT, OFFSET, BUFFER);
  ELSE
  
    INSERT INTO MESSAGE_NEW
      (MESSAGE_ID,
       MESSAGE_TITLE,
       DISPLAY_TITLE,
       MESSAGE_TYPE_ID,
       MESSAGE_DATE,
       LEVEL_ID,
       FROM_PERSON_ID,
       FROM_PERSON_NAME,
       EXAMINANT_ID,
       COPYWRITER_NAME,
       BACK_IDEA,
       DETAIL_CONTENT,
       RELEASE_DATE,
       MESSAGE_STATE,
       WHETHER_EXAMINANT,
       --09.08.10新添参数 
       MESSAGE_DEPARTMENT)
    VALUES
      (MSG_DIRECT_INSERT.NEXTVAL,
       MSG_TITLE,
       MSG_DISPLAY_TITLE,
       MSG_TYPE_ID,
       TO_DATE(MSG_DATE, 'yyyy-MM-dd'),
       MSG_LEVEL_ID,
       MSG_FROM_PERSON_ID,
       MSG_FROM_PERSON_NAME,
       MSG_EXAMINANT_ID,
       MSG_COPYWRITE_NAME,
       MSG_BACK_IDEA,
       EMPTY_CLOB(),
       TO_DATE(MSG_RELEASE_DATE, 'yyyy-mm-dd'),
       0,
       0,
       --09.08.10新添参数 
       MSG_DEPT);
    SELECT MSG_DIRECT_INSERT.CURRVAL INTO MSG_OUT_ID FROM DUAL;
    --为空的clob字段赋值（详细内容） 
    SELECT DETAIL_CONTENT INTO LOCLOB FROM MESSAGE_NEW WHERE MESSAGE_ID = MSG_OUT_ID FOR UPDATE;
  
    WHILE (I <= CEIL(LENGTH(BUFFER) / 200)) LOOP
      OFFSET   := (I - 1) * 200 + 1;
      V_BUFFER := SUBSTR(MSG_DETAIL_CONTENT, OFFSET, 200);
      AMOUNT   := LENGTH(V_BUFFER);
      IF B THEN
        DBMS_LOB.WRITE(LOCLOB, AMOUNT, OFFSET, V_BUFFER);
        B := FALSE;
      ELSE
        DBMS_LOB.WRITEAPPEND(LOCLOB, AMOUNT, V_BUFFER);
      END IF;
      I := I + 1;
    END LOOP;
    --DBMS_LOB.write(loclob,amount,offset,buffer); 
  END IF;

Answer 11

不好意思，给你段代码，让你参考一下！
DECLARE
   directions CLOB;
   amount BINARY_INTEGER;
   offset INTEGER;
   first_direction VARCHAR2(100);
   more_directions VARCHAR2(500);
BEGIN
   --Delete any existing rows for 'Munising Falls' so that this
   --example can be executed multiple times
   DELETE
     FROM waterfalls
    WHERE falls_name='Munising Falls'; 
  
   --Insert a new row using EMPTY_CLOB(  ) to create a LOB locator
   INSERT INTO waterfalls
             (falls_name,falls_directions)
      VALUES ('Munising Falls',EMPTY_CLOB(  )); 
  
   --Retrieve the LOB locator created by the previous INSERT statement
   SELECT falls_directions
     INTO directions
     FROM waterfalls
    WHERE falls_name='Munising Falls'; 
  
   --Open the LOB; not strictly necessary, but best to open/close LOBs.
   DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE); 
  
   --Use DBMS_LOB.WRITE to begin
   first_direction := 'Follow I-75 across the Mackinac Bridge.';
   amount := LENGTH(first_direction);  --number of characters to write
   offset := 1; --begin writing to the first character of the CLOB
   DBMS_LOB.WRITE(directions, amount, offset, first_direction); 
  
   --Add some more directions using DBMS_LOB.WRITEAPPEND
   more_directions := ' Take US-2 west from St. Ignace to Blaney Park.'
                   || ' Turn north on M-77 and drive to Seney.'
                   || ' From Seney, take M-28 west to Munising.';
   DBMS_LOB.WRITEAPPEND(directions,
                        LENGTH(more_directions), more_directions); 
  
   --Add yet more directions
   more_directions := ' In front of the paper mill, turn right on H-58.'
                   || ' Follow H-58 to Washington Street. Veer left onto'
                   || ' Washington Street. You''ll find the Munising'
                   || ' Falls visitor center across from the hospital at'
                   || ' the point where Washington Street becomes'
                   || ' Sand Point Road.';
   DBMS_LOB.WRITEAPPEND(directions,
                        LENGTH(more_directions), more_directions); 
  
   --Close the LOB, and we are done.
   DBMS_LOB.CLOSE(directions);
END;