Timestamp literal in Oracle SQL Developer

Question

I need to test sql queries on Oracle SQL Developer. These queries contain timestamp literals in the format

{ts 'yyyy-mm-dd hh:mm:ss.fff'}

Oracle SQL Developer does not seem to accept this syntax, the symbol { causes error ORA-00911: invalid character.

Is there anything I can do?

EDIT

The sql editor advises me that { is not allowed. I tried with two other tools (DbVisualizer and DBeaver), both using the Oracle Thin driver, all works fine.

However I still want to use that syntax in Oracle SQL Developer because it has interesting features. The queries I have to test are not written by me, change syntax is not an option.

Camber

That error is not from SQL Developer, is from Oracle itself. Search "oracle timestamp literal" to get info on how to write that literals. – Septennial

SQL Developer simply turns off the JDBC escape processing in the JDBC driver, that's the reason you can't use {ts 'yyyy-mm-dd hh:mm:ss.fff'}. There is nothing you can do in SQL Developer. And Oracle doesn't care and claims it isn't a bug: community.oracle.com/message/14068970#14068970 – Rabbinical

@a_horse_with_no_name yours is the only useful answer – Camber

Answer 1

Use an actual SQL timestamp literal:

TIMESTAMP 'yyyy-mm-dd hh:mm:ss.fff'

What you were using is the JDBC escape syntax, which is supported by JDBC drivers, but not by the Oracle database itself.

Gleason

SQL Developer doesn't support JDBC escapes, because it turns off the escape processing in the JDBC driver. – Rabbinical

Answer 2

You can use CAST

select to_char(cast(sysdate as timestamp),'DD-MON-YYYY HH24:MI:SS.FF') from dual

Druid

cast(sysdate as timestamp) is useless – Rabbinical

Answer 3

See the answer of : How to convert date to timestamp(DD-MON-YYYY HH24:MI:SS.FF format) in oracle?

The "{ts xxx}" syntax is specific to ODBC or OLEDB driver...

Kozlowski

Answer 4

issue with using timestamp literals in the specific format {ts 'yyyy-mm-dd hh:mm:ss.fff'} in Oracle SQL . The curly braces {} are not standard SQL syntax and might be causing the error you're encountering.

you can use the standard ANSI SQL timestamp literal format without the curly braces. Here's how you can format your timestamp literal:

TIMESTAMP 'yyyy-mm-dd hh:mm:ss.fff'

example

SELECT * FROM tablename WHERE timestampcolumn >= TIMESTAMP '2023-08-29 12:34:56.789';

If the queries you're working with were written using the specific {ts 'yyyy-mm-dd hh:mm:ss.fff'} syntax, you might need to replace it with the standard ANSI SQL timestamp literal format to ensure compatibility with Oracle SQL.

Lansquenet