PL/SQL Function Incorrectly Invoked Twice for Each Row Processed in Result Set (Doc ID 2758342.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

• Following an upgrade to Oracle Database 19c, a PL/SQL function, intended to be invoked once for each row of the result set, is now invoked twice.  For example, assume that PL/SQL package, MY_PKG, contains a function called "TALLY_ROW_COUNT," whose job it is to increment a counter once, for each row processed within the result set:
  
  g_row_count pls_integer := 0;

  FUNCTION tally_row_count return pls_integer IS
  BEGIN

      g_row_count := g_row_count + 1;

  END tally_row_count;

  SELECT my_pkg.tally_row_count
  FROM T1;

   

If table T1 contains ten rows, then the expected value of variable G_ROW_COUNT, after completion of the query, is also ten.  Similarly, given a table with 100 rows, the counter is also expected to be equal to 100.

However, since the database upgrade, the counter value is unexpectedly equal to twice the number of rows stored within T1.  Therefore, in the previous example of table T1, containing 100 rows, an execution of the query above causes G_ROW_COUNT to equal 200.

Changes

Upgraded the database software to Oracle Database 19c or higher.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.