Limitation in Oracle to call a local procedure?

Question

I have executed below block in SQL*PLUS and it successfully executed the whole block. Strangely, it inserted only 32768 unique values in the table and remaining 7232 were duplicates.

Please note we have to manually call the local procedure 40k times without using a loop.

/*
-- @Hello
-- please create this test table
-- on your database schema

CREATE TABLE my_table (
   num       NUMBER,
   upd_dt   DATE DEFAULT SYSDATE
)
/
*/

DECLARE
   TYPE my_tp IS TABLE OF my_table%ROWTYPE;
   my_nest_t my_tp := my_tp ();

   PROCEDURE add (
      p_num   IN      NUMBER
   )
   AS
   BEGIN
      my_nest_t.EXTEND;
      my_nest_t (my_nest_t.LAST) := NULL;
      my_nest_t (my_nest_t.LAST).num := p_num;
   END add;
BEGIN
   add (1);
   add (2);
   add (3);
   add (4);
   /*
   -- @Hello
   -- this way manually write the call to
   -- the local proc 40000 times
   -- I used CONNECT BY LEVEL to generate the script
   -- SELECT ' add (p_num=>' || level || ');' FROM DUAL CONNECT BY LEVEL < 40001
   -- use this query only to generate the 40k rows of code
   */
   add (39999);
   add (40000);
   
   IF my_nest_t.COUNT < 40000 THEN
      RAISE_APPLICATION_ERROR (-20001, '@Hello - please call PROCEDURE add 40000 times as mentioned above');
   END IF;

   FORALL i IN 1.. my_nest_t.COUNT
      INSERT INTO my_table (num)
      VALUES (my_nest_t (i).num);
   COMMIT;
END;
/

/*
-- @Hello
*/

-- 40000, 32768
SELECT COUNT (1),
               COUNT (DISTINCT num)
FROM    my_table

user_6033601 · Answer

Works fine for me on version 12.1.

I believe there are duplicates among calls to add procedure.

Here, how I generated those for tests

select 'add(' || level || ');' from dual connect by level <= 40000

then I copied them to the plsql block and launched. After that the check-query from you

SELECT COUNT (1), COUNT (DISTINCT num)
  FROM    ek_test;

displays 40000 for both coleumns.

So my answer is, please check all those 40 thousands of procedure calls and look for duplicates.

Limitation in Oracle to call a local procedure?

1 Answers