ibatis, oracle: escaping inside BEGIN/END block

Question

<select id="foo" parameberClass="fooClass">
    {call
        declare
        dsc varchar(512);
        begin
    select
        count(*)
    into dsc
    from foo
    where birthday = TO_DATE(#birthday#, 'YYYY-MM-DD');
        end
        }
</select>

birthday looks like '1999.11.11' and I can't change it, and yes birthday field is varchar not date and I can't change it either..

I get errors because of the . in #birthday string.

ORA-06550: line 1, column 140:
PL/SQL: ORA-00911: invalid character
ORA-06550: line 1, column 48:
PL/SQL: SQL Statement ignored

I've tried quite a few different ways to prevent the error but couldn't find the solution.

Answer 1

birthday field is varchar not date

birthday = TO_DATE(#birthday#, 'YYYY-MM-DD');

This is wrong, you are comparing a string with a date. birthday column is VARCHAR2 data type, and you are comparing it with TO_DATE which converts a string into date.

Either use TO_DATE on both sides, or don't use at all.

For example,

SQL> WITH t(birthday) AS
  2    ( SELECT '1999.11.11' FROM dual
  3    )
  4  SELECT COUNT(*)
  5  FROM t
  6  WHERE TO_DATE(birthday, 'YYYY.MM.DD') = TO_DATE('1999.11.11', 'YYYY.MM.DD');

  COUNT(*)
----------
         1

SQL>