Getting "Non supported SQL92 token at position: 3477: S" error after running the jsp report

Hi,


I am getting the following error after running the jsp report.

Query Tag:doAfterBody(),0,Non supported SQL92 token at position: 3477: S


I know that the following snippet causing the problem ‘{S}’:

…….. AND (L.Applicable_Location_Region_ID IN (N'PARIS2', N'{S}Paris2'))

As per my investigation, I came to know following:
Oracle parses the SQL and treats the text of "'{S}Paris2" as a string literal then finds {S} which it is treating as a SQL Escape Sequence which it doesn't recognize.

The following link explains well about “Escape Characters”:
http://docs.oracle.com/cd/F49540_01/DOC/inter.815/a67843/cqspcl.htm

we must use {S} followed by name.

The above AND statement works fine with SQL Server and MySQL, but causes problem with Oracle as “When you use braces to escape a single character, the escaped character becomes a separate token in the query.”

Due to above reason the application treats {S}, as the escaped a single character with braces becomes a separate token in the query.

So please could you help me how can I escape single character with braces in oracle or any suggestion please

Unfortunately although SQL is a standard there are differences in the syntax for SQL Server and Oracle so what works in one database may not work in the other.

You are quite right in saying that {S} is treated by Oracle as being an escape character. Normally characters are escaped because they would otherwise have a special meaning. Therefore, for example, if you wanted your string to include an ampersand '&' you would have to escape it. There are 2 ways of escaping the special meanings of characters, so to use a string containing '&' you could either write '\&' or '{&}'.

Your problem seems to be that you need to escape the escape character! The documentation is not clear. It does state
"The open brace { signals the beginning of the escape sequence, and the closed brace } indicates the end of the sequence. Everything between the opening brace and the closing brace is part of the escaped query expression (including any open brace characters). To include the close brace character in an escaped query expression, use }}. "
This suggests therefore that using '{S}}' would work and your code would become:

…….. AND (L.Applicable_Location_Region_ID IN (N'PARIS2', N'{S}}Paris2'))

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.