CODEWITHSUNDEEP
oracle-databasecollectionsplsqluser-defined-types
Alan Mulligan
Alan Mulligan

Reputation: 1198

Pl sql Oracle invalid use of type name or subtype name

I am trying to search an array of uppercase letters to see if a letter is in the array. But I am getting the error: Error(7,27): PLS-00330: invalid use of type name or subtype name but I can't seem to fix it.

create or replace FUNCTION fun_ISUPPER(parCharAt IN VARCHAR2)
RETURN number IS
varCharAt VARCHAR2(1) := parCharAt;
TYPE upperCharArr IS VARRAY(4) OF VARCHAR2(1);
ARRAY upperCharArr := upperCharArr('A', 'B', 'C', 'D');  
BEGIN   
IF varCharAt MEMBER OF upperCharArr THEN
    RETURN 1;
ELSE
    RETURN 0;
END IF;
END;

Upvotes: 0

Views: 2549

Answers (1)

APC
APC

Reputation: 146349

You have declared a type. To use it you need to declare a variable of that type.

" i now get the error Error(7,6): PLS-00306: wrong number or types of arguments in call to 'MEMBER OF'"

That is because you're using a VARRAY. The documentation advises us "There is no mechanism for comparing varrays." So you should use a nested table instead. The only reason for using a VARRAY is if we need to retain the order of elements in the collection; I don't think that applies here. Here is a working version:

create or replace FUNCTION fun_ISUPPER(parCharAt IN VARCHAR2)
  RETURN number IS
  varCharAt VARCHAR2(1) := parCharAt;
  TYPE upperCharArr IS TABLE OF VARCHAR2(1);
  l_array upperCharArr := upperCharArr('A', 'B', 'C', 'D');  
BEGIN   
  IF varCharAt MEMBER OF l_array THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
END;

Upvotes: 2

Related Questions