Oracle 10g - Best way to escape single quotes
Posted
by satynos
on Stack Overflow
See other posts from Stack Overflow
or by satynos
Published on 2010-03-22T20:14:16Z
Indexed on
2010/03/23
2:31 UTC
Read the original article
Hit count: 340
oracle10g
I have to generate some update statements based off a table in our database. I created the following script which is generating the update statements I need. But when I try to run those scripts I am getting errors pertaining to unescaped single quotes in the content and &B, &T characters which have special meaning in oracle. I took care of the &B and &T problem by setting SET DEFINE OFF. Whats the best way to escape single quotes within the content?
DECLARE
CURSOR C1 IS
SELECT * FROM EMPLOYEES;
BEGIN
FOR I IN C1
LOOP
DBMS_OUTPUT.PUT_LINE('UPDATE EMPLOYEES SET
FIRST_NAME= ''' || I.FIRST_NAME|| ''',
LAST_NAME = ''' || I.LAST_NAME ''',
DOB = ''' || I.DOB|| '''
WHERE EMPLOYEE_ID = ''' || I.EMPLOYEE_ID || ''';');
END LOOP;
END;
Here if the first_name or last_name contains single quotes then the generated update statements break. Whats the best way to escape those single quotes within the first_name and last_name?
© Stack Overflow or respective owner