Reference to uninitialized collection PL/SQL

Question

I receive ORA-06531: Reference to uninitialized collection when I run a stored procedure with the following details:

User-defined datatype:

CREATE OR REPLACE TYPE T IS TABLE OF VARCHAR2;

Stored procedure definition:

CREATE OR REPLACE PROCEDURE TEST ( u IN T, v OUT T)
IS
BEGIN
  FOR i IN u.FIRST..u.LAST LOOP
    v(i) := u(i);
  END LOOP;
END;

I use the following to invoke the procedure:

DECLARE
  v_t T;
  u_t T;
BEGIN
  v_t := T();
  v_t.EXTEND(2);

  v_t(1) := "This is test1";
  v_t(2) := "This is test2";
  TEST(v_t, u_t);
END;

Answer 1

In your TEST procedure you have v declared as an OUT parameter - this means that the procedure needs to initialize the output collection in the procedure (e.g. v := T();). Even if you change the calling block to initialize u_t this won't help, as the u_t collection isn't passed in to the procedure - it only receives what the procedure passes back out.

Change your code as follows:

CREATE OR REPLACE PROCEDURE TEST ( u IN T, v OUT T) IS
  i NUMBER := u.FIRST;
BEGIN
  v := T();
  v.EXTEND(u.COUNT);

  IF i IS NOT NULL THEN
    LOOP
      v(i) := u(i);
      i := u.NEXT(i);
      EXIT WHEN i IS NULL;
    END LOOP;
  END IF;
END TEST;

DECLARE
  v_t T;
  u_t T;
BEGIN
  v_t := T();
  v_t.EXTEND(2);

  v_t(1) := 'This is test1';
  v_t(2) := 'This is test2';

  TEST(v_t, u_t);

  FOR i IN u_t.FIRST..u_t.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(u_t(i));
  END LOOP;
END;

Please note that string constants in PL/SQL must be enclosed in single-quotes, not double-quotes.

Also - using similar variable names which have opposite meanings in the procedure and the calling block just adds to the confusion. Get in the habit of using meaningful names and you'll save yourself a lot of confusion later.

Share and enjoy.