Pl sql Oracle invalid use of type name or subtype name

Question

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;

APC · Accepted Answer · 2014-11-02 11:14:09Z

2

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;

edited Nov 2, 2014 at 11:14

answered Nov 2, 2014 at 9:07

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alan Mulligan Over a year ago

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

Collectives™ on Stack Overflow

Pl sql Oracle invalid use of type name or subtype name

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related