Q:

pl sql and dynamic sql

I am trying to create some dynamic sql using the following code block
    firstSqlStatement := true;
    updateText := 'UPDATE T_EMPLOYEES SET ';
    if FIRSTNAME IS NOT NULL and FIRSTNAME > 0 THEN
        updateText:=updateText || ' firstName=' || FIRSTNAME || ' ';
        firstSqlStatement := false;
    end if;

    if MIDDLENAME IS NOT NULL and length(MIDDLENAME) > 0 THEN
        if firstSqlStatement = false THEN
            updateText:=updateText || ',';
        end if;

        updateText:=updateText || ' middleName=' || MIDDLENAME || ' ';
        firstSqlStatement := false;
    end if;

    updateText:=updateText 
           || ' where upper(id)=upper(' || ID ||');';                     
   DBMS_OUTPUT.put_line(updateText);
   EXECUTE IMMEDIATE updateText;

The statement never executes properly as there are missing single quotes around values.
Any ideas what i can do to make this small example work or is there any better way of doing this?

A:

firstSqlStatement := true;
updateText := 'UPDATE T_EMPLOYEES SET ';

if FIRSTNAME IS NOT NULL and FIRSTNAME > 0 THEN
    updateText:=updateText || ' firstName=''' || FIRSTNAME || ''' ';
    firstSqlStatement := false; 
end if;

if MIDDLENAME IS NOT NULL and length(MIDDLENAME) > 0 THEN
    if firstSqlStatement = false THEN
        updateText:=updateText || ',';
    end if;

    updateText:=updateText || ' middleName=''' || MIDDLENAME || ''' ';
    firstSqlStatement := false;
end if;
updateText:=updateText || ' where upper(id)=upper(' || ID || ');';
DBMS_OUTPUT.put_line(updateText);
EXECUTE IMMEDIATE updateText;

use '''

