Oracle调用TABLE OF VARCHAR2入参函数报ORA-01722错误如何解决

ORA-01722为无效数字错误，你遇到的问题由两个可能的原因共同导致：

• 集合查询语法错误：Oracle中不能直接将嵌套表类型变量放在FROM子句中作为查询源，必须使用TABLE()函数将集合转为可读取的行集。你写的SELECT * FROM myVar逻辑不成立，Oracle解析时会尝试将myVar集合对象和MYCOLUMN的NUMBER类型做隐式转换，直接触发报错。
• 类型不匹配：如果MYCOLUMN是NUMBER类型，你传入的集合元素是字符串'abc'，就算语法正确，字符串转数字失败也会抛出该错误。

另外你的代码还有两处语法隐患：一是函数定义中变量声明前漏写IS关键字，二是函数声明返回VARCHAR类型但实际返回NUMBER类型的myVar2，存在隐式转换风险。

首先修改函数中的查询逻辑，用TABLE()函数包裹集合参数，同时对齐返回值类型：

FUNCTION MYFUNCTION(myVar IN str_tab_t ) RETURN NUMBER -- 和实际返回的变量类型对齐
IS -- 补充缺失的IS关键字
myVar2 NUMBER; 
BEGIN
  SELECT MYCOLUMN INTO myVar2  
  FROM MYTABLE 
  WHERE MYCOLUMN IN (SELECT * FROM TABLE(myVar)); -- 用TABLE()包裹集合参数
  RETURN myVar2 ;
END MYFUNCTION;

如果MYCOLUMN为NUMBER类型，还需要保证传入集合的元素都是可合法转为数字的字符串，也可以直接将集合定义修改为TABLE OF NUMBER和字段类型完全对齐，避免隐式转换。

额外优化建议：建议补充NO_DATA_FOUND、TOO_MANY_ROWS异常处理，避免查询结果为空或返回多行时函数抛出未处理异常。

内容的提问来源于stack exchange，提问作者hajduk