SELECT *DISTINCT* with Oracle CURSOR and BULK COLLECT TO

Question

Please assume I am using the following code:

  TYPE tb_MY_TABLE
  IS
     TABLE OF MY_TABLE%ROWTYPE
        INDEX BY PLS_INTEGER;

  tb_c3_MY_TABLE          tb_MY_TABLE;

  CURSOR c3_MY_TABLE (
     p3_IDENTIFIER_01   IN            VARCHAR2,
     p3_IDENTIFIER_02   IN            VARCHAR2
  )
  IS
     SELECT    IDENTIFIER_01,
               IDENTIFIER_02,
               STRING_01,
               STRING_02,
               STRING_03
       FROM   MY_TABLE
      WHERE   MY_TABLE.IDENTIFIER_01 = p3_IDENTIFIER_01
              AND MY_TABLE.IDENTIFIER_02 = p3_IDENTIFIER_02;

  OPEN c3_MY_TABLE (v_IDENTIFIER_01, v_IDENTIFIER_02);

  FETCH c3_MY_TABLE BULK COLLECT INTO   tb_c3_MY_TABLE;

  CLOSE c3_MY_TABLE;

  BEGIN
    FOR v_INDX_TER IN 1 .. tb_c3_MY_TABLE.COUNT

      LOOP
        ----- .....
      END LOOP;

  END;

How should I change the code if c3_MY_TABLE is on SELECT DISTINCT IDENTIFIER_01, IDENTIFIER_02 FROM MY_TABLE?

  CURSOR c3_MY_TABLE (
     p3_IDENTIFIER_01   IN            VARCHAR2,
     p3_IDENTIFIER_02   IN            VARCHAR2
  )
  IS
     SELECT    DISTINCT IDENTIFIER_01,
                        IDENTIFIER_02
       FROM   MY_TABLE
      WHERE   MY_TABLE.IDENTIFIER_01 = p3_IDENTIFIER_01
              AND MY_TABLE.IDENTIFIER_02 = p3_IDENTIFIER_02;

Thank you in advance for your kind suggestions!

Answer 1

Please change your declaration( in DECLARE block) like this, and you are done. DISTINCT applies to entire row you SELECT disregarding the number of columns actually in the TABLE. It happens after the Selection process only.

CURSOR c3_MY_TABLE (
     p3_IDENTIFIER_01   IN            VARCHAR2,
     p3_IDENTIFIER_02   IN            VARCHAR2
  )
  IS
     SELECT    DISTINCT IDENTIFIER_01,
                        IDENTIFIER_02
       FROM   MY_TABLE
      WHERE   MY_TABLE.IDENTIFIER_01 = p3_IDENTIFIER_01
              AND MY_TABLE.IDENTIFIER_02 = p3_IDENTIFIER_02;


TYPE TY_ROW IS RECORD
(
   IDENTIFIER_01 MY_TABLE.IDENTIFIER_01%TYPE,
   IDENTIFIER_02 MY_TABLE.IDENTIFIER_02%TYPE
);

TYPE TY_TABLE is TABLE OF TY_ROW;

tb_c3_MY_TABLE TY_TABLE;