Oracle 10g - Best way to escape single quotes
- by satynos
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?
