Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Dynamic SQL and Data with Single Quotes in it.

HouseofHungerSep 18 2008 — edited Sep 19 2008
Hi There,
I have a problem in that I am using dynamic SQL and it happens that one of the columns does contain single quotes (') in it as part of the data. This causes the resultant dynamic SQL to get confused as the single quote that is part of the data is taken to mean end of sting, when in fact its part of the data. This leaves out a dangling single quote that was meant to enclose the string. Here is my dynamic SQL and the result of the parsed SQL that I have captured:

****Dynamic SQL*****
l_sql:='select NOTE_TEMPLATE_ID '||
'FROM TMP_NOTE_TEMPLATE_VALUES '||
'where TRIM(LEGACY_NOTE_CODE)='''||trim(fp_note_code)||''' '||
'and TRIM(DISPLAY_VALUE)='''||trim(fp_note_text)||''' ';
execute immediate l_sql INTO l_note_template_id;
***************************

Because the column DISPLAY_VALUE contains data with single quotes, the resultant SQL is:

******PARSED SQL************
select NOTE_TEMPLATE_ID
FROM TMP_NOTE_TEMPLATE_VALUES
where TRIM(LEGACY_NOTE_CODE)='INQ' and TRIM(DISPLAY_VALUE)='Cont'd'
************************************

And the problem lies with the single quote between teh characters t and d in the data field for DISPLAY_ITEM. How can I handle this?

Many thanks,
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 17 2008
Added on Sep 18 2008
15 comments
3,087 views