Home » SQL & PL/SQL » SQL & PL/SQL » Single quote problem
Single quote problem [message #353030] Fri, 10 October 2008 12:17 Go to next message
Messages: 7
Registered: October 2008
Junior Member

I have a problem which i need to solve urgently. Kindly help.

I am using a select statement which substitutes where condition depending on user input like:

select <col>
from <tbl_name>
where &where_clause

The value of this where_clause is obtained from a pl/sql block which has the following line for this:

:where_clause := ' AND f1.category = ''' || :CODE||'''' ;

And user needs to enter the value of CODE variable. And code value is not just numbers, it
includes single quotes also lets say ABTF's, SD, RA's and so on.
The problem is:
If user enter anything which has a single qoutes then this statement wont work.
and it gives error: Quoted string not properly terminated.
For ex:
If user enter RA's then this time it gives an error.

We are using oracle 9 so i cant use regular expressions and neither can i implement q' method.

Can you tell me what can i do in this case so as when value is substituted in where clause it wont give this error??
Is pattern matching possible here??

Kindly help..
Re: Single quote problem [message #353031 is a reply to message #353030] Fri, 10 October 2008 12:23 Go to previous message
Michel Cadot
Messages: 63911
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should better post the actual (and format it as requested in OraFAQ Forum Guide as "&where_clause" and ":where_clause" are 2 different things.

In addition, generally speaking, "' AND f1.category = ''' || :CODE||''''" is silly, if you use bind variables you don't concatenate them in a string.


Previous Topic: 2 separate select works but when union all gives error
Next Topic: Error Creating a Database trigger
Goto Forum:

Current Time: Sat Oct 22 22:20:42 CDT 2016

Total time taken to generate the page: 0.24406 seconds