This discussion is archived
11 Replies Latest reply: Dec 5, 2012 2:34 AM by Stew Ashton RSS

escape syntax error when run on oracle

977539 Newbie
Currently Being Moderated
hi everyone,

My application uses the below query to update record in Oracle database. When I am running this query, I am getting error like "missing expression" . But this query works fine in Sql server.

UPDATE SFMESSAGE SET PUBLICACCESS=-1,RESPONDBYDATE={ts '2012-11-24 00:00:00'} WHERE PROJECTID=42 AND ( (MESSAGETYPE=21 AND OTHERCOMPTRKINGNUM='519711') OR (MESSAGETYPE=3 AND SERFFTRACKINGNUM='519711') )

Actually this is neither SQL SERVER nor Oracle syntax. That curly braces used for 'RESPONDBYDATE' is actually EScape syntax. If it is supported in sql server, it should be supporten in oracle as well.

Please advise.
  • 1. Re: escape syntax error when run on oracle
    Niket Kumar Pro
    Currently Being Moderated
    this will not work in oracle....
    oracle have to_date function to insert date into date column.
  • 2. Re: escape syntax error when run on oracle
    stefan nebesnak Journeyer
    Currently Being Moderated
    The JDBC escape sequence, which is the syntax that you specify between curly brace, is recognized and parsed by the Oracle JDBC drivers.
    {ts foo} use TO_TIMESTAMP
    Check datatype column,
    JDBC connection string, db and table charset.
  • 3. Re: escape syntax error when run on oracle
    BluShadow Guru Moderator
    Currently Being Moderated
    user13050752 wrote:
    Actually this is neither SQL SERVER nor Oracle syntax. That curly braces used for 'RESPONDBYDATE' is actually EScape syntax. If it is supported in sql server, it should be supporten in oracle as well.
    What a silly statement to make.

    Just because SQL Server does something doesn't mean that Oracle should as well. Cars and Planes are both forms of transport, but just because a plane can fly doesn't mean a car should as well.

    There are international standards for things like SQL, and that is the ANSI standards. Oracle supports those ANSI standards. That escape syntax is not a standard recognised by ANSI, so why should Oracle do it? MS SQL Server doesn't cater for all the syntax and functions Oracle offers, and it can't even cope will certain calendar dates that Oracle does (for example) so why doesn't it do what Oracle does?
  • 4. Re: escape syntax error when run on oracle
    977539 Newbie
    Currently Being Moderated
    Hi stefan,

    Thanks for the information.

    I would like to know if JDBC driver comes directly with ORacle 11g or 10g database. actually In our code JDBC escape syntax is used. One of the customers is having error like "missing expression" in oracle environment. that is due to escape syntax.Their database is 10g. So I suspect if Oracle JDBC drive is there or not in their environment.

    please advise.
  • 5. Re: escape syntax error when run on oracle
    BluShadow Guru Moderator
    Currently Being Moderated
    user13050752 wrote:
    Hi stefan,

    Thanks for the information.

    I would like to know if JDBC driver comes directly with ORacle 11g or 10g database. actually In our code JDBC escape syntax is used. One of the customers is having error like "missing expression" in oracle environment. that is due to escape syntax.Their database is 10g. So I suspect if Oracle JDBC drive is there or not in their environment.

    please advise.
    The JDBC drivers are available seperately for install, from the download page...

    http://www.oracle.com/technetwork/indexes/downloads/index.html#database

    But, if your customer is using your application against the oracle database without JDBC connectivity, then just installing the JDBC driver isn't going to magically make the Oracle database understand JDBC syntax unless the application changes it's connectivity to actually use JDBC.
  • 6. Re: escape syntax error when run on oracle
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user13050752 wrote:

    If it is supported in sql server, it should be supporten in oracle as well.
    Neither supports interfacing with coffee machines and making coffee for you. So both must be cr@p... According to your logic. Right?
  • 7. Re: escape syntax error when run on oracle
    Stew Ashton Expert
    Currently Being Moderated
    Billy and BluShadow,

    It appears that this "escape syntax" is a JDBC standard, so it is reasonable to ask that JDBC drivers support it.

    http://docs.oracle.com/cd/E11882_01/java.112/e16548/apxref.htm#JJDBC28913

    I was ready to jump to the conclusion that this was an SQL Server thing, but no. Rereading the original question, the OP did mention that.

    To the OP:

    This looks like something that makes it easier to put literals into queries, which is a very bad thing. OLTP queries should use bind variables.
  • 8. Re: escape syntax error when run on oracle
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Always rubs me the wrong way when the argument "+product A does it, so there product B should too+" is put forward.

    People choose one product over another, not because of sameness, but because of differences. These differences equate to one product being better than the other in the mind of the consumer/user.

    Companies market their products by showcasing differences between their product and that of the competition. Special ingredient X. Eco friendly. Supersized. Prettier colours. Whatever.

    And then we have people, who choose the products they purchase daily based of differences between products, posting here stating that as SQL-Server does feature X, Oracle should do feature X too.

    Is that because of their inability to understand differences? Which does not make sense as they are consumers of products using product differences to decide what to buy and not to buy.

    More likely in my view, it is simply because they are too darn lazy to understand the differences when it comes to Oracle. Quick to blame their lack of Oracle product knowledge on Oracle not being the same as some other product like SQL-Server.
  • 9. Re: escape syntax error when run on oracle
    BluShadow Guru Moderator
    Currently Being Moderated
    Stew Ashton wrote:
    Billy and BluShadow,

    It appears that this "escape syntax" is a JDBC standard, so it is reasonable to ask that JDBC drivers support it.

    http://docs.oracle.com/cd/E11882_01/java.112/e16548/apxref.htm#JJDBC28913

    I was ready to jump to the conclusion that this was an SQL Server thing, but no. Rereading the original question, the OP did mention that.
    OP didn't mention that JDBC was being used. OP only mentioned that query is being run against oracle using escape syntax and it's not working, and is then expecting that Oracle should support escape syntax as he had it working in SQL Server. Yes, JDBC does support it (and is clearly being used in some way by their SQL Server stuff), but OP then goes on to suggest that if JDBC isn't installed as standard on Oracle, then installing it will make the application work... which clearly it won't, as it won't change the way oracle handles that syntax... the syntax would actually have to be diverted down the JDBC connectivity rather than the current application's connectivity method.

    That was the point. ;)
  • 10. Re: escape syntax error when run on oracle
    Stew Ashton Expert
    Currently Being Moderated
    BluShadow wrote:
    OP didn't mention that JDBC was being used.
    OP said "this is neither SQL SERVER nor Oracle syntax". I said the OP did mention that "this was [not] an SQL Server thing". Just trying to be fair.

    Your main point is well taken: the JDBC driver is something that has to be installed with and used by the Java code that submits the query.
  • 11. Re: escape syntax error when run on oracle
    Stew Ashton Expert
    Currently Being Moderated
    Billy  Verreynne  wrote:
    People choose one product over another, not because of sameness, but because of differences. These differences equate to one product being better than the other in the mind of the consumer/user.
    I fully agree, and I am making enemies in my company by defending that point of view.

    In this case, there is a JDBC standard so objectively the issue is different. The part about "Oracle should do this because SQL Server does" was a red herring.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points