ORA-06530:“引用未初始化组合”，即使初始化。

I have read solutions for solving the error but I don't know why still I get error,

我已经读过解这个错误的解但我不知道为什么仍然会出错，

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

and the anonymous block to use them:

以及使用它们的匿名块:

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;

I use Oracle Database 12c and below is the error in SQL Developer 4.2:

我使用Oracle数据库12c，下面是SQL Developer 4.2中的错误:

Error report -

错误报告,

ORA-06530: Reference to uninitialized composite

ORA-06530:引用未初始化的组合。

ORA-06512: at line 14

ora - 06512:14行

00000 - "Reference to uninitialized composite"

00000 -“引用未初始化组合”

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

*原因:一个对象、LOB或其他复合材料在未初始化的情况下被引用为左手边。

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

*操作:用适当的构造函数或全对象赋值来初始化组合。

1 个解决方案

#1

The syntax that you are using works for RECORDs

您正在使用的语法为记录工作。

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

To use OBJECT, you have to use object constructor to insert into the table of that 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

UPDATE: To open a cursor on the collection

更新:在集合上打开一个游标。

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.

您必须使用ref游标从集合中收集值，使用表函数和CAST函数来帮助oracle识别集合的数据类型。

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

#1