PLSQL - IN clause using variable with multiple values

Question

I am looping a statement and extracting the IDs and putting it into a variable:

declare
  l_id  NUMBER;

BEGIN

     FOR i IN 1..l_row_count
        loop
            l_id := to_number(apex_json.get_varchar2(p_path => 'rows[%d].id', 
                                                        p0 => i, p_values => l_values
                                                        )
                                );
     end loop:

I am trying to get all the ids and then run a select statement where this list of IDs will be used inside of a where statement. So the full code would look like this:

    declare
      l_id  NUMBER;
      l_id_list [SOME_TYPE_NOT_SURE];
    
    BEGIN
    
         FOR i IN 1..l_row_count
            loop
                l_id := to_number(apex_json.get_varchar2(p_path => 'rows[%d].id', 
                                                            p0 => i, p_values => l_values
                                                            )
                                    );
                //somehow add l_id to the l_id_list
 
         end loop:

        SELECT * FROM mytable WHERE someid IN (l_id_list);

END;

I haven't been able to get it to work. I am not an expert in PLSQL, but I tried using arrays or trying to concat the l_id_list as a string, but I am unable to get it work. Basically all I am trying to do is create a list of all the IDs, then run a select statement to see if these IDs exist in this other table.

You need to learn about Oracle collections(VARRAYS and Nested tables in your case for SQL->PL/SQL): docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/… — Sayan Malakshinov
– Sayan Malakshinov, Commented Apr 3, 2022 at 21:34

MT0 · Accepted Answer · 2022-04-04 08:53:40Z

Declare the type globally:

CREATE TYPE number_list IS TABLE OF NUMBER;

Then you can initialise the list and then, in each iteration of the loop, EXTEND the list and assign the value and, finally, use the MEMBER OF operator or IN with a subquery and table collection expression:

DECLARE
  l_id_list NUMBER_LIST := NUMBER_LIST();
BEGIN
  FOR i IN 1..l_row_count
  LOOP
    l_id_list.EXTEND;
    l_id_list(l_id_list.COUNT) := to_number(
                                    apex_json.get_varchar2(
                                      p_path   => 'rows[%d].id', 
                                      p0       => i,
                                      p_values => l_values
                                    )
                                  );
  END LOOP;

  SELECT *
  -- BULK COLLECT INTO ...
  FROM   mytable
  WHERE  someid MEMBER OF l_id_list;

  -- or

  SELECT *
  -- BULK COLLECT INTO ...
  FROM   mytable
  WHERE  someid IN (SELECT COLUMN_VALUE FROM TABLE(l_id_list));
END;

Humoyun · Accepted Answer · 2022-04-04 05:53:45Z

In your case l_id_list's type must be declared globally and as collection-type. Locally defined types cannot be used as collections, so cannot be used inside in-clause.

 declare
      l_id  NUMBER;
      l_id_list [SOME_GLOBAL_COLLECTION_TYPE];
    
    BEGIN
    
         FOR i IN 1..l_row_count
            loop
                l_id := to_number(apex_json.get_varchar2(p_path => 'rows[%d].id', 
                                                            p0 => i, p_values => l_values
                                                            )
                                    );
                //somehow add l_id to the l_id_list
 
         end loop:

        SELECT * FROM mytable WHERE someid IN (select * from table(l_id_list));
END;

Collectives™ on Stack Overflow

PLSQL - IN clause using variable with multiple values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related