I got an ORA-00907 exception when I used mybatis to execute sql

Question

I'm trying to query some data from my DB, This is my SQL code and it runs smoothly in the console：

select  BTN_NAME as btnName,                
    LAST_EXEC_TIME as lastExecTime,                
    LAST_EXEC_PARAM as lastExecParam         
from BTN_FUNC_FLOW_CTRL
where BTN_NAME = 'REG_FILE_COLLECT' AND LAST_EXEC_TIME >= (SYSTIMESTAMP - INTERVAL '60' MINUTE);

But when I wrote this sql in mybatis and executed it, something went wrong, I got an ORA-00907

<select id="getByBtnName" resultType="xxx.BtnFuncFlowCtrl">
    select BTN_NAME as btnName,
           LAST_EXEC_TIME as lastExecTime,
           LAST_EXEC_PARAM as lastExecParam
    from regulatory.BTN_FUNC_FLOW_CTRL
    where BTN_NAME = #{btnName} and LAST_EXEC_TIME >= (SYSTIMESTAMP - INTERVAL #{execInterval} MINUTE);
</select>

### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis

### The error may exist in file [C:\Users\Administrator\IdeaProjects\xxx\BtnFuncFlowCtrlMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select BTN_NAME as btnName,                LAST_EXEC_TIME as lastExecTime,                LAST_EXEC_PARAM as lastExecParam         from regulatory.BTN_FUNC_FLOW_CTRL         where BTN_NAME = ? and LAST_EXEC_TIME >= (SYSTIMESTAMP - INTERVAL ? MINUTE);
### Cause: java.sql.SQLSyntaxErrorException: ORA-00907:missing right parenthesis

When I remove the parenthesis, I got an ORA-00933

<select id="getByBtnName" resultType="xxx.BtnFuncFlowCtrl">
    select BTN_NAME as btnName,
           LAST_EXEC_TIME as lastExecTime,
           LAST_EXEC_PARAM as lastExecParam
    from regulatory.BTN_FUNC_FLOW_CTRL
    where BTN_NAME = #{btnName} and LAST_EXEC_TIME >= SYSTIMESTAMP - INTERVAL #{execInterval} MINUTE;
</select>

### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

### The error may exist in file [C:\Users\Administrator\IdeaProjects\xxx\BtnFuncFlowCtrlMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select BTN_NAME as btnName,                LAST_EXEC_TIME as lastExecTime,                LAST_EXEC_PARAM as lastExecParam         from regulatory.BTN_FUNC_FLOW_CTRL         where BTN_NAME = ? and LAST_EXEC_TIME >= SYSTIMESTAMP - INTERVAL ? MINUTE;
### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

I wonder why those questions arise and how to fix them.

For ORA-00907, see https://stackoverflow.com/a/38056493/1261766 . For ORA-00933, see https://stackoverflow.com/a/57739792/1261766 . — ave, Jun 20 '23 at 04:19

score 0 · Accepted Answer · answered Jun 20 '23 at 07:30

Interval literal should have literals as its parts: it should be constant.

Literals

The terms literal and constant value are synonymous and refer to a fixed data value.

What you are trying to compose is not a constant because it has bind variable.

SQL> variable i varchar2(10);
SQL> 
SQL> exec :i := '10';

PL/SQL procedure successfully completed.

SQL> 
SQL> select *
  2  from dual
  3  where systimestamp > systimestamp - interval :i minute
  4  ;

Error starting at line : 7 in command -
select *
from dual
where systimestamp > systimestamp - interval :i minute

Error at Command Line : 9 Column : 46
Error report -
SQL Error: ORA-00933: неверное завершение SQL-предложения
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:

However, you may use interval arithmetic and specify base value of interval and multiply it by the number of units you want:

SQL> select dual.*, interval '1' minute * to_number(:i) as interval_value
  2  from dual
  3  where systimestamp > systimestamp - interval '1' minute * to_number(:i)
  4  ;

D INTERVAL_VALUE     
- -------------------
X +00 00:10:00.000000

1 row selected.

Or use numtodsinterval function for variable unit:

SQL> variable u varchar2(10);
SQL> exec :u := 'minute';

PL/SQL procedure successfully completed.

SQL> 
SQL> select dual.*, numtodsinterval(to_number(:i), :u) as interval_value
  2  from dual
  3  where systimestamp > systimestamp - numtodsinterval(to_number(:i), :u)
  4  ;

D INTERVAL_VALUE     
- -------------------
X +00 00:10:00.000000

1 row selected.

I got an ORA-00907 exception when I used mybatis to execute sql

1 Answers1