UPDATE statements in oracle pl/sql loop with tablenames as parameters

Question

I have a requirement where I need to run set of UPDATE statements in a for loop. In the cursor there is a column called PROPERTY_ID which is a number and there are many tables that have this number appended. For ex: SELECT * FROM PC_ORG_EXT_111(where 111 is the property_id)

This is the code and it's throwing error. Can anyone assist me if I'm missing something here.

SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0

DECLARE
    V_PROP_ID VARCHAR(200);
    V_CNT NUMBER(25);
    V_SQL_STRING VARCHAR2(500);

   CURSOR CUR_CON
   IS
      SELECT   * FROM PRE_CONVERSION_UNMERGE_LIST;
BEGIN
   FOR REC_CON IN CUR_CON
   LOOP
   V_PROP_ID := 'PROPARCH.PC_ORG_EXT_' || REC_CON.PROPERTY_ID;

     dbms_output.put_line('Property Table Name ' || V_PROP_ID);  

   EXECUTE IMMEDIATE 'select COUNT(1) from ' ||V_PROP_ID;


    EXECUTE IMMEDIATE '
       UPDATE  SIEBEL.S_ACCNT_POSTN
         SET   OU_EXT_ID = ' || REC_CON.VALID_SURVIVIR_REC ||'
       WHERE       OU_EXT_ID = ' || REC_CON.INVALID_SURVIVOR_REC||'
               AND OU_EXT_ID IN (SELECT   ISAC_ROW_ID
                                   FROM   ' || V_PROP_ID || '
                                  WHERE   INTEGRATION_ID = '||REC_CON.DELPHI_ID||')
               AND POSITION_ID NOT IN
                        (SELECT   POSITION_ID
                           FROM   SIEBEL.S_ACCNT_POSTN
                          WHERE   OU_EXT_ID = '||REC_CON.VALID_SURVIVIR_REC||')';

   END LOOP;
END;

Error says : ORA-00933: SQL command not properly ended ORA-06512: at line 20

Also let me know if there's a better way of doing it.

Thanks,

Answer 1

Please try this! - Single Quotes has to be Used with escape characters!

  EXECUTE IMMEDIATE '
           UPDATE  SIEBEL.S_ACCNT_POSTN
             SET   OU_EXT_ID = ''' || REC_CON.VALID_SURVIVIR_REC ||'''
           WHERE       OU_EXT_ID = ''' || REC_CON.INVALID_SURVIVOR_REC||'''
                   AND OU_EXT_ID IN (SELECT   ISAC_ROW_ID
                                       FROM  ' || V_PROP_ID || '
                                      WHERE   INTEGRATION_ID = '''||REC_CON.DELPHI_ID||''')
                   AND POSITION_ID NOT IN
                            (SELECT   POSITION_ID
                               FROM   SIEBEL.S_ACCNT_POSTN
                              WHERE   OU_EXT_ID = '''||REC_CON.VALID_SURVIVIR_REC||''')';