Oracle ORA-01858 when using parameters in query

Question

I have a table which contains a column of type TIMESTAMP(6), if I try to query this table everything is fine as long as I don't parametrize it.

This works:

SELECT
  COUNT(*)
FROM
  t_data
WHERE
  DATA_TS >= TO_TIMESTAMP('13.09.18 11:30:00')
  AND   DATA_TS <= TO_TIMESTAMP('13.09.18 11:33:00');

However this fails with ORA-01858 "a non-numeric character was found where a numeric was expected" supplying the values as stated above. I also tried to wrap the parameters in TO_TIMESTAMP() using the format Mask from the NLS_PARAMETERS.

SELECT
  COUNT(*)
FROM
  t_data
WHERE
  DATA_TS >= TO_TIMESTAMP(:AStart,'DD.MM.RR HH24:MI:SSXFF')
  AND   DATA_TS <= TO_TIMESTAMP(:AEnd,'DD.MM.RR HH24:MI:SSXFF');

Anyone knows what causes this issue?

Testcase: Create Table

create table t_data ( 
    data varchar2(80), 
    data_ts timestamp 
);

Insert DATA

Insert into t_data (data,data_ts) VALUES ('TEST', systimestamp);

Using &AStart instead of :AStart works well, if that helps somebody

Answer 1

You should use param inside the function

SELECT   COUNT(*)
FROM  t_data
WHERE   DATA_TS >= TO_TIMESTAMP(:AStart)
  AND   DATA_TS <= TO_TIMESTAMP(:AEnd);

Answer 2

Is it really a timestamp? Because, values you posted look like ordinary dates to me.

Anyway: I believe you should use TO_TIMESTAMP and provide appropriate format mask to the parameter value. It is not that you should apply NLS settings format mask, but the one you used when you typed value of that parameter.

For example:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
18.09.2018 10:07:57

SQL> select to_timestamp('&par_ts', 'dd.mm.yy hh24:mi:ss') result from dual;
Enter value for par_ts: 18.09.18 10:08:23

RESULT
---------------------------------------------------------------------------
18.09.18 10:08:23,000000000

SQL>

If you passed differently formatted parameter, format mask has to reflect that change:

SQL> select to_timestamp('&par_ts', 'mm-yyyy-dd hh24:mi:ss') result from dual;
Enter value for par_ts: 09-2018-18 10:10:15

RESULT
---------------------------------------------------------------------------
18.09.18 10:10:15,000000000

SQL>

It means that your query should look like this:

SELECT COUNT(*)
FROM  t_data
WHERE DATA_TS >= TO_TIMESTAMP(:AStart, 'dd.mm.yy hh24:mi:ss')
  AND DATA_TS <= TO_TIMESTAMP(:AEnd, 'dd.mm.yy hh24:mi:ss');

Fix the format mask, if necessary.

Answer 3

You cannot use TO_TIMESTAMP('13.09.18 11:30:00') as a bind variable content. The DBMS expects a value, not code.

So you'd either pass a timestamp or date value, if your tool or programming language allows that, or you pass the string '13.09.18 11:30:00' only and have TO_DATE(:AStart, 'dd.mm.rr hh24:mi:ss') in your query.

UPDATE: Make sure not to include the quotes when passing a string as a bind variable. Pass the string content only (e.g. 13.09.18 11:30:00, not '13.09.18 11:30:00').

Answer 4

Thanks everyone for your contribution. I was on the wrong track the whole time.

After uninstalling my old version of SQL Developer and installing a current one the issue disappeared. I don't know what caused it but it got fixed with that.

Result of this is I now know more about time and date formatting as before.

Regards Attix