获取错误PLS-00435:不能在FORALL内部使用没有BULK In-BIND的DML语句 [英] Getting error PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

问题描述

我得到

错误(68,3):PLS-00435:不能在FORALL内部使用没有BULK In-BIND的DML语句.

Error(68,3): PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL.

请帮助我解决这个问题.

Please help me out with this.

我的代码是:-

create or replace  PACKAGE BODY    FIBRE_TOOLS AS

g_package_name varchar2(30):='FIBRE_TOOLS';
g_proc_name varchar2(30);   

.. .. ..

   procedure prc_purge(p_nb_month IN number default 210) is
   reqSelec VARCHAR2(4000);
   reqDELDES VARCHAR2(4000);
   reqDELINS VARCHAR2(4000);

   TYPE Curseur IS REF CURSOR;
   c_desinscription Curseur;

TYPE selREC IS RECORD (

  EMAIL     desinscription.EMAIL%type,
  IDRA      desinscription.IDRA%type,
  D_DATE    desinscription.desinscription_date%type
  );

   TYPE selTABLE IS TABLE OF selREC;
   ListeFIB selTABLE;

 BEGIN  
    reqSelec :='select EMAIL,IDRA,desinscription_date from desinscription where desinscription_date < trunc(add_months(sysdate,-'||p_nb_month||'))';

    reqDELDES := 'DELETE FROM DESINSCRIPTION WHERE EMAIL=:1 AND IRDA=:2';

    reqDELINS := 'DELETE FROM INSCRIPTION WHERE EMAIL=:1 AND IDRA=:2 AND INSCRIPTION_DATE < TRUNC(:3)';
    prc_log('Begining of purging procedure');
    open c_desinscription for reqSelec;
    LOOP
    fetch c_desinscription bulk collect into ListeFIB LIMIT 10000;
    EXIT WHEN ListeFIB.count = 0;
    FORALL i in ListeFIB.first.. ListeFIB.last
        EXECUTE IMMEDIATE reqDELDES USING  ListeFIB.EMAIL,ListeFIB.IRDA;
        EXECUTE IMMEDIATE reqDELINS USING ListeFIB.EMAIL,ListeFIB.IDRA,ListeFIB.D_DATE;
        COMMIT;
        EXIT WHEN c_desinscription%NOTFOUND;
    END LOOP;
    close c_desinscription;
    COMMIT;
    prc_log('Ending of purging procedure');
end prc_purge;
end FIBRE_TOOLS;

我正在尝试根据在条件下被选中的两列从两个表中删除数据，即nb_months.

I am trying to delete data from two tables based on the two columns getting selected on a criteria I.e. nb_months.

我认为问题在于表类型和记录类型.我很困惑该怎么做.根据我的知识记录类型，应与FORALL一起使用.请帮助我，因为这非常关键.

I think problem is with table type and record type. I am confused which way it can be done. As per my knowledge record type shall be used with FORALL. kindly help me on this, as it is very critical.

推荐答案

您正在使用带有FORALL的DML语句，但未绑定使用的集合-

You're using a DML statement with a FORALL but without binding the collection used - Oracle doesn't allow for that.

用绑定的集合替换执行立即数，应该可以工作

Replace execute immediates with the binded collection, should work

EXECUTE IMMEDIATE reqDELDES USING  ListeFIB(i).EMAIL,ListeFIB(i).IRDA;
EXECUTE IMMEDIATE reqDELINS USING ListeFIB(i).EMAIL,ListeFIB(i).IDRA,ListeFIB(i).D_DATE;

请参阅这些文章中的更多示例:

See more examples in these articles:

• Oracle杂志
• Oracle文档

• Oracle Magazine
• Oracle Documentation

这篇关于获取错误PLS-00435:不能在FORALL内部使用没有BULK In-BIND的DML语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！