CREATE OR REPLACE PROCEDURE DUMMY(O_ERRMSG OUT VARCHAR2) IS
--DECLARE VARIABLES
BEGIN
      DELETE FROM AA;
      INSERT INTO AA (
                              SELECT
                                      TABLE_NAME,
                                      COLUMN_NAME,
                                      DATA_TYPE,
                                      DATA_LENGTH
                              FROM USER_TAB_COLS
      WHERE COLUMN_NAME IN ('AGE')
      AND DATA_LENGTH IN(10,6)
      COMMIT;
EXCEPTION
      WHEN NO_DATA_FOUND THEN
           AOS_ERRMSG := SQLCODE ||' : ' ||SUBSTR(SQLERRM,1,200);
           PLOG.ERROR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
           PLOG.ERROR('AA - RECORDS NOT AVAILABLE' || ' ' ||O_ERRMSG);           
      RETURN;
      WHEN OTHERS THEN
           AOS_ERRMSG := SQLCODE ||' : ' ||SUBSTR(SQLERRM,1,200);
           PLOG.ERROR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
           PLOG.ERROR('AA - UNEXPECTED ERROR' || ' ' ||O_ERRMSG);
      RETURN;    
END;   

CREATE OR REPLACE PROCEDURE TEST(O_ERRMSG OUT VARCHAR2) IS
  CURSOR PF_CUR IS
  SELECT TABLE_NAME,COLUMN_NAME FROM AA; 
  TYPE V_PF IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
  V_SQLC V_PFOLIO;
  E_KEY RAW(128) := UTL_RAW.CAST_TO_RAW('33669911');
  TAB_NAME VARCHAR2(4000);
  COL_NAME VARCHAR2(4000);
  ENCRYPTED_VALUE RAW(2048);
  ENCRYPTED_STRING RAW(64);
    
BEGIN

  DUMMY(O_ERRMSG);

  FOR T_CUR IN PF_CUR 
      LOOP
        
      TAB_NAME := T_CUR.TABLE_NAME;
      COL_NAME := T__CUR.COLUMN_NAME;
      
      EXECUTE IMMEDIATE 'SELECT ' || COL_NAME ||' FROM '|| TAB_NAME ||' WHERE ROWNUM<10'
      BULK COLLECT INTO V_SQLC;
                  
        DBMS_OUTPUT.PUT_LINE(V_SQLC.COUNT);
              
        FOR I IN 1..V_SQLC.COUNT
          LOOP
          
          DBMS_OUTPUT.PUT_LINE(V_SQLC.COUNT);
          
            ENCRYPTED_VALUE := DBMS_CRYPTO.ENCRYPT(SRC => UTL_RAW.CAST_TO_RAW (V_SQLC(I)), 
                                                   TYP => DBMS_CRYPTO.DES_CBC_PKCS5, 
                                                   KEY => E_KEY);
                                                   
            DBMS_OUTPUT.PUT_LINE('ENCRYPTED : ' || RAWTOHEX(ENCRYPTED_VALUE));
            
            ENCRYPTED_STRING := SUBSTR(RAWTOHEX(ENCRYPTED_VALUE),1,10);
            
            DBMS_OUTPUT.PUT_LINE('ENCRYPTED : ' || ENCRYPTED_STRING);
  
            EXECUTE IMMEDIATE 'UPDATE '||TAB_NAME ||
                              ' SET '||COL_NAME ||' = '|| ENCRYPTED_STRING
                              ||' WHERE CURRENT OF PF_CUR'; -- PROBLEM GOES HERE…..
                    
            INSERT INTO BB VALUES (TAB_NAME,COL_NAME,V_SQLC(I),'Y',SYSDATE,USER); -- FOR AUDIT PURPOSE
            COMMIT;
        END LOOP;    
      END LOOP;  
      
EXCEPTION
      WHEN NO_DATA_FOUND THEN
           AOS_ERRMSG := SQLCODE ||' : ' ||SUBSTR(SQLERRM,1,200);
           PLOG.ERROR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
           PLOG.ERROR('RECORDS NOT AVAILABLE' || ' ' ||O_ERRMSG);           
      RETURN;
      WHEN OTHERS THEN
           AOS_ERRMSG := SQLCODE ||' : ' ||SUBSTR(SQLERRM,1,200);
           PLOG.ERROR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
           PLOG.ERROR('UNEXPECTED ERROR' || ' ' ||O_ERRMSG);
      RETURN;     

DECLARE
  CURSOR pf_cur
  IS
    SELECT table_name, column_name
      FROM aa;

  TYPE v_pf IS TABLE OF VARCHAR2 (32767)
    INDEX BY BINARY_INTEGER;

  e_key              RAW (128)        := UTL_RAW.cast_to_raw ('33669911');
  tab_name           VARCHAR2 (4000);
  col_name           VARCHAR2 (4000);
  encrypted_value    VARCHAR2 (100);
  encrypted_string   RAW (64);

  TYPE cursor_type IS REF CURSOR;

  v_cursor           cursor_type;
  l_string           VARCHAR2 (32700);

  TYPE r IS RECORD (
    column_data   VARCHAR2 (32000),
    rid           ROWID
  );

  TYPE t IS TABLE OF r;

  p                  t                := t ();
  l_counter          PLS_INTEGER      := 2;
BEGIN
  --DUMMY(O_ERRMSG);
  FOR t_cur IN pf_cur
  LOOP
    l_string := 'SELECT ' || t_cur.column_name || ',rowid FROM ' || t_cur.table_name || ' WHERE ROWNUM<10';

    OPEN v_cursor FOR l_string;

    LOOP
      p.EXTEND (l_counter);

      FETCH v_cursor
       INTO p (l_counter).column_data, p (l_counter).rid;

      DBMS_OUTPUT.put_line ('TKSKLDK ' || p (l_counter).column_data);
      l_counter := l_counter + 1;
      EXIT WHEN v_cursor%NOTFOUND;
    END LOOP;

    FOR i IN p.FIRST .. p.LAST
    LOOP
      encrypted_value := DBMS_RANDOM.STRING (1, 10);

      EXECUTE IMMEDIATE    'UPDATE '
                        || t_cur.table_name
                        || ' SET '
                        || t_cur.column_name
                        || ' = '
                        || ''''
                        || TO_CHAR (encrypted_value)
                        || ''''
                        || ' WHERE rowid  = : rid'
                  USING p (i).rid;                                                     -- PROBLEM GOES HERE…..

      COMMIT;
    END LOOP;
  END LOOP;
END;

V_SQLS := 'SELECT 1 INTO ' || PR_DATA ||' 

EXECUTE IMMEDIATE V_SQLS INTO PR_DATA USING TAB_NAME,COL_NAME,V_SQLC(I);