OraFAQ Forum: SQL & PL/SQL » Escaping Single Quotes in dynamic SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Escaping Single Quotes in dynamic SQL

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Escaping Single Quotes in dynamic SQL [message #15604]

Fri, 15 October 2004 10:20

Randy White
Messages: 2
Registered: October 2004

Junior Member

�

�Hi,

�We use dynamic SQL to create insert statements.

�Obviously, it breaks when the inserted value contains a single quote (').� Could one of the experts help us out with the best way to get around this.

�Thanks

�Rand

�

�

�

�

Report message to a moderator

Re: Escaping Single Quotes in dynamic SQL [message #15605 is a reply to message #15604]

Fri, 15 October 2004 11:01

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Single quotes won't break anything if it is coded correctly. How are you doing it?

Report message to a moderator

Re: Escaping Single Quotes in dynamic SQL [message #15610 is a reply to message #15605]

Fri, 15 October 2004 14:09

Randy White
Messages: 2
Registered: October 2004

Junior Member

Here is the relevant code and the Table:

PL/SQL procedure that logs data
from multiple Tables into common LOG Table.

LOG Table has 3 columns, LOG_ID NUMBER(10),
LOG_TXT VARCHAR2(100),
LOG_TYPE VARCHAR(1)

Code for inserting data dynamically is as follows:

v_id NUMBER := 1;
v_txt VARCHAR2(100) := 'ABCDEF';
v_sql VARCHAR2(4000);
v_type VARCHAR(1) := 'E';

v_sql := 'INSERT INTO LOG VALUES(';
v_sql := v_sql || TO_CHAR(v_id);
v_sql := v_sql || ', ' || v_txt;
v_sql := v_sql || ', ' || v_type;
v_sql := v_sql || ')';

execute immediate v_sql;

Above code works fine for most of the cases.
It blows up when v_txt has any single quotes
such as ABC'DEF

In these cases it gives error ORA-00917: missing comma

How do we escape the Single Quote within the v_txt
so that Oracle would be able to insert data into the
table properly.

Any help/clues would be appreciated

Thanks
Randy

Report message to a moderator

Re: Escaping Single Quotes in dynamic SQL [message #15612 is a reply to message #15610]

Fri, 15 October 2004 14:51

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

So, why is this dynamic SQL at all? Why not just:

insert into log values (to_char(v_id), v_txt, v_type);

You are not substituting object names (table, column), so why all the complexity?

Report message to a moderator

Re: Escaping Single Quotes in dynamic SQL [message #15615 is a reply to message #15610]

Fri, 15 October 2004 21:48

Hem Anand
Messages: 68
Registered: March 2004

Member

Hi
Todd Barry is right

But just to have an update : just try this

v_sql := v_sql || ', ' || ''''||v_txt||'''';

These '''' at the run time will be considered as
'ABCDEF'

Regards
J.Hem anand

Report message to a moderator

Re: Escaping Single Quotes in dynamic SQL [message #15619 is a reply to message #15610]

Sat, 16 October 2004 06:53

William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK

Senior Member

Assuming the dynamic code is necessary, you can just use REPLACE to double up all the quotation marks, i.e:

REPLACE(v_txt,'''','''''')

or if you prefer,

REPLACE(v_txt, CHR(39), CHR(39)||CHR(39))

or even

k_quote CONSTANT VARCHAR2(1) := CHR(39);
...
REPLACE(v_txt, k_quote, k_quote||k_quote)

this will change (for example) O'Reilly into O''Reilly.

Report message to a moderator

Re: Escaping Single Quotes in dynamic SQL [message #15649 is a reply to message #15619]

Mon, 18 October 2004 09:11

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Even if for some obscure reason NDS is required (and it is not given the posted code), the use of bind variables with NDS makes the single quote issue a non-issue.

Report message to a moderator

Previous Topic:	Update Table with Trigger or Function or ?
Next Topic:	copy contents of one column to another column in a table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 15 12:27:21 CDT 2025