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: 337

Filed under:

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

Related posts about oracle10g