rem
rem
rem  Bulk Modify Package
rem  ldapblkmod
rem
create or replace
PACKAGE ldapblkmod IS
    attNamestr    VARCHAR2(50);
    valStr        VARCHAR2(2000);
    normValStr    VARCHAR2(720);
    SUBTYPE  ATT_NAME  IS  attNamestr%TYPE;
    SUBTYPE  ATT_VAL   IS valStr%TYPE;
    SUBTYPE  ATT_NVAL  IS normValStr%TYPE; 
    TYPE ENTRY_LIST    IS TABLE OF INTEGER   INDEX BY BINARY_INTEGER;
    TYPE ATTR_VALLIST  IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
    TYPE ATTR_NVALLIST IS TABLE OF VARCHAR2(720) INDEX BY BINARY_INTEGER;
    TYPE ATTR_VERLIST  IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;

    PROCEDURE BulkModify( entries IN  ENTRY_LIST,
                           atName IN  ATT_NAME,
                           atVals  IN  ATTR_VALLIST,
                           atNVals IN  ATTR_NVALLIST,
                           atVer   IN  ATTR_VERLIST,
                           atIndex IN  CHAR,
                           atTime  IN  VARCHAR2,
                           atServer IN VARCHAR2,
                           oper    IN  CHAR,
                           rc      IN OUT INTEGER );
END ldapblkmod;
/
create or replace
PACKAGE BODY ldapblkmod AS
    PROCEDURE updateCatalog ( ID  IN INTEGER,
                              atName   IN ATT_NAME,
                              NormVal  IN ATT_NVAL,
                              oper     IN CHAR,
                              rc       IN OUT INTEGER)
    IS
        catalogName     VARCHAR2(32);
        atNameLower   VARCHAR2(32);
        sqlCommand      VARCHAR2(3000);
        sqlCursor       INTEGER;
        rows_processed  INTEGER;
        catalogUpdateProblem EXCEPTION;
    BEGIN
        atNameLower := lower(atName);
        rc := 0 ;
        catalogName := 'CT_' || atNameLower;
        sqlCursor := dbms_sql.open_cursor;
        IF ( oper = 'D' ) THEN
          sqlCommand := 'DELETE ' || catalogName || ' WHERE entryid = :tempid';
          dbms_sql.parse(sqlCursor, sqlCommand, dbms_sql.v7);
          dbms_sql.bind_variable( sqlCursor, ':tempid', ID );
          rows_processed := dbms_sql.execute(sqlCursor);
        ELSE
          sqlCommand := 'INSERT INTO '|| catalogName|| ' (attrvalue, entryid ) values ( :tempVal , :tempid )';
          dbms_sql.parse(sqlCursor, sqlCommand, dbms_sql.v7);
          dbms_sql.bind_variable( sqlCursor, ':tempVal', NormVal );
          dbms_sql.bind_variable( sqlCursor, ':tempid', ID );
          rows_processed := dbms_sql.execute(sqlCursor);
          IF rows_processed = 0 THEN
            RAISE catalogUpdateProblem;
          END IF;
        END IF;
        dbms_sql.close_cursor(sqlCursor);
        rc := 0;
        EXCEPTION
        WHEN catalogUpdateProblem THEN
           rc := 1; /* Insert did not go properly */
        WHEN OTHERS THEN
           rc := dbms_sql.last_sql_function_code;
           dbms_sql.close_cursor(sqlCursor);
    END updateCatalog;
    PROCEDURE updateAttrStore ( ID  IN INTEGER,
                              atName   IN ATT_NAME,
                              Val      IN ATT_VAL,
                              oper     IN CHAR,
                              version  IN VARCHAR2,
                              rc       IN OUT INTEGER)
    IS
       insertProblem     EXCEPTION;
    BEGIN
       IF oper = 'D' THEN
          DELETE ds_attrstore where entryid = ID and attrname = atName;
       ELSE
          INSERT INTO ds_attrstore (entryid, attrname, attrval, attrkind, attrver)
                      values( ID, atName, Val, 'u', version);
          IF SQL%ROWCOUNT = 0 THEN
             raise insertProblem;
          END IF;
       END IF;

       EXCEPTION
       WHEN insertProblem THEN
             rc := 1 ;  /* Insert into ds_attrstore problem */
       WHEN OTHERS THEN
             rc := SQLCODE ; /* SQL Execution code */
    END updateAttrStore;
    PROCEDURE BulkModify( entries IN  ENTRY_LIST,
                           atName IN  ATT_NAME,
                           atVals  IN  ATTR_VALLIST,
                           atNVals IN  ATTR_NVALLIST,
                           atVer   IN  ATTR_VERLIST,
                           atIndex IN  CHAR,
                           atTime  IN  VARCHAR2,
                           atServer IN VARCHAR2,
                           oper    IN  CHAR,
                           rc      IN OUT INTEGER )
    IS
       i            INTEGER;
       k            INTEGER;
       l            INTEGER;
       nosVals      INTEGER;
       nosEntries   INTEGER;
       limit        INTEGER;
       version      INTEGER;
       attrVer      VARCHAR2(50);
    BEGIN
       --INSERT INTO ds_LDAP_log values ( 1, ' Entere BulkMOdify');
       --INSERT INTO ct_telephonenumber values ( 5000, 'Amit Sharma');
       dstest.SetTrace( FALSE );
       dstest.TraceIn( 'BulkModify' );
       nosEntries := entries.LAST ;
       nosVals := atVals.LAST ;
       i := entries.FIRST;
       limit := rc;
       rc := 0;

       FOR couter IN 1..limit LOOP
       dstest.Report( 'Processing Entry ' ||':' || entries(i) );
       dstest.Report( 'Operation Being Performed ' ||':' || oper );
       dstest.Report( 'Attrib Index Flag ' ||':' || atIndex );
           EXIT when entries(i) = 0 ;
           IF oper != 'A' THEN
              updateAttrStore ( entries(i), atName, NULL , 'D', NULL , rc );
              IF rc != 0 THEN
                  dstest.Report( 'Error while deleting AttrStroe ');
                   return;
              END IF;
              IF atIndex = 'T' THEN
                   updateCatalog ( entries(i), atName, NULL , 'D' ,rc);
                   IF rc != 0 THEN
                        dstest.Report( 'Error while deleting Catalog ');
                        return;
                   END IF;
              END IF;
           END IF;
           FOR j IN atVals.FIRST..nosVals LOOP
                k := atNvals.FIRST;
                l := atVer.FIRST;
                EXIT when atVals(j) IS NULL ;

                version := atVer(l) + 1 ;
                attrVer := version || CHR(94) || atTime || CHR(94) || atServer; 
                dstest.Report( 'At Value ' ||':' || atVals(j));
                dstest.Report( 'Version ' ||':' || atVer(l));
                dstest.Report( 'Norm Values ' ||':' || atNVals(k));
                updateAttrStore (entries(i), atName, atVals(j),oper,attrVer,rc);
                IF rc != 0 THEN
                     dstest.Report( 'Error while Updating AttrStore ');
                     return;
                END IF;
                IF atIndex = 'T' THEN
                   updateCatalog ( entries(i), atName, atNVals(k), oper ,rc);
                   IF rc != 0 THEN
                     dstest.Report( 'Error while Updating Catalog ');
                     return;
                   END IF;
                END IF;
                k := k + 1;
                l := l + 1;
           END LOOP;
           i := i + 1;
        END LOOP;
       dstest.TraceOut( 'BulkModify' );
     END BulkModify ;
END ldapblkmod;
/
