Home » SQL & PL/SQL » SQL & PL/SQL » & sign in an SQL statement
& sign in an SQL statement [message #286143] Thu, 06 December 2007 10:31 Go to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
I have a SQL statement as follows

SELECT * FROM  OPTIONS_VIEW WHERE short_nm='S&P400'


but when I run it I get the following error

Enter value for p400:


Question is how can I get around with it, i.e. an & sign
Re: & sign in an SQL statement [message #286145 is a reply to message #286143] Thu, 06 December 2007 10:35 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If you are in SQL*Plus, turn variable substitution off with

set define off


before running the query.
Re: & sign in an SQL statement [message #286146 is a reply to message #286145] Thu, 06 December 2007 10:39 Go to previous messageGo to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
I am running the query from an application, so is there a way to make this escape a part of the query?
Re: & sign in an SQL statement [message #286149 is a reply to message #286146] Thu, 06 December 2007 10:43 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That would depend entirely on the application and how it connects to Oracle, so there is no general answer.

Re: & sign in an SQL statement [message #286152 is a reply to message #286149] Thu, 06 December 2007 10:47 Go to previous messageGo to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
Ok I am executing a query from a java app that I am writing and am plaing this SQL query in a String over there. So if I can put the ESCAPE as a part of that query itself It would be good.
Re: & sign in an SQL statement [message #286153 is a reply to message #286152] Thu, 06 December 2007 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT a problem of SQL.
IT IS a problem of YOUR application that interprets the &.

Regards
Michel
Re: & sign in an SQL statement [message #286156 is a reply to message #286152] Thu, 06 December 2007 10:57 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I'm also writing java apps (using the Oracle thin driver)

There this never happens. I just tried a similar query as PreparedStatement and as CallableStatement.

It worked both times without the "error".

Which looks pretty much like the SQL*Plus variable prompt. You are not calling the SQL*Plus binary from your application, are you?
Previous Topic: How to get the OS-User
Next Topic: dbms_stats.gather_table_stats in stored procedure
Goto Forum:
  


Current Time: Sat Dec 10 10:55:15 CST 2016

Total time taken to generate the page: 0.11033 seconds