SELECT *DISTINCT* with Oracle CURSOR and BULK COLLECT TO

UltraCommit

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!

Maheswaran Ravisankar

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;

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Cursor/bulk collect

Getting rows that are having distinct id with bulk collect - Oracle

Use Bulk Collect result in a select query without cursor

oracle add select statement in oracle bulk forall collect loop

Difference between bulk collect and cursor

How to fetch the Rowid with all the columns of a table using bulk collect for a cursor in oracle

Understanding 'BULK COLLECT' in Oracle Function

"Bulk Collect Into" and "Execute Immediate" in Oracle

oracle bulk collect and reading the data

Oracle Bulk Collect into Collection using LOOP

Oracle bulk collect error PLS-00201

how to use bulk collect in db2 cursor

How to populate nested table (with nested objects) by using cursor and bulk collect

Oracle/PLSQL Processing all data in a for LOOP after BULK COLLECT

Oracle: detect exception in type construtor during bulk collect

Please help on "BULK COLLECT" in Oracle, it runs into an infinite loop

Oracle select distinct merge duplicates

Oracle Select to show distinct results

how to select distinct records in oracle?

Looking for a datatype which can be filled with BULK COLLECT INTO and then to be selected from with SELECT

Oracle PL/SQL collect values from a loop into a cursor

How to append the records in table type object using bulk collect inside the cursor loop

Bulk Collect with Sum function

Strange behaviour of BULK COLLECT

BULK COLLECT INTO multiple collections

Bulk collect into multiple columns

Oracle select distinct with join and multiple columns

SQL Oracle, select distinct unordered couples

How to generate sequential number for distinct select in Oracle