Forum Stats

  • 3,733,951 Users
  • 2,246,846 Discussions
  • 7,856,949 Comments

Discussions

deterministic function errors

Zeratul
Zeratul Member Posts: 14
edited May 2015 in SQL

Hello i have problem with call my deterministic functions:

1 scenario:

SELECT *
  FROM v_ReferencedTeam rt
 WHERE PKG_TextUtils.EqualItems(PKG_TextUtils.SplitStringToWords(rt.Name), 
                                PKG_TextUtils.SplitStringToWords('Team one')
                               ) = 1

raise check null parameters exception in function PKG_TextUtils.SplitStringToWords

I know in view v_ReferencedTeam are all not null values.

2 scenario:

SELECT PKG_TextUtils.EqualItems(PKG_TextUtils.SplitStringToWords(rt.Name),
                                PKG_TextUtils.SplitStringToWords('Team one'))
  FROM v_ReferencedTeam rt

raise errors "Oracle ORA-03113: end-of-file on communication channel"

or "ORA-22163: left hand and right hand side collections are not of same type" in PKG_TextUtils.EqualItems

3 scenario:

DECLARE
  l_number NUMBER;
BEGIN
  FOR rec IN (SELECT PKG_TextUtils.SplitStringToWords(rt.Name) AS a,
                     PKG_TextUtils.SplitStringToWords('Team one') AS b
                FROM v_ReferencedTeam rt) LOOP
    l_number := PKG_TextUtils.EqualItems(rec.a, rec.b);
  END LOOP;
END;

runs fine

This is my function

  FUNCTION EqualItems(p_Items1 T_TABLE_VARCHAR2_100, p_Items2 T_TABLE_VARCHAR2_100) RETURN NUMBER DETERMINISTIC AS
    l_Items1         T_TABLE_VARCHAR2_100 := p_Items1;
    l_Items2         T_TABLE_VARCHAR2_100 := p_Items2; -- ORA-22163
    l_MinEqualsCount NUMBER := 0;
    l_EqualsCount    NUMBER := 0;
  BEGIN
    l_MinEqualsCount := LEAST(p_Items1.Count, p_Items2.Count);
    FOR i1 IN 1 .. l_Items1.Count LOOP
      FOR i2 IN 1 .. l_Items2.Count LOOP
        IF l_Items1(i1) = l_Items2(i2) THEN
          l_Items1(i1) := NULL;
          l_Items2(i2) := NULL;
          l_EqualsCount := l_EqualsCount + 1;
        END IF;
      END LOOP;
    END LOOP;
    IF l_EqualsCount >= l_MinEqualsCount THEN
      RETURN 1;
    ELSE
      RETURN 0;
    END IF;
  END;

I Usning Oracle 11g

Sign In or Register to comment.