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

**Never** use `to_timestamp()` without a format mask. Use `TO_TIMESTAMP('13.09.18 11:30:00', 'dd.mm.rr hh24:mi:ss')` — , Sep 18 '18 at 07:42
THanks, I'll keep that in mind, however this does not solve the issue — Attix, Sep 18 '18 at 07:54
I wouldn't be surprised if the lack of a format mask _is_ the reason for your error. — , Sep 18 '18 at 07:55
Executing SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER = 'NLS_TIMESTAMP_FORMAT'; Shows this format: 'DD.MM.RR HH24:MI:SSXFF' Even using this I still get the same error... — Attix, Sep 18 '18 at 08:02
How are `:AStart` and `:AEnd` defined? For example, if you are using SQL\*Plus, it doesn't support `date` or `timestamp` variables, and so probably you defined them as strings (varchar2). Can you edit your post to show a value that is not being accepted? — William Robertson, Sep 18 '18 at 08:37
We really need to know exactly how you defined `:AStart` and `:AEnd` and what values they have. http://idownvotedbecau.se/nomcve — William Robertson, Sep 18 '18 at 09:40
I've tagged this request SQL Developer. The OP says they use this tool to execute the query and pass the bind variable in a comment to my answer. — Thorsten Kettner, Sep 18 '18 at 09:46

Thorsten Kettner · Answer 1 · 2018-09-18T09:25:50.193

2

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').

edited Sep 18 '18 at 09:25

answered Sep 18 '18 at 08:12

Thorsten Kettner

89,309
7
49
73

See my edit, I tried that. Same issue, it works as long as I dont replace '13.09.18 11:33:00' with a parameter. – Attix Sep 18 '18 at 08:14
So you actually passed a string matching the format `'DD.MM.RR HH24:MI:SSXFF'`, e.g. `'13.09.18 11:30:00.00'`? I.e. you added the fractions mentioned in the format string? And this still fails with ORA-01858 "a non-numeric character was found where a numeric was expected"? And you also tried `TO_DATE` instead of `TO_TIMESTAMP` as I recommended for your original date string, and this too fails? – Thorsten Kettner Sep 18 '18 at 08:20
That's correct, I tried those combinations already. It also fails if I use this DATA_TS <= :AEnd And supply SYSTIMESTAMP as Value – Attix Sep 18 '18 at 08:34
`SYSTIMESTAMP` is not a value; it's a function giving you the current time. So this again is code you cannot pass as a bind variable. How are you passing the value? Are you using a programming language or a tool? – Thorsten Kettner Sep 18 '18 at 08:36
I am using the SQL Developer for this. I know that SYSTIMESTAMP is a function, but I did expect to work in this case. – Attix Sep 18 '18 at 08:49
I suppose you are passing the string including the quotes, i.e. you pass `'13.09.18 11:30:00.00'` literally, which is a string starting with a quote, followed by a one, etc. (in SQL this would be `'''13.09.18 11:30:00.00'''`. If this is the case, pass the string without the quotes: `13.09.18 11:30:00,00` for `to_timestamp` or `13.09.18 11:30:00` for `to_date`. I've updated my answer accordingly. – Thorsten Kettner Sep 18 '18 at 09:23

score 1 · Answer 2 · answered Sep 18 '18 at 07:23

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);

answered Sep 18 '18 at 07:23

ScaisEdge

131,976
10
91
107

then you param is not valid timestimp check for the real input values – ScaisEdge Sep 18 '18 at 08:42

score 1 · Answer 3 · answered Sep 18 '18 at 08:12

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.

You are right, the values I provided are looking like ordinary dates. However even copying an existant value from my table and supplying it to the parameter does not work: VALUE: '13.09.18 11:32:43,000000000' ... DATA_TS >= TO_TIMESTAMP(:AStart,'DD.MM.RR HH24:MI:SSXFF') ... — Attix, Sep 18 '18 at 08:27
Consider providing a test case - CREATE TABLE and INSERT INTO sample data, as well as SELECT you then used in order to fetch data from that table. It is difficult to guess what exactly you have, what you did and how Oracle responded. — Littlefoot, Sep 18 '18 at 08:28

score 1 · Answer 4 · answered Sep 18 '18 at 10:29

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

Oracle ORA-01858 when using parameters in query

4 Answers4