Oracle 10g - Best way to escape single quotes

Question

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?

Answer 1

You can use REPLACE:

DECLARE
    CURSOR C1 IS
        SELECT * FROM EMPLOYEES;

BEGIN
    FOR I IN C1
    LOOP
        DBMS_OUTPUT.PUT_LINE('UPDATE EMPLOYEES SET 
            FIRST_NAME= ''' || REPLACE(I.FIRST_NAME,'''','''''') || ''',
            LAST_NAME = ''' || REPLACE(I.LAST_NAME,'''','''''') || ''',
            DOB = TO_DATE(''' || TO_CHAR(I.DOB,'DD/MM/YYYY') || '',''DD/MM/YYYY'')'
            WHERE EMPLOYEE_ID = ' ||  I.EMPLOYEE_ID || ';');
    END LOOP;
END;

Notes:

• You were missing a || after I.LAST_NAME.
• I assume I.EMPLOYEE_ID is a number - in which case, I would not surround it with quotes.
• I assume I.DOB is a date - in which case, I recommend you explicitly cast it to a date.

ALTERNATIVE: If you're on Oracle 10g or later, you can use this alternative syntax which may be easier to read; and use REPLACE to make it a bit more obvious what's going on - this is my personal preference:

DECLARE
    CURSOR C1 IS
        SELECT * FROM EMPLOYEES;

BEGIN
    FOR I IN C1
    LOOP
        DBMS_OUTPUT.PUT_LINE(REPLACE(REPLACE(REPLACE(REPLACE(
         q'[UPDATE EMPLOYEES SET 
            FIRST_NAME= '#FIRST_NAME#',
            LAST_NAME = '#LAST_NAME#',
            DOB = TO_DATE('#DOB#','DD/MM/YYYY')
            WHERE EMPLOYEE_ID = #EMPLOYEE_ID#;
           ]'
           ,'#FIRST_NAME#', I.FIRST_NAME)
           ,'#LAST_NAME#', I.LAST_NAME)
           ,'#DOB#', TO_CHAR(I.DOB,'DD/MM/YYYY'))
           ,'#EMPLOYEE_ID#', I.EMPLOYEE_ID)
           );
    END LOOP;
END;

The above has the advantage that it is easy to spot possible errors in the dynamic SQL, which is not checked at compile time for syntax errors.