Get Distinct Value from an Element in a PL/SQL Associative Array

Question

Say I have the table XX_TABLE_SAMPLE with the records below:

TAB_ID  BATCH_NAME  EMP_NO  EMP_NAME    STATUS      SALARY  CATEGORY
------  ----------  ------  --------    --------    ------- ------------
1       BATCH_A     1       Jared       Active      1000    (NULL)
2       BATCH_A     2       Siege       Active      3000    (NULL)
3       BATCH_A     45      James       Suspended   2000    (NULL)
4       BATCH_B     67      Harry       Active      100     (NULL)
5       BATCH_B     99      Pikachu     Active      10000   (NULL)
6       BATCH_x     100     Eren        Suspended   4000    (NULL)

and i have the PL/SQL block like below (please note the comments):

declare

    cursor emp_cur is
    select  *
    from    XX_TABLE_SAMPLE
    where   status = 'Active';

    type emp_cur_type is table of XX_TABLE_SAMPLE%rowtype index by pls_integer;
    emp_rec emp_cur_type;

begin

    open    emp_cur;
    fetch   emp_cur
    bulk collect
    into    emp_rec;
    close   emp_cur;

    /*  do some pre-processing here via another stored procedure
        but the problem is, it has a parameter of p_batch_name, not a type of associative array

        for i in emp_rec.first..emp_rec.last loop
            pay_pkg.validate_pay (p_batch_name => emp_rec(i).p_batch_name);
        end;    
        -- the problem with this is that it will loop 4 times (twice each for BATCH_A and BATCH_B)
           when it should only get the 2 batch names (BATCH_A and BATCH_B)        
    */

    -- then check the salary of the emp and update the associative array    
    for i in emp_rec.first..emp_rec.last loop

        if emp_rec(i).salary > 200 and emp_rec(i).salary < 3000 then

            emp_rec(i).CATEGORY = 'Manager';

        end if;

    end loop;    

    forall i in emp_rec.first..emp_rec.last 
    update  XX_TABLE_SAMPLE
    set     CATEGORY = emp_rec(i).CATEGORY 
    where   TAB_ID   = emp_rec(i).TAB_ID;

end;

With that, I would like to get the distinct values of the Element Batch_Name in an Associative Array and then pass it to the Stored Procedure pay_pkg.validate_pay.

Any thoughts on how i can achive this without declaring another Explicit Cursor?

Could you please show us what is result you need in the array? — Juan Carlos Oropeza, Jul 12 '17 at 20:54
@JuanCarlosOropeza, the associative array would be populated with records with "Active" Status (4 rows). however, in the part that i would call `pay_pkg.validate_pay`, i only need to get 2 rows ("BATCH_A" and "BATCH_B") from 1 column (`Batch_Name`) — Migs Isip, Jul 12 '17 at 20:58
Migs the problem is I don't have time to try decipher what your code does even less if you don't provide the result needed. My suggestion is simplify the problem and focus what you cant do. Is make a query? , is create an array? because I don't event think you need a cursor for that. — Juan Carlos Oropeza, Jul 12 '17 at 21:01

score 1 · Answer 1 · answered Jul 12 '17 at 21:13

1

Maybe is something else you aren't saying, but if you need {"BATCH_A", "BATCH_B"}

Why dont just use:

SELECT DISTINCT BATCH_NAME  
FROM XX_TABLE_SAMPLE 
WHERE status = 'Active'
  AND salary > 200 
  AND salary < 3000

answered Jul 12 '17 at 21:13

Juan Carlos Oropeza

47,252
12
78
118

That's the scenario i'm asking about. Is it possible to only the distinct values of a subset of an Array, without having to explicitly declare them? – Migs Isip Jul 12 '17 at 21:17
2

I don't see how. You have a set, and now need another set. You can loop the first set and remove duplicates, but for that just create a set from the beginning. What is the problem on create two sets? – Juan Carlos Oropeza Jul 12 '17 at 21:20

score 1 · Accepted Answer · answered Jul 13 '17 at 08:13

To me it seems that you are thinking in unnecessary complex solution. I think your example can be simplified to the following solution that requires zero PL/SQL data structures (r is an implicit record type but the compiler makes it for you!).

Btw, there is no need to be afraid of cursors in PL/SQL.

declare
  -- a dummy placeholder for the "tricky" subprogram
  procedure validate_pay(p_batch_name in varchar2) is begin null; end;
begin
  for r in (select distinct batch_name
              from xx_sample_data
             where status = 'Active')
  loop
    validate_pay(p_batch_name => r.batch_name);
  end loop;

  update xx_sample_data set
   category = 'Manager'
  where status = 'Active'
    and salary between 201 and 2999
  ;
end;
/

Just a tip, you are assuming salary is an integer to simplify the `> <` condition, but I doubt that is the case. — Juan Carlos Oropeza, Jul 13 '17 at 14:40
@JuanCarlosOropeza Yes you're correct. I leave it as an exercise for the OP. — user272735, Jul 14 '17 at 03:56

score 0 · Answer 3 · answered Jan 18 '18 at 22:38

0

if you are on oracle 12 there is another way.

but it involves selecting from your associative array

see Select from PLSQL Associative array?

answered Jan 18 '18 at 22:38

ShoeLace

3,476
2
30
44

Get Distinct Value from an Element in a PL/SQL Associative Array

3 Answers3