字符串缓冲区太小ORA-06502

Question

我在游标循环中连接varchar2数据类型时遇到了问题。

过程在循环中迭代，以生成用于批处理的插入和删除操作的in子句。该过程将对每1000个帐号批处理运行。

有没有其他方法可以做到这一点？

create or replace PROCEDURE LOAD_ITEM_DATA_TO_CONSOLIDATE(updatecount OUT NUMBER
)
IS
  cnt       NUMBER := 0;
  c_limit CONSTANT PLS_INTEGER DEFAULT 1000;
  search_id varchar2(32727);
  TYPE account_array
    IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
  l_data    ACCOUNT_ARRAY;
  CURSOR account_cursor IS
    SELECT DISTINCT account_no AS account_num
    FROM   item_temp;
BEGIN
    OPEN account_cursor;

    LOOP
        FETCH account_cursor bulk collect INTO l_data limit c_limit;

        search_id := '''';

        FOR i IN 1 .. l_data.count LOOP
            IF( i != 1 ) THEN
              search_id := search_id
                           || ','
                           || ''''
                           || l_data(i)
                           || '''';
            ELSE
              search_id := search_id
                           || l_data(i)
                           || '''';
            END IF;
        END LOOP;

        BEGIN

        SAVEPOINT move_data_to_temp_table;

        EXECUTE IMMEDIATE 'delete from item where ACCOUNT_NO IN('||search_id||')';

        EXECUTE IMMEDIATE 'insert into item(ID,ACCOUNT_NO,ITEM_ID,ITEM_VALUE) select HIBERNATE_SEQUENCE.nextval,temp.ACCOUNT_NO,temp.ITEM_ID,temp.ITEM_VALUE from item_TEMP temp     where ACCOUNT_NO IN('||search_id||')';

        cnt := cnt + SQL%rowcount;

        COMMIT;

        EXCEPTION WHEN OTHERS THEN ROLLBACK to move_data_to_temp_table;

        END;

        EXIT WHEN account_cursor%NOTFOUND;

    END LOOP;

    updatecount := cnt;

    CLOSE account_cursor;

END LOAD_ITEM_DATA_TO_CONSOLIDATE;

Answer 1

这似乎有些过度设计。为什么不就这样呢？

create or replace PROCEDURE LOAD_ITEM_DATA_TO_CONSOLIDATE
    (updatecount OUT NUMBER)
IS
BEGIN
    delete from item 
    where ACCOUNT_NO IN ( SELECT account_no
                          FROM   item_temp);

   insert into item(ID,ACCOUNT_NO,ITEM_ID,ITEM_VALUE) 
   select HIBERNATE_SEQUENCE.nextval, temp.ACCOUNT_NO, temp.ITEM_ID, temp.ITEM_VALUE 
   from item_TEMP temp  ;

    updatecount := SQL%rowcount;

END LOAD_ITEM_DATA_TO_CONSOLIDATE;