oracle 对未初始化集合 PL/SQL 的引用

Reference to uninitialized collection PL/SQL

提问by rohit_agarwal

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

我ORA-06531: Reference to uninitialized collection在运行存储过程时收到以下详细信息：

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;

回答by Bob Jarvis - Reinstate Monica

In your TEST procedure you have vdeclared 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_tthis won't help, as the u_tcollection isn't passed in to the procedure - it only receives what the procedure passes back out.

在您的 TEST 过程中，您已v声明为 OUT 参数 - 这意味着该过程需要初始化该过程中的输出集合（例如v := T();）。即使您更改调用块以初始化u_t这也无济于事，因为u_t集合不会传递给过程 - 它只接收过程传回的内容。

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.

请注意，PL/SQL 中的字符串常量必须用单引号括起来，而不是用双引号括起来。

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.

分享和享受。

回答by neshkeev

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

我今天有一个类似的问题，你必须初始化你的 u_t 变量，检查这个答案以获得更多