Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL quoting question
I have a related problem.
I'm trying to build a package for use with the WOW gateway, to allow the user to specify text for a VARCHAR2(2000) element. If he enters a single quote, my procedure busts a gut, falls flat on its back and kicks its legs in the air.
I've tried using INSERT INTO yadda-yadda .... REPLACE(Fleep,'''','''''').... , which works just fine for preserving apostrophes, as long as the user has the grace and foresight to enter two. If the inconsiderate louse just enters one, then more bust guts.
I could do something in JavaScript to run through the string and double up the apostrophes before my PL/SQL gets its mitts on it, but that seems like a lot of trouble for something that ought to have been handled a million times so far, and have a built-in solution.
So, the question in brief: "How do I handle unpredictable use of apostrophes in user input to a PL/SQL procedure?"
Any assistance greatly appreciated.
Thomas Kyte wrote:
> >Okay, one quotes an apostrophe (') with adding another one (''),
> >
> > But how does one quote an ampersand (&), and underscore (_), and a
> >percent sign (%). So far, doubling those or adding a preceding slash
> >(\) doesn't seem to work.
> >
>
> well, the & is a sqlplus'ism only. To make that be just another character in
> sqlplus, simply issue:
>
> SQL> set scan off
>
> and & will be just like any other character.
--
Regards,
Robert Smith
Ministry of Health
Received on Tue Jul 28 1998 - 14:20:38 CDT