How to fix ORA-00911: invalid character in Mybatis query

Question

I'm new to mybatis and i'm facing the following error:

ORA-00911: invalid character

In mybatis I execute this query:

<select id="queryWithProblem" 
        parameterType="javaClass" 
        resultMap="javaMap">

    SELECT DISTINCT TSV.SPLTSV_CDG_CARRIER_VUELO SPLTSV_CDG_CARRIER_VUELO, 
        TSV.SPLTSV_NMR_VUELO SPLTSV_NMR_VUELO, 
        TSV.SPLTSV_TMS_SALIDA_LOCAL SPLTSV_TMS_SALIDA_LOCAL, 
        TSV.SPLTSV_TMS_SALIDA_UTC SPLTSV_TMS_SALIDA_UTC, 
        TSV.SPLTSV_FCH_VUELO SPLTSV_FCH_VUELO 
    FROM SPLPSV PSV, 
          SPLTSV TSV 
    WHERE PSV.SPLSSV_SEQ_CDG = #{id, jdbcType=NUMERIC} 
            AND SPLPSV_FCH_INGRESO <![CDATA[ >= ]]> TRUNC(#{entranceDate, 
            jdbcType=DATE}, 'DD') 
            AND SPLPSV_FCH_INGRESO  <  TRUNC(#{entranceDate, jdbcType=DATE}, 
            'DD') + 1
            AND TRUNC(#{entranceDate, jdbcType=DATE}, 'DD') = 
            #{entranceDate, jdbcType=DATE} 
            AND TSV.SPLTSV_SEQ_CDG = PSV.SPLTSV_SEQ_CDG;

</select>

I have tried changing the wraping the where clause but i'm still getting the same error.

<select id="queryWithProblem" 
        parameterType="javaClass" 
        resultMap="javaMap">

        SELECT DISTINCT TSV.SPLTSV_CDG_CARRIER_VUELO SPLTSV_CDG_CARRIER_VUELO, 
            TSV.SPLTSV_NMR_VUELO SPLTSV_NMR_VUELO, 
            TSV.SPLTSV_TMS_SALIDA_LOCAL SPLTSV_TMS_SALIDA_LOCAL, 
            TSV.SPLTSV_TMS_SALIDA_UTC SPLTSV_TMS_SALIDA_UTC, 
            TSV.SPLTSV_FCH_VUELO SPLTSV_FCH_VUELO 
        FROM SPLPSV PSV, 
            SPLTSV TSV 
            <![CDATA[
        WHERE PSV.SPLSSV_SEQ_CDG = #{id, jdbcType=NUMERIC} 
                AND SPLPSV_FCH_INGRESO >= TRUNC(#{entranceDate, jdbcType=DATE}, 'DD') 
                AND SPLPSV_FCH_INGRESO  <  TRUNC(#{entranceDate, jdbcType=DATE}, 'DD') + 1
                AND TRUNC(#{entranceDate, jdbcType=DATE}, 'DD') = #{entranceDate, jdbcType=DATE} 
                AND TSV.SPLTSV_SEQ_CDG = PSV.SPLTSV_SEQ_CDG;
                ]]>

    </select>

When I run this query directly on oracle I have no problem.

[EDIT]

Problem solved by removing the ; at the end of the query.

@a_horse_with_no_name thank you that solve the problem I miss that `;` — Juan Enrique Riquelme, Jul 04 '19 at 13:57
additionally you could use `<` `>=` instead of `<` and `>=` in your original SQL — are, Jul 04 '19 at 18:56

score 1 · Answer 1 · answered Jul 04 '19 at 13:50

Description When you encounter an ORA-00911 error, the following error message will appear:

ORA-00911: invalid character

Cause You tried to execute a SQL statement that included a special character.

Resolution The option(s) to resolve this Oracle error are:

Option #1 This error occurs when you try to use a special character in a SQL statement. If a special character other than $, _, and # is used in the name of a column or table, the name must be enclosed in double quotations.

Option #2 This error may occur if you've pasted your SQL into your editor from another program. Sometimes there are non-printable characters that may be present. In this case, you should try retyping your SQL statement and then re-execute it.

Option #3 This error occurs when a special character is used in a SQL WHERE clause and the value is not enclosed in single quotations.

For example, if you had the following SQL statement:

SELECT * FROM suppliers
WHERE supplier_name = ?;

You would receive the following error message:

Oracle PLSQL

You could correct this error by enclosing the ? in single quotations as follows:

SELECT * FROM suppliers
WHERE supplier_name = '?';

Thank you for your answer, in this case the problem was a `;` at the end of the query. I think this could be a 4# or a complement for 1#. — Juan Enrique Riquelme, Jul 04 '19 at 14:01
Yes, I've read a comment It is also a possible reason which is already resolved your problem. — Dushyant Tankariya, Jul 04 '19 at 14:04

How to fix ORA-00911: invalid character in Mybatis query

1 Answers1