关于ORA-00907:missingrightparenthesis问题整理

在写存储过程的时候,在把系统时间格式转换用于与数据库字段做比较,当我test时抛出ORA-00907: missing right parenthesis,缺省右括

在写存储过程的时候,在把系统时间格式转换用于与数据库字段做比较,当我test时抛出ORA-00907: missing right parenthesis,缺省右括号;

下面是我的出错代码:

dynamic_sql := 'select t3.departmentcode, t3.total from (select departmentcode,count(*) total from ' || str_tablename || ' where (LASTUPDATEDTIME between to_date(to_char(sysdate-1,"yyyy-mm-dd") ,''yyyy-mm-dd'') and to_date(to_char(sysdate,"yyyy-mm-dd") ,''yyyy-mm-dd'')) and departmentcode is not NULL group by departmentcode) t3,v_s_data_input_dept_usernum t4 where t3.departmentcode = ' || str_departmentcode || '';

更正后,执行成功的代码:

dynamic_sql := 'select t3.departmentcode, t3.total from (select departmentcode,count(*) total from ' || str_tablename || ' where (LASTUPDATEDTIME between to_date(''' || to_char(sysdate-1,'yyyy-mm-dd') || ''',''yyyy-mm-dd'') and to_date(''' || to_char(sysdate,'yyyy-mm-dd') || ''',''yyyy-mm-dd'')) and departmentcode is not NULL group by departmentcode) t3,v_s_data_input_dept_usernum t4 where t3.departmentcode = ' || str_departmentcode || '';

红色的部分对比

希望对出现类似错误的有所帮助

还有第二种情况

(Oracle测试)使用SQL语句的select语句是经常会用到字段和表的的别名,使用要注意,字段的别名前面可以加as关键字,或是省略不写,但是表名的别名前面不可以加as,否则会报如下错误:

命令出错, 行: 11 列: 20
错误报告:
SQL 错误: ORA-00907: 缺失右括号
00907. 00000 - "missing right parenthesis"
*Cause:
*Action: