CallableStatementCallback; uncategorized SQLException for SQL [{call PROC_INDEX_ENABLE_DISABLE(?, ?, ?, ?)}]; 
SQL state [null]; error code [17410]; 
No more data to read from socket; nested exception is java.sql.SQLException: No more data to read from socket

CREATE TABLE  GROUP_BRANCHES(BRN_GROUP_ID  VARCHAR2(20), BRANCH_NAME VARCHAR2(20));
INSERT INTO "GROUP_BRANCHES" (BRN_GROUP_ID, BRANCH_NAME) VALUES ('B1', 'ABC');
INSERT INTO "GROUP_BRANCHES" (BRN_GROUP_ID, BRANCH_NAME) VALUES ('B1', 'XYZ');
INSERT INTO "GROUP_BRANCHES" (BRN_GROUP_ID, BRANCH_NAME) VALUES ('ABC', 'ABC');
INSERT INTO "GROUP_BRANCHES" (BRN_GROUP_ID, BRANCH_NAME) VALUES ('XYZ', 'XYZ');
DROP TABLE  LIST_PARTITIONED_TABLE; 
CREATE TABLE LIST_PARTITIONED_TABLE(BRANCH VARCHAR2(4 CHAR) NOT NULL ENABLE,REFERENCE_NUMBER VARCHAR2(20 CHAR) NOT NULL ENABLE )
PARTITION BY LIST( BRANCH)( PARTITION ABC VALUES('0ABC'));
create or replace TYPE SCHARTABLE  IS TABLE OF VARCHAR2(20);

create or replace PROCEDURE PROC_INDEX_ENABLE_DISABLE(
    v_table_name IN VARCHAR2,
    vMode         IN VARCHAR2,
    vStatus       IN OUT VARCHAR2,
    v_brn_group_id       IN VARCHAR2) 
IS
  v_Index_Name      VARCHAR2(30);
  v_Constraint_Name VARCHAR2(30);
  v_Process_Time    NUMBER;
  v_Owner_Name      VARCHAR2(10) := 'schemaname';
  vErrStr           VARCHAR2(4000);
  v_MV_Wait_Time    NUMBER;
  v_isJobNameExists USER_JOBS.JOB%TYPE;
  C_START_STATUS VARCHAR(2)                  := '-1';
  C_FINAL_STATUS VARCHAR(1)                  := '0';
  C_PRGNAME   VARCHAR(200):= 'PROC_INDEX_ENABLE_DISABLE';
  C_STARTED     VARCHAR2(7)                      := 'STARTED';
  C_COMPLETED   VARCHAR2(10)                     := 'COMPLETED';
  C_ERROR_STACK VARCHAR2(20)                     := 'Error_Stack...';
  C_FAILURE     VARCHAR2(10)                     := 'F';
  C_SUCCESS     VARCHAR2(10)                     := 'S';
  list_partition_name schartable                 := schartable();
TYPE tbPartitioned
IS
  TABLE OF USER_IND_PARTITIONS.PARTITION_NAME%TYPE;
  tPartitioned tbPartitioned;
TYPE tbIndex_Name
IS
  TABLE OF USER_INDEXES.INDEX_NAME%TYPE;
  tIndex_Name tbIndex_Name;
  PROCEDURE SP_LOAD_PARTITIONS
  IS
  BEGIN
    IF vMode IN ('D','E') THEN
      SELECT PARTITION_NAME bulk collect
      INTO list_partition_name
      FROM XMLTABLE('/ROWSET/ROW' PASSING DBMS_XMLGEN.GETXMLTYPE ('SELECT TABLE_NAME, HIGH_VALUE ,PARTITION_NAME FROM USER_TAB_PARTITIONS U') COLUMNS TABLE_NAME VARCHAR2(100), HIGH_VALUE VARCHAR2(200), PARTITION_NAME VARCHAR2(200)) XT ,
        GROUP_BRANCHES MBI
      WHERE TRIM(MBI.BRANCH_NAME) = REPLACE( TRIM(XT.HIGH_VALUE),'''',NULL)
      AND trim(MBI.BRN_GROUP_ID)     = trim(v_brn_group_id)
      AND XT.TABLE_NAME             = v_table_name;
    END IF ;
  END SP_LOAD_PARTITIONS;
  PROCEDURE SP_DELETE_PARTITIONS
  IS
  BEGIN
    IF vMode IN ('D','E') THEN
      list_partition_name.delete;
    END IF ;
  END SP_DELETE_PARTITIONS;
  PROCEDURE SP_NON_PARTITION_IDXS_ENABLE
  IS
  BEGIN
    FOR echIndxNM IN
    (SELECT INDEX_NAME
    FROM USER_INDEXES
    WHERE STATUS    = 'UNUSABLE'
    AND TABLE_NAME  = v_table_name
    AND TABLE_OWNER = v_Owner_Name
    AND PARTITIONED = 'NO'
    )
    LOOP
      --Rebuilding the Indexes
      EXECUTE IMMEDIATE ' ALTER INDEX '||echIndxNM.INDEX_NAME ||' REBUILD ONLINE PARALLEL 16';
      --Resetting the Parallel Degree back to 1
      EXECUTE IMMEDIATE ' ALTER INDEX '||echIndxNM.INDEX_NAME ||' PARALLEL 1';
    END LOOP;
  END SP_NON_PARTITION_IDXS_ENABLE;
  PROCEDURE SP_PARTITION_IDXS_ENABLE
  IS
  BEGIN
    FOR rec_indx IN
    ( SELECT DISTINCT UIP.PARTITION_NAME ,
      UI.INDEX_NAME
    FROM USER_IND_PARTITIONS UIP ,
      USER_INDEXES UI,
      USER_PART_INDEXES UPI
    WHERE UI.INDEX_NAME = UIP.INDEX_NAME
    AND UI.INDEX_NAME   = UPI.INDEX_NAME
    AND UI.TABLE_NAME   = UPI.TABLE_NAME
    AND UI.TABLE_NAME   = v_table_name
    AND UI.TABLE_OWNER  = v_Owner_Name
    AND UIP.STATUS      = 'UNUSABLE'
    AND PARTITIONED     = 'YES'
    AND UIP.COMPOSITE   ='NO'
    AND 1               =
      CASE
        WHEN UPI.PARTITIONING_TYPE = 'LIST'
        AND UIP.PARTITION_NAME MEMBER OF list_partition_name
        THEN 1
        WHEN UPI.PARTITIONING_TYPE <> 'LIST'
        THEN 1
        ELSE 0
      END
    )
    LOOP
      --Rebuilding the Partitions.
      EXECUTE IMMEDIATE ' ALTER INDEX '||rec_indx.INDEX_NAME||' REBUILD PARTITION '||rec_indx.PARTITION_NAME|| '  PARALLEL 16';
      --Resetting the Parallel Degree back to 1
      EXECUTE IMMEDIATE ' ALTER INDEX '||rec_indx.INDEX_NAME ||' PARALLEL 1';
    END LOOP;
  END SP_PARTITION_IDXS_ENABLE;
  PROCEDURE SP_SUBPARTITION_IDXS_ENABLE
  IS
  BEGIN
    FOR rec_indx IN
    ( SELECT DISTINCT UIS.SUBPARTITION_NAME ,
      UI.INDEX_NAME BULK COLLECT
    INTO tPartitioned ,
      tIndex_Name
    FROM USER_INDEXES UI ,
      USER_IND_PARTITIONS UIP ,
      USER_IND_SUBPARTITIONS UIS,
      USER_PART_INDEXES UPI
    WHERE UI.INDEX_NAME    = UIP.INDEX_NAME
    AND UI.INDEX_NAME      = UIS.INDEX_NAME
    AND UI.INDEX_NAME      = UPI.INDEX_NAME
    AND UI.TABLE_NAME      = UPI.TABLE_NAME
    AND UIP.PARTITION_NAME = UIS.PARTITION_NAME
    AND UI.TABLE_NAME      = v_table_name
    AND UI.TABLE_OWNER     = v_Owner_Name
    AND PARTITIONED        = 'YES'
    AND UIP.COMPOSITE      ='YES'
    AND UIS.STATUS        IN ('UNUSABLE','N/A')
    AND 1                  =
      CASE
        WHEN UPI.PARTITIONING_TYPE = 'LIST'
        AND UIP.PARTITION_NAME MEMBER OF list_partition_name
        THEN 1
        WHEN UPI.PARTITIONING_TYPE <> 'LIST'
        THEN 1
        ELSE 0
      END
    )
    LOOP
      --Rebuilding the Partitions.
      EXECUTE IMMEDIATE ' ALTER INDEX '||rec_indx.index_name||' REBUILD SUBPARTITION '||rec_indx.SUBPARTITION_NAME|| '  PARALLEL 16';
      --Resetting the Parallel Degree back to 1
      EXECUTE IMMEDIATE ' ALTER INDEX '||rec_indx.index_name ||' PARALLEL 1';
    END LOOP;
    IF v_Constraint_Name IS NOT NULL THEN
      EXECUTE IMMEDIATE 'ALTER TABLE '||v_table_name||' ENABLE PRIMARY KEY';
    END IF;
  END SP_SUBPARTITION_IDXS_ENABLE;
  PROCEDURE SP_NON_PARTITION_IDXS_DISABLE
  IS
  BEGIN
    IF v_Constraint_Name IS NOT NULL THEN
      EXECUTE IMMEDIATE 'ALTER TABLE '||v_table_name||' DISABLE PRIMARY KEY';
    END IF;
    --1: All Non Partitioned index on Partitioned/Non Partitioned  table
    FOR n_Idx IN
    (SELECT INDEX_NAME
    FROM USER_INDEXES
    WHERE STATUS    = 'VALID'
    AND TABLE_NAME  = v_table_name
    AND TABLE_OWNER = v_Owner_Name
    AND PARTITIONED = 'NO'
    )
    LOOP
      EXECUTE IMMEDIATE 'ALTER INDEX '||n_Idx.INDEX_NAME||' UNUSABLE';
    END LOOP;
  END SP_NON_PARTITION_IDXS_DISABLE;
  PROCEDURE SP_PARTITION_IDXS_DISABLE
  IS
  BEGIN
    FOR rec_idx IN
    ( SELECT DISTINCT UIP.PARTITION_NAME ,
      UI.INDEX_NAME
    FROM USER_IND_PARTITIONS UIP ,
      USER_INDEXES UI,
      USER_PART_INDEXES UPI
    WHERE UI.INDEX_NAME = UIP.INDEX_NAME
    AND UI.INDEX_NAME   = UPI.INDEX_NAME
    AND UI.TABLE_NAME   = UPI.TABLE_NAME
    AND UI.TABLE_NAME   = v_table_name
    AND UI.TABLE_OWNER  = v_Owner_Name
    AND UIP.STATUS      = 'USABLE'
    AND PARTITIONED     = 'YES'
    AND UIP.COMPOSITE   ='NO'
    AND 1               =
      CASE
        WHEN UPI.PARTITIONING_TYPE = 'LIST'
        AND UIP.PARTITION_NAME MEMBER OF list_partition_name
        THEN 1
        WHEN UPI.PARTITIONING_TYPE <> 'LIST'
        THEN 1
        ELSE 0
      END
    )
    LOOP
      --Disabling the Indexes at Partitions level.
      EXECUTE IMMEDIATE ' ALTER INDEX '||rec_idx.INDEX_NAME||' MODIFY PARTITION '||rec_idx.PARTITION_NAME|| ' UNUSABLE ';
    END LOOP;
  END SP_PARTITION_IDXS_DISABLE;
  PROCEDURE SP_SUBPARTITION_IDXS_DISABLE
  IS
  BEGIN
    FOR REC_INDX IN
    ( SELECT DISTINCT UIS.SUBPARTITION_NAME ,
      UI.INDEX_NAME
    FROM USER_INDEXES UI ,
      USER_IND_PARTITIONS UIP ,
      USER_IND_SUBPARTITIONS UIS,
      USER_PART_INDEXES UPI
    WHERE UI.INDEX_NAME    = UIP.INDEX_NAME
    AND UI.INDEX_NAME      = UIS.INDEX_NAME
    AND UI.INDEX_NAME      = UPI.INDEX_NAME
    AND UI.TABLE_NAME      = UPI.TABLE_NAME
    AND UIP.PARTITION_NAME = UIS.PARTITION_NAME
    AND UI.TABLE_NAME      = v_table_name
    AND UI.TABLE_OWNER     = v_Owner_Name
    AND PARTITIONED        = 'YES'
    AND UIP.COMPOSITE      ='YES'
    AND UIS.STATUS         ='USABLE'
    AND 1                  =
      CASE
        WHEN UPI.PARTITIONING_TYPE = 'LIST'
        AND UIP.PARTITION_NAME MEMBER OF list_partition_name
        THEN 1
        WHEN UPI.PARTITIONING_TYPE <> 'LIST'
        THEN 1
        ELSE 0
      END
    )
    LOOP
      --Rebuilding theIndexes at SUBPartitions.
      EXECUTE IMMEDIATE ' ALTER INDEX '||REC_INDX.INDEX_NAME||' MODIFY SUBPARTITION '||REC_INDX.SUBPARTITION_NAME || ' UNUSABLE ';
    END LOOP;
  END SP_SUBPARTITION_IDXS_DISABLE;
BEGIN
  vStatus := C_START_STATUS;
  SP_LOAD_PARTITIONS;
  -- Gets the Constraint_Name for table passed as v_table_name.
  BEGIN
    SELECT CONSTRAINT_NAME
    INTO v_Constraint_Name
    FROM USER_CONSTRAINTS
    WHERE TABLE_NAME   =v_table_name
    AND CONSTRAINT_TYPE='P'
    AND OWNER          = v_Owner_Name;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    v_Constraint_Name := NULL;
  END;
  -----------------------------------------------------------------------------------------
  -----------------------------------------------------------------------------------------
  /* If vMode = 'D' i.e, DISABLE, then
  (1) Fetches the all Indexes of the Table and Disables the same
  (2) If the Table has Primary Key Constraint then Disables it.
  */
  IF vMode = 'D' THEN
    SP_NON_PARTITION_IDXS_DISABLE; --To Disable the non partitioned indexes for the given table
    SP_PARTITION_IDXS_DISABLE;     --To Disable the partitioned indexes for the given table
    SP_SUBPARTITION_IDXS_DISABLE;  --To Disable the sub partitioned indexes for the given table
  Elsif vMode = 'E' THEN
    SP_NON_PARTITION_IDXS_ENABLE;--To Enable the non partitioned indexes for the given table
    SP_PARTITION_IDXS_ENABLE;    --To Enable the  partitioned indexes for the given table
    SP_SUBPARTITION_IDXS_ENABLE; --To Enable the sub partitioned indexes for the given table

  END IF;

  SP_DELETE_PARTITIONS;
  
EXCEPTION
WHEN OTHERS THEN
  vErrStr:= SUBSTR('Error_Stack...' || DBMS_UTILITY.FORMAT_ERROR_STACK() || Chr(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(),1,3500);
   vStatus := C_START_STATUS;
END PROC_INDEX_ENABLE_DISABLE;

SELECT PARTITION_NAME bulk collect
      INTO list_partition_name
      FROM XMLTABLE('/ROWSET/ROW' PASSING DBMS_XMLGEN.GETXMLTYPE ('SELECT TABLE_NAME, HIGH_VALUE ,PARTITION_NAME FROM USER_TAB_PARTITIONS U') COLUMNS TABLE_NAME VARCHAR2(100), HIGH_VALUE VARCHAR2(200), PARTITION_NAME VARCHAR2(200)) XT ,
        GROUP_BRANCHES MBI
      WHERE TRIM(MBI.BRANCH_NAME) = REPLACE( TRIM(XT.HIGH_VALUE),'''',NULL)
      AND trim(MBI.BRN_GROUP_ID)     = trim(v_brn_group_id)
      AND XT.TABLE_NAME             = v_table_name;
    END IF 

vStatus := C_FINAL_STATUS;

PROCEDURE SP_SUBPARTITION_IDXS_ENABLE
  IS
  BEGIN
    FOR rec_indx IN
    ( SELECT DISTINCT UIS.SUBPARTITION_NAME ,
      UI.INDEX_NAME BULK COLLECT
    INTO tPartitioned ,
      tIndex_Name
    FROM USER_INDEXES UI ,
      USER_IND_PARTITIONS UIP ,
      USER_IND_SUBPARTITIONS UIS,
      USER_PART_INDEXES UPI
    WHERE UI.INDEX_NAME    = UIP.INDEX_NAME
    AND UI.INDEX_NAME      = UIS.INDEX_NAME
    AND UI.INDEX_NAME      = UPI.INDEX_NAME
    AND UI.TABLE_NAME      = UPI.TABLE_NAME
    AND UIP.PARTITION_NAME = UIS.PARTITION_NAME
    AND UI.TABLE_NAME      = v_table_name
    AND UI.TABLE_OWNER     = v_Owner_Name
    AND PARTITIONED        = 'YES'
    AND UIP.COMPOSITE      ='YES'
    AND UIS.STATUS        IN ('UNUSABLE','N/A')
    AND 1                  =
      CASE
        WHEN UPI.PARTITIONING_TYPE = 'LIST'
        AND UIP.PARTITION_NAME MEMBER OF list_partition_name
        THEN 1
        WHEN UPI.PARTITIONING_TYPE <> 'LIST'
        THEN 1
        ELSE 0
      END
    )
    LOOP
      --Rebuilding the Partitions.
      EXECUTE IMMEDIATE ' ALTER INDEX '||rec_indx.index_name||' REBUILD SUBPARTITION '||rec_indx.SUBPARTITION_NAME|| '  PARALLEL 16';
      --Resetting the Parallel Degree back to 1
      EXECUTE IMMEDIATE ' ALTER INDEX '||rec_indx.index_name ||' PARALLEL 1';
    END LOOP;
    IF v_Constraint_Name IS NOT NULL THEN
      EXECUTE IMMEDIATE 'ALTER TABLE '||v_table_name||' ENABLE PRIMARY KEY';
    END IF;
  END SP_SUBPARTITION_IDXS_ENABLE;