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;

`u_t` is not uninitialized... – Erich Kitzmueller Aug 08 '14 at 10:57 — Erich Kitzmueller, Aug 08 '14 at 10:57

Bob Jarvis - Слава Україні · Accepted Answer · 2014-08-11T11:22:57.597

15

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.

edited Aug 11 '14 at 11:22

answered Aug 08 '14 at 11:32

Bob Jarvis - Слава Україні

48,992
9
77
110

If the entire purpose of this test is to simply copy `u` collection, then `v` collection can be simply assigned to `u` collection, even without `u` be initialized first. There is no need to use `for` loop to perform the element by element copying. Moreover, if the `v` collection happens to be sparse, `no_data_found` exception will be raised. – Nick Krasnov Aug 08 '14 at 12:27
This works for me. I am also trying to execute this procedure with a JDBC Program. Instead of printing it to the dbms_output, I simple asigning it to the out parameter and extracting the output from the OUT parameter. Works like a charm! Thank you so much! – rohit_agarwal Aug 11 '14 at 05:31
@NicholasKrasnov - regarding the "use assignment instead of a FOR loop", you're correct. However, in this case it appears that OP could use a demonstration of how to perform the collection management manually so I thought it would be better the "fix" to FOR loop implementation. I have rewritten the TEST procedure to handle the sparse case by using the `FIRST` and `NEXT` collection methods. Thanks! – Bob Jarvis - Слава Україні Aug 11 '14 at 11:24
@bob-jarvis-reinstate-monica I think it's worth mentioning that if type `T` is declared in the procedure internally and if type `T` is declared again in the invoking code, we'll get `PLS-00306`. I've followed this code and stumbled on this https://stackoverflow.com/a/57542700/5524175. – duplex143 Jun 11 '20 at 06:55

score 0 · Answer 2 · edited May 23 '17 at 10:32

0

I had a similar question today, you have to initialize your u_t variable, check this answer to get more

edited May 23 '17 at 10:32

Community

1
1

answered Aug 08 '14 at 11:06

neshkeev

6,280
3
26
47

Reference to uninitialized collection PL/SQL

2 Answers2

Linked