set feedback off
Rem
Rem 
Rem

CREATE OR REPLACE
PROCEDURE deleteEntries(tranSize IN INTEGER)
AS
    basedn   VARCHAR2(1000);
    temprdn   VARCHAR2(255);
    tempparentdn   VARCHAR2(255);
    baseEid  INTEGER;
    sqlCursor INTEGER;
    rows_processed INTEGER;
    comCounter  INTEGER;
    sqlCommand VARCHAR2 (4000);
    noofRows         INTEGER;
    minEntry         INTEGER;
    maxEntry         INTEGER;
    cursor catAttrs IS
        SELECT 'ct_'||LOWER(attrval) tblname
        FROM ds_attrstore
        where entryid = 912
        and attrname = 'orclindexedattribute';  
    cursor entries(dn VARCHAR2) is
        SELECT entryid from ct_dn where parentdn like dn 
        ORDER BY entryid;


BEGIN

       dstest.SetTrace( FALSE );
       dstest.TraceIn( 'deleteEntries' );
       dstest.Report( 'Commit Size is  ' ||':' || tranSize );

       SELECT count(*) 
       INTO noofRows
       from tempEID;
       IF noofRows = 0 then 
          BEGIN
          SELECT entryid, parentdn, rdn
          INTO baseEid, tempparentdn, temprdn
          FROM ct_dn
          WHERE parentdn||rdn = ( select NormDN 
                                  from dn_check)
          OR parentdn||rdn = ( select ' ,'||NormDN 
                                  from dn_check);
    
          EXCEPTIOn
             WHEN NO_DATA_FOUND then
            RAISE_APPLICATION_ERROR( -20110, 'Base dn specified is INVALID');
          END;
        IF tempparentdn = ' ,' THEN
            basedn := temprdn || ',%' ;
        ELSE
            basedn := tempparentdn || temprdn || ',%' ;
        END IF;

        IF baseEid >= 1000 THEN
          iNSERT INTO tempEID 
          values ( baseEid );
        END IF;
        FOR cur in entries(basedn) LOOP
           INSERT INTO tempEID values ( cur.entryid);
        END LOOP;
          commit;
      END IF;
        SELECT max(rownum), max(entryid) INTO noofRows, maxEntry from tempEID;

        comCounter := noofRows;
        minEntry := maxEntry;
    LOOP
       maxEntry := minEntry;
       select NVL(max(entryid), 0) Into minEntry from tempEID  where rownum < (comCounter - tranSize);
       dstest.Report( 'MaxEntryID   ' ||':' || maxEntry );
       dstest.Report( 'MinEntryID   ' ||':' || minEntry );
        EXIT WHEN comCounter <= 0  ;
        BEGIN
        for cur in catAttrs LOOP
            sqlCursor := dbms_sql.open_cursor;
            sqlCommand := 'DELETE ' || cur.tblname || 
	    ' where entryid IN ( select entryid from tempEID where entryid > '
            || minEntry || ' and entryid <= ' || maxEntry || ')' ;
            dbms_sql.parse(sqlCursor , sqlCommand, dbms_sql.v7);
            rows_processed := dbms_sql.execute(sqlCursor);
            dbms_sql.close_cursor(sqlCursor);
        END LOOP;

        DELETE from ds_attrstore 
        where entryid IN ( select entryid from tempEID 
                           where entryid > minEntry
                           and entryid <= maxEntry) ;
        DELETE from ds_battrstore 
        where entryid IN ( select entryid from tempEID 
                           where entryid > minEntry
                           and entryid <= maxEntry) ;
        DELETE from ct_dn 
        where entryid IN ( select entryid from tempEID 
                           where entryid > minEntry
                           and entryid <= maxEntry) ;
        COMMIT;
        comCounter := comCounter - tranSize;
        END;
   END LOOP;
    
END;
/

EXEC deleteEntries ( &1) ;
DROP TABLE dn_check;

exit
