6

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.

barney..gumble
  • 71
  • 2
  • 5
  • That error is not from SQL Developer, is from Oracle itself. Search "oracle timestamp literal" to get info on how to write that literals. – Alberto Martinez Sep 22 '17 at 13:46
  • 1
    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: https://community.oracle.com/message/14068970#14068970 –  Sep 22 '17 at 15:50
  • @a_horse_with_no_name yours is the only useful answer – barney..gumble Sep 23 '17 at 16:48

4 Answers4

10

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.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • 2
    SQL Developer doesn't support JDBC escapes, because it turns off the escape processing in the JDBC driver. –  Sep 22 '17 at 15:49
2

You can use CAST 

select to_char(cast(sysdate as timestamp),'DD-MON-YYYY HH24:MI:SS.FF') from dual
Srini V
  • 11,045
  • 14
  • 66
  • 89
0

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

Didier68
  • 1,027
  • 12
  • 26
0

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.

dasun
  • 11
  • 2