Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

According to this (quite old) article the principle behind ODBC escape sequences is that

The ODBC driver reads the escape sequence and translates it into the DBMS-specific syntax before sending the query to the database

However if I try the following

SQL Server

  SELECT  {fn concat ('foo', 'bar')};



  SELECT  {fn concat ('foo', 'bar')};

The escape sequences remain untranslated in the object definition and calling the routines works fine. That indicates to me that the RDBMSs themselves natively understand the syntax rather than relying on translation so will work independent of connection method. Is that understanding correct and if so does this also apply to Oracle?

share|improve this question
up vote 3 down vote accepted

This seems to work only through a JDBC connection. Your SELECT works with Oracle, PostgreSQL and DB2 when using a Java/JDBC based query tool.

Using the native tools this statement throws an error:

Connected to:
Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning and OLAP options

SQL> SELECT  {fn concat ('foo', 'bar')} from dual;
SELECT  {fn concat ('foo', 'bar')} from dual
ERROR at line 1:
ORA-00911: invalid character

The same is true for PostgreSQL:

psql (9.1.1)
Type "help" for help.

postgres=> SELECT  {fn concat ('foo', 'bar')};
ERROR:  syntax error at or near "{"
LINE 1: SELECT  {fn concat ('foo', 'bar')};

and DB2:

(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.0

db2 => SELECT  {fn concat ('foo', 'bar')} from sysibm.sysdummy1;
SQL0104N  An unexpected token "SELECT  {" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".
share|improve this answer
+1 Thanks for the comprehensive answer. BTW - Did you try creating a stored routine in any of those DBMSs? I presume in that instance the JDBC driver just rewrites it as || as the article I referenced suggests? – Martin Smith Oct 16 '11 at 13:54
No, I didn't create a stored procedure. If the statement does not work on it's own it will not work inside a stored procedure either – a_horse_with_no_name Oct 16 '11 at 13:59

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.