Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How to select a random value from an associative array in PL/SQL?

I have an associative array full of data
how to select a random data from there?

declare
TYPE r_emp_id IS TABLE OF NUMBER;
    emps r_emp_id;
begin
select employes_id into emps from employes order by employes_id;

for i in <(random number of employes_id from emps)> loop

DBMS_OUTPUT.put_line(employes_id (i)); 
end loop;
end;

>Solution :

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

You want to BULK COLLECT INTO a collection and then you don’t want a loop if you just want a single random value:

DECLARE
  TYPE r_emp_id IS TABLE OF NUMBER;
  emps r_emp_id;
  i    PLS_INTEGER;
BEGIN
  SELECT employes_id
  BULK COLLECT INTO emps
  FROM   employes
  ORDER BY employes_id;

  i := FLOOR(DBMS_RANDOM.VALUE(1, emps.COUNT + 1));

  DBMS_OUTPUT.put_line(emps(i)); 
END;
/

If you want all the values in a random order then:

DECLARE
  TYPE r_emp_id IS TABLE OF NUMBER;
  emps r_emp_id;
BEGIN
  SELECT employes_id
  BULK COLLECT INTO emps
  FROM   employes
  ORDER BY DBMS_RANDOM.VALUE;

  FOR i IN 1 .. emps.COUNT LOOP
    DBMS_OUTPUT.put_line(emps(i)); 
  END LOOP;
END;
/

However, for a single value, you could skip the collection entirely:

DECLARE
  emp_id EMPLOYES.EMPLOYES_ID%TYPE;
BEGIN
  SELECT employes_id
  INTO   emp_id
  FROM   employes
  ORDER BY DBMS_RANDOM.VALUE
  FETCH FIRST ROW ONLY;

  DBMS_OUTPUT.put_line(emp_id); 
END;
/

Or, in SQL (and not PL/SQL):

SELECT employes_id
FROM   employes
ORDER BY DBMS_RANDOM.VALUE
FETCH FIRST ROW ONLY;

db<>fiddle here

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading