Dynamic SQL in Oracle

Had a case which need to use daynamic SQL and execute immediate statement. Share and memo a solution.

Customer's Needs:
There're two tables, A and B.
1. Update some columns in table B
2. The name of columns which will be updated are saved in table A(colname)
3. The columns' new value are saved in table A(colvalue)
4. Before get data from table A, we don't know which column will be updated.

Method:
1. Get data from table A and put in an cursor.
2. Create a dynamic SQL
3. Execute dynamic SQL

Code:

CREATE OR REPLACE PROCEDURE UPD_B(
returnVAL OUT PLS_INTEGER
) AS
-- SQL
wSQL VARCHAR2(31000);
wSQL_SET VARCHAR2(30000);
-- column name
wUPD_COL A.COLNAME%TYPE;
-- column value
wUPD_COL_VAL VARCHAR2(30000) := '';

CURSOR C_A IS
SELECT COLNAME, COLVALUE FROM A;
R_A C_A%ROWTYPE;

BEGIN
DBMS_OUTPUT.PUT_LINE('Start');

FOR R_A IN C_A LOOP
-- init
wSQL := '';
-- daynamic update SQL start
wSQL := 'UPDATE B SET ' || COLNAME || '=' || COLVALUE;
EXECUTE IMMEDIATE wSQL;
-- SQL end
COMMIT;
returnVAL := '1';

END LOOP;

EXCEPTION
-- Some functions
END UPD_B;
/

SQL with where:

wSQL := 'UPDATE B' || ' SET ' || COLNAME || ' = ' || COLVALUE || ' WHERE FLG1 = :parameter1' || ' AND FLG2 = :parameter2';
EXECUTE IMMEDIATE wSQL 
USING paramV1, paramV2;

When use '||' to concatenate strings, don't forget the space between two strings.