Oracle SQL使用TYPE封装查询结果报ORA-00907缺失右括号错误

错误根因 #

你在调用自定义对象类型uploaded_data的构造函数时，在传入的CASE表达式参数后错误添加了列别名AS IS_DELETABLE。
对象构造函数的括号参数列表内只允许传入值表达式，不支持给单个参数写AS 别名的语法。Oracle解析到这个不属于参数语法的AS关键字时，会判定括号内语法非法，抛出缺失右括号的报错。

注意：ORA-00907属于Oracle的通用语法报错，绝大多数场景下并不是真的漏写了右括号，而是括号覆盖的语法块内出现了不符合位置规则的关键字、符号，导致解析器判定语法块应该提前闭合，才会抛出这个提示。

排查修正步骤 #

• 第一步：移除构造函数参数内的别名声明
  把构造函数括号里CASE表达式后面的AS IS_DELETABLE删掉，别名如果需要加，要写在整个构造函数的括号外面，给最终生成的uploaded_data类型列起别名，不能写在构造参数列表里。
• 修正后参考SQL：

select uploaded_data(
    a.DATA_ID, 
    b.FILE_NAME, 
    b.CREATION_DATE, 
    CASE WHEN a.cdvs_data is null THEN 'N' ELSE 'Y' END
) AS uploaded_data_row
from cl_dat_dst_his c 
inner join CL_DAT_VERSION a on c.CL_DAT_VERSION_ID=a.CL_DAT_VERSION_ID
inner join cl_history b on c.CDVS_HIST_ID = b.CL_HIS_ID  
where b.CL_HIS_TABLE_ID=14424 
ORDER BY b.CREATION_DATE DESC

• 第二步：类型定义匹配校验
  如果改完别名问题还是报错，核对uploaded_data类型的定义：
  • 传入参数的数量必须和类型定义的属性数量完全一致，当前场景对应4个属性，不能多传也不能少传
  • 参数顺序必须和类型定义的属性顺序一一对应
  • 每个传入值的类型必须和对应属性的类型兼容，比如日期位置不能传字符串、字符串位置不能传数字

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