TABLE OF VARCHAR2 in WHERE clause

Question

The code is simplified. I have a type:

   CREATE TYPE str_tab_t IS TABLE OF VARCHAR2(20);

Here a function:

FUNCTION MYFUNCTION(myVar IN str_tab_t ) RETURN VARCHAR
myVar2 NUMBER; 
BEGIN
  SELECT MYCOLUMN INTO myVar2  FROM MYTABLE WHERE MYCOLUMN IN(SELECT * FROM myVar );
  RETURN myVar2 ;
END MYFUNCTION;

I wonder if it possible to call the function like this:

 MYFUNCTION(str_tab_t ('abc'));

As I'm getting error ORA-01722. I don't think, that it is because of wrong query. I think, I'm passing the argument in a wrong way. Could someone give me any clue? Thanks!

https://stackoverflow.com/questions/5165580/how-to-use-a-table-type-in-a-select-from-statement — OldProgrammer, Nov 25 '21 at 20:14
Try `WHERE MYCOLUMN MEMBER OF myVar` or `WHERE MYCOLUMN IN ( SELECT * FROM TABLE(myVar) )` — Wernfried Domscheit, Nov 25 '21 at 20:18

score 0 · Answer 1 · answered Nov 25 '21 at 20:22

Did you by any chance OVERsimplified it?

Because, if table contents is something like this (i.e. strings in MYCOLUMN)

SQL> select * from mytable;

MYC
---
abc
def

and you want the function to return a number, then you can't select mycolumn (which is a string) into a number datatype local function variable myvar2. But, you can select some number (such as 1 in my example):

SQL> create or replace function myfunction (myvar in str_tab_t)
  2    return number
  3  is
  4    myvar2 number;
  5  begin
  6    select 1 into myvar2
  7    from mytable
  8    where mycolumn in (select * from table(myvar));
  9    return myvar2;
 10  end;
 11  /

Function created.

SQL> select myfunction(str_tab_t('abc')) from dual;

MYFUNCTION(STR_TAB_T('ABC'))
----------------------------
                           1

SQL>

Or, obviously, return a different datatype:

SQL> create or replace function myfunction (myvar in str_tab_t)
  2    return mytable.mycolumn%type
  3  is
  4    myvar2 mytable.mycolumn%type;
  5  begin
  6    select mycolumn into myvar2
  7    from mytable
  8    where mycolumn in (select * from table(myvar));
  9    return myvar2;
 10  end;
 11  /

Function created.

SQL> select myfunction(str_tab_t('abc')) from dual;

MYFUNCTION(STR_TAB_T('ABC'))
-------------------------------------------------------------------------
abc

SQL>

Thank you. Yes, I oversimplified it. The problem is definitely related with passing the argument. — hajduk, Nov 25 '21 at 21:01
To me, it is related to RETURN value datatype, not the argument ... — Littlefoot, Nov 26 '21 at 07:10

TABLE OF VARCHAR2 in WHERE clause

1 Answers1