oracle - ORA-06530 : "Reference to uninitialized composite" even if initialized

Question

我已经阅读了解决错误的解决方案，但我不知道为什么仍然出现错误，

CREATE OR REPLACE TYPE tmp_object IS OBJECT (
            id      NUMBER,
            code    NUMBER
            );
CREATE OR REPLACE TYPE tmp_table IS TABLE OF tmp_object;

以及使用它们的匿名块:

DECLARE
    cnt         PLS_INTEGER;
    tmp_tbl     tmp_table := tmp_table();
BEGIN
    SELECT regexp_count('34, 87, 908, 123, 645', '[^,]+', 1) str
    INTO cnt
    FROM dual;

    DBMS_OUTPUT.PUT_LINE('Counter is: ' || cnt);

    FOR i IN 1..cnt
    LOOP
        tmp_tbl.EXTEND;
        SELECT TRIM(REGEXP_SUBSTR('34, 87, 908, 123, 645', '[^,]+', 1,i)) str
        INTO tmp_tbl(tmp_tbl.LAST).code
        FROM dual;
        DBMS_OUTPUT.PUT_LINE(tmp_tbl(i).code);
    END LOOP;
END;

我使用 Oracle Database 12c 及以下是 SQL Developer 4.2 中的错误:

Error report -

ORA-06530: Reference to uninitialized composite

ORA-06512: at line 14

6530. 00000 - "Reference to uninitialized composite"

*Cause: An object, LOB, or other composite was referenced as a left hand side without having been initialized.

*Action: Initialize the composite with an appropriate constructor or whole-object assignment.

Answer 1

您使用的语法适用于 RECORD

SQL> set serverout on;
SQL> 
SQL> DECLARE
  2      cnt         PLS_INTEGER;
  3      l_code      NUMBER;
  4      TYPE tmp_object IS RECORD (
  5              id      NUMBER,
  6              code    NUMBER
  7              );
  8      TYPE tmp_table IS TABLE OF tmp_object;
  9      tmp_tbl     tmp_table := tmp_table();
 10  BEGIN
 11      SELECT regexp_count('34, 87, 908, 123, 645', '[^,]+', 1) str
 12      INTO cnt
 13      FROM dual;
 14  
 15      DBMS_OUTPUT.PUT_LINE('Counter is: ' || cnt);
 16  
 17      FOR i IN 1..cnt
 18      LOOP
 19          tmp_tbl.EXTEND;
 20  
 21          SELECT TRIM(REGEXP_SUBSTR('34, 87, 908, 123, 645', '[^,]+', 1,i)) str
 22          INTO tmp_tbl(tmp_tbl.LAST).code
 23          FROM dual;
 24          DBMS_OUTPUT.PUT_LINE(tmp_tbl(i).code);
 25  
 26      END LOOP;
 27  END;
 28  /

Counter is: 5
34
87
908
123
645

PL/SQL procedure successfully completed

要使用 OBJECT，您必须使用对象构造函数插入到该对象的表中

SQL> 
SQL> CREATE OR REPLACE TYPE tmp_object IS OBJECT (
  2              id      NUMBER,
  3              code    NUMBER
  4              );
  5  
  6  /

Type created

SQL> CREATE OR REPLACE TYPE tmp_table IS TABLE OF tmp_object;
  2  /

Type created

SQL> 
SQL> DECLARE
  2      cnt         PLS_INTEGER;
  3      tmp_tbl     tmp_table := tmp_table();
  4  BEGIN
  5      SELECT regexp_count('34, 87, 908, 123, 645', '[^,]+', 1) str
  6      INTO cnt
  7      FROM dual;
  8  
  9      DBMS_OUTPUT.PUT_LINE('Counter is: ' || cnt);
 10  
 11      FOR i IN 1..cnt
 12      LOOP
 13          tmp_tbl.EXTEND;
 14  
 15          SELECT tmp_object(i, TRIM(REGEXP_SUBSTR('34, 87, 908, 123, 645', '[^,]+', 1,i)))
 16          INTO tmp_tbl(tmp_tbl.last)
 17          FROM dual;
 18  
 19          DBMS_OUTPUT.PUT_LINE(tmp_tbl(i).code);
 20      END LOOP;
 21  END;
 22  /

Counter is: 5
34
87
908
123
645

PL/SQL procedure successfully completed

更新:在集合上打开游标

You have to use a ref cursor to collect the value from the collection using TABLE function and CAST function to help oracle identify the datatype of the collection.

SQL> DECLARE
  2      cnt      PLS_INTEGER;
  3      tmp_tbl  tmp_table := tmp_table();
  4      c_cursor SYS_REFCURSOR;
  5      l_id     NUMBER;
  6      l_code   NUMBER;
  7  BEGIN
  8      SELECT regexp_count('34, 87, 908, 123, 645', '[^,]+', 1) str INTO cnt FROM dual;
  9  
 10      dbms_output.put_line('Counter is: ' || cnt);
 11  
 12      FOR i IN 1 .. cnt LOOP
 13          tmp_tbl.extend;
 14  
 15          SELECT tmp_object(i, TRIM(regexp_substr('34, 87, 908, 123, 645', '[^,]+', 1, i)))
 16            INTO tmp_tbl(tmp_tbl.last)
 17            FROM dual;
 18  
 19      END LOOP;
 20  
 21      OPEN c_cursor FOR
 22          SELECT * FROM TABLE(CAST(tmp_tbl AS tmp_table));
 23      LOOP
 24          FETCH c_cursor
 25              INTO l_id,
 26                   l_code;
 27          EXIT WHEN c_cursor%NOTFOUND;
 28          dbms_output.put_line(l_id || ',' || l_code);
 29      END LOOP;
 30      CLOSE c_cursor;
 31  
 32  END;
 33  /

Counter is: 5
1,34
2,87
3,908
4,123
5,645

PL/SQL procedure successfully completed