引用未初始化的集合PL/SQL [英] Reference to uninitialized collection PL/SQL

问题描述

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

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

用户定义的数据类型:

CREATE OR REPLACE TYPE T IS TABLE OF VARCHAR2;

存储过程定义:

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;

推荐答案

在TEST过程中，已将v声明为OUT参数-这意味着该过程需要初始化该过程中的输出集合(例如，).即使您将调用块更改为初始化u_t，这也无济于事，因为u_t集合未传递到过程中-它仅接收过程传回的内容.

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;

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

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.

分享并享受.

这篇关于引用未初始化的集合PL/SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！