FORALL loop - select statement from associative array

Question

I have a function, which accepts associative array as parameters. Function - before insert/update - needs to check if records exists in table, so I need to loop over array.

Since FORALL doesn't allow SELECT statement, what is the right way to do this ?

Function (just the part I need to fix - It's an example!!):

 type t_name is table of MySchema.Orders.NAME%type index by pls_integer;
 type t_year is table of MySchema.Orders.YEAR%type index by pls_integer;
 type t_month is table of MySchema.Orders.MONTH%type index by pls_integer;

 FUNCTION Check_records (name_in IN  t_name, year_in IN  t_year, month_in IN  t_month) RETURN INTEGER
 IS
  
 record_exists INTEGER;
 
 BEGIN
    
      FORALL i in name_in.FIRST..name_in.LAST
      SELECT COUNT(*) INTO record_exists 
      FROM MySchema.Orders@link_to_table
      WHERE name= name_in(i)
      AND year= year_in(i)
      AND month= month_in(i);
     
      return record_exist
         
 END Check_records;

For anyone wondering WHY - It will be a function for checking if records exist, before Insert. If so, function will be called again to BULK delete records. Because I'm doing BULK Insert (including BLOB's) via OracleBulkCopy class - which directly inserts records without any PL\SQL... In other words, to avoid problems with BulkCopyClass... — Lucy82
What is it that you want your function to return? The total number of matches (i.e. sum the count(*) for all values of i)? Or something else? If you're just going to delete all the data that matches, why bother doing the check first? Just issue the delete (and return the number of rows deleted if you care whether there was data there or not). No reason to incur the expense of hitting the table twice. — Justin Cave
@JustinCave, user needs to be informed that records are allready there, before he performs a new insert, just an app design. And If I'm allready doing bulk inserts which increases speed and decreases network traffic, I created this function. BLOB's are problem, slow performance when doing regular Insert. — Lucy82
OK. So you aren't necessarily going to bulk delete the records that are there? What is it that you want your function to return? The total number of matches (i.e. sum the count(*) for all values of i)? Or something else? — Justin Cave
@JustinCave, yes I want to retun total number of matches, of all values(i). — Lucy82

Justin Cave Justin Cave · Accepted Answer · 2020-12-21T13:20:52

The simplest approach would simply be to use a for loop

DECLARE
  l_total_records pls_integer;
  l_count         pls_integer;
BEGIN
  l_total_records := 0;

  FOR i in name_in.FIRST..name_in.LAST
  LOOP
    SELECT COUNT(*) 
      INTO l_count 
      FROM MySchema.Orders@link_to_table
     WHERE name= name_in(i)
       AND year= year_in(i)
       AND month= month_in(i);

    l_total_count := l_total_count + l_count;
  END LOOP;
END;

If you were querying a local table and you could use a single collection (i.e. a collection of an object type with name, year, and month fields), you could do something more elegant with a MEMBER OF function but I'm not sure that works over a database link and don't have one available to test at the moment.

FORALL loop - select statement from associative array

1 Answers