CODEWITHSUNDEEP
sqloracle-databaseplsql
NewbUser
NewbUser

Reputation: 303

How do I convert this procedure to a PLSQL block correctly ? Reference to uninitialized function

The following block compiles correctly .(Unimportant parts redacted)

CREATE OR REPLACE PROCEDURE testProc
IS
TYPE test_h IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
test_h_list test_h;
TYPE l_ids IS TABLE OF VARCHAR(100);
l_id_list l_ids;

BEGIN 
  test_h_list('A'):='Apple'; 
  test:=test_h_list.FIRST;
  WHILE test IS NOT NULL LOOP
    BEGIN
    SELECT  tbl1.l_id BULK COLLECT INTO l_id_list
    WHERE ....
    ....
    ....
  END;

However, when I attempt to convert it into a plsql block 

DECLARE
TYPE test_h IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100); 
test_h_list test_h; --ORA-06531: Reference to uninitialized collection  :-(
TYPE l_ids IS TABLE OF VARCHAR(100);
l_id_list l_ids;

BEGIN 
  test_h_list('A'):='Apple'; 
  test:=test_h_list.FIRST;
  WHILE test IS NOT NULL LOOP
    BEGIN
    SELECT  tbl1.l_id BULK COLLECT INTO l_id_list
    WHERE ....
    ....
    ....
  END;

I get the 'ORA-06531: Reference to uninitialized collection ' error as annotated above. I tried searching around and came across this and based on the examples here

I came up with this 

DECLARE
TYPE test_h IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100); 
test_h_list test_h := test_h();
TYPE l_ids IS TABLE OF VARCHAR(100);
l_id_list l_ids :=l_ids();


BEGIN 
  test_h_list.EXTEND(100);
  l_ids.EXTEND(100);
  test_h_list('A'):='Apple'; 
  test:=test_h_list.FIRST;
  WHILE test IS NOT NULL LOOP
    BEGIN
    SELECT  tbl1.l_id BULK COLLECT INTO l_id_list
    WHERE ....
    ....
    ....
  END;

But this throws an error saying PLS-00222: no function with name 'test_h' exists in this scope. Any idea on what I might be missing?

MCVE

Script -

DECLARE
TYPE test_h IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
test_h_list test_h := test_h(); --Line 3
TYPE l_ids IS TABLE OF VARCHAR(100);
l_id_list l_ids := l_ids();
test_str VARCHAR(50);

BEGIN 
  test_h_list.EXTEND(100);
  l_id_list.EXTEND(100);
  test_h_list('App'):='1'; 
  test_h_list('Red'):='2'; 
  test_str:=test_h_list.FIRST;
  WHILE test_str IS NOT NULL LOOP
    BEGIN
    SELECT  TABLE1.DEPT  BULK COLLECT INTO l_id_list
    FROM    TABLE1
    WHERE   TABLE1.NAME = test_str;

    FOR indx IN 1..l_id_list.COUNT
      LOOP

                         DBMS_OUTPUT.PUT_LINE( l_id_list(indx));


      END LOOP;
    test_str:=test_h_list.NEXT(test_str);
    EXCEPTION
    WHEN OTHERS THEN -- Just print  the failure to logs
           NULL;

    END;
  END LOOP;
END; 
/

Error Report -

Error starting at line 1 in command:
DECLARE
TYPE test_h IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
test_h_list test_h := test_h();
TYPE l_ids IS TABLE OF VARCHAR(100);
l_id_list l_ids := l_ids();
test_str VARCHAR(50);

BEGIN 
  test_h_list.EXTEND(100);
  l_id_list.EXTEND(100);
  test_h_list('App'):='1'; 
  test_h_list('Red'):='2'; 
  test_str:=test_h_list.FIRST;
  WHILE test_str IS NOT NULL LOOP
    BEGIN
    SELECT  TABLE1.DEPT  BULK COLLECT INTO l_id_list
    FROM    TABLE1
    WHERE   TABLE1.NAME = test_str;

    FOR indx IN 1..l_id_list.COUNT
      LOOP

                         DBMS_OUTPUT.PUT_LINE( l_id_list(indx));


      END LOOP;
    test_str:=test_h_list.NEXT(test_str);
    EXCEPTION
    WHEN OTHERS THEN 
           NULL;

    END;
  END LOOP;
END; 
Error report:
ORA-06550: line 3, column 23:
PLS-00222: no function with name 'TEST_H' exists in this scope
ORA-06550: line 3, column 13:
PL/SQL: Item ignored
ORA-06550: line 9, column 3:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored
ORA-06550: line 11, column 3:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 11, column 3:
PL/SQL: Statement ignored
ORA-06550: line 12, column 3:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 12, column 3:
PL/SQL: Statement ignored
ORA-06550: line 13, column 13:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 13, column 3:
PL/SQL: Statement ignored
ORA-06550: line 27, column 15:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 27, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
Elapsed: 00:00:00.011

Upvotes: 2

Views: 382

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

In your MCVE you're mixing up different types of PL/SQL table. Your test_h types is indexed so doesn't need to be initialized and can't be extended - since it's a sparse table type. So removing the := test_h() and the extend line makes this work:

DECLARE
TYPE test_h IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
test_h_list test_h; -- do no instantiate := test_h(); --Line 3
TYPE l_ids IS TABLE OF VARCHAR(100);
l_id_list l_ids := l_ids();
test_str VARCHAR(50);

BEGIN 
  -- test_h_list.EXTEND(100); -- do not extend either
  l_id_list.EXTEND(100);
  test_h_list('App'):='1'; 
  test_h_list('Red'):='2'; 
  test_str:=test_h_list.FIRST;
  WHILE test_str IS NOT NULL LOOP
    BEGIN
    SELECT  TABLE1.DEPT  BULK COLLECT INTO l_id_list
    FROM    TABLE1
    WHERE   TABLE1.NAME = test_str;

    FOR indx IN 1..l_id_list.COUNT
      LOOP

                         DBMS_OUTPUT.PUT_LINE( l_id_list(indx));


      END LOOP;
    test_str:=test_h_list.NEXT(test_str);
    EXCEPTION
    WHEN OTHERS THEN -- Just print  the failure to logs
           NULL;

    END;
  END LOOP;
END; 
/

PL/SQL procedure successfully completed.

Your original first anonymous block wasn't doing either of those things, and had the same sparse table type for test_h, so should not have been getting the ORA-06531. You would have seen it if you'd removed the INDEX BY VARCHAR2(100) from the type definition, but that isn't what you showed.

You could also have got it from trying to refer to elements of l_id_list without initialising that - but as presented in the question, it would always have been initalized implicitly by the bulk collect in the loop, even if the real table being queried was empty - you'd just have an empty PL/SQL table.

The code you originally showed doesn't throw the error; and the MCVE is doing something different, closer to your second anonymous block than the first one.

Upvotes: 1

Related Questions