多集并集的不同给出了“错误数量的类型或传递的参数".错误

问题描述

我正在使用for循环将不同的值传递给游标，使用MULTISET UNION运算符批量收集数据并将其附加到同一嵌套表中.但是，为避免重复数据，我尝试使用MULTISET UNION DISTINCT并抛出错误PLS-00306: wrong number or types of arguments in call to 'MULTISET_UNION_DISTINCT'.这些代码在没有DISTINCT的情况下效果很好.请让我知道我是否在这里丢失任何东西.我正在使用 Oracle数据库11g企业版11.2.0.3.0-64位生产版本

I am using a for loop to pass different values to a cursor, bulk collect the data and append it to the same nested table using MULTISET UNION operator. However, to avoid duplicate data, i tried to use MULTISET UNION DISTINCT and it throws the error PLS-00306: wrong number or types of arguments in call to 'MULTISET_UNION_DISTINCT' The codes works well without DISTINCT. Please let me know if i'm missing anything here.I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

我的代码如下:

DECLARE
TYPE t_search_rec
IS
  RECORD
  (
    search_id   VARCHAR2(200),
    search_name VARCHAR2(240),
    description VARCHAR2(240) );
TYPE t_search_data
IS
  TABLE OF t_search_rec;
  in_user_id       NUMBER;
  in_user_role     VARCHAR2(20);
  in_period     VARCHAR2(20) := 'Sep-15';
  in_search_string VARCHAR2(20) := 'v';
  l_search_tt t_search_data;
  x_search_tt t_search_data;
  v_entity_gl VA_LOGIN_API.t_entity_list ;
  X_RETURN_CODE VARCHAR2(20);
  X_RETURN_MSG  VARCHAR2(2000);
  CURSOR c_vendors_gl(v_entity VARCHAR2)
  IS
    SELECT UNIQUE vendor_id,
      vendor,
      NULL description
    FROM XXPOADASH.XX_VA_PO_LINES
    WHERE period = in_period
    AND entity      = v_entity
    AND upper(vendor) LIKE upper(in_search_string)
      ||'%';
BEGIN
  in_user_role := 'ROLE';
  in_user_id   := 4359;
  VA_LOGIN_API.DATA_ACCESS_PROC( X_RETURN_CODE => X_RETURN_CODE, X_RETURN_MSG => X_RETURN_MSG, IN_PERSON_ID => in_user_id, IN_PERSON_ROLE => in_user_role, X_ACCESSED_ENTITY_LIST => v_entity_gl );
  IF( v_entity_gl.COUNT >0) THEN
    x_search_tt        := t_search_data();
    FOR I IN v_entity_gl.FIRST..v_entity_gl.COUNT
    LOOP
      OPEN c_vendors_gl(v_entity_gl(i));
      FETCH c_vendors_gl BULK COLLECT INTO l_search_tt;
      CLOSE c_vendors_gl;
      x_search_tt := x_search_tt MULTISET
      UNION DISTINCT l_search_tt;
      l_search_tt.delete;
    END LOOP;
    IF x_search_tt.count = 0 THEN
      x_return_msg      := 'No lines found';
    END IF;
  END IF;
  DBMS_OUTPUT.PUT_LINE(x_return_msg);
  DBMS_OUTPUT.PUT_LINE(x_search_tt.count);
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(x_return_msg);
  DBMS_OUTPUT.PUT_LINE(x_search_tt.count);
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

推荐答案

multiset union distinct要求集合中的元素具有可比性.在您的情况下，这些元素是PL/SQL记录，不幸的是它们不是可比较的数据结构(即PL/SQL不提供用于比较PL/SQL记录的内置机制).

multiset union distinct requires the elements of the collection to be comparable. In your case the elements are PL/SQL records that are unfortunately not comparable data structures (i.e. PL/SQL provides no build-in mechanism to compare PL/SQL records).

multiset union之所以有效，是因为它不需要比较元素.

multiset union works because it doesn't need to compare the elements.

一种可能的解决方法是使用Oracle对象类型而不是PL/SQL记录.对象类型使您可以实现比较方法 multiset union distinct要求.

One possible workaround is to use Oracle object type instead of PL/SQL record. Object type allows you to implement a comparison method required by multiset union distinct.

这篇关于多集并集的不同给出了“错误数量的类型或传递的参数".错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！