plsql - Getting error PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

Question

I am getting

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

Please help me out with this.

My code is : -

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;

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

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.

Answer 1

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 Magazine
• Oracle Documentation