How to update the below statement, i am getting error while update

Question

I am trying to update the RRP_VALIDATION value as below, but getting error as sql command not ended properly, how to run the below statement with correct format.

UPDATE HES_REPORT_REF_PARAMS 
SET RRP_VALIDATION = 'select to_char(RECONCILED_ID), decode(STATUS, 'R', 'RECONCILED', 'UNRECONCILED')||' - bank '||BANK_CODE||' - date '||to_char(RECONCILED_DATE, 'DD-MON-YYYY') from HES_BANK_RECONCILED_GROUPS where CONTROL_CONTEXT_ID = TO_NUMBER(:PARAMETER.BPX_CCI) order by RECONCILED_ID desc'
WHERE HES_REPORT_REF_PARAMS.RRP_MODULE_NUM = '100';

Answer 1

A simple option is to use the q-quoting mechanism, so that you wouldn't have to worry about string literals which have to be enclosed into two single quotes (and that can get pretty ugly).

UPDATE HES_REPORT_REF_PARAMS
   SET RRP_VALIDATION =
          q'[select to_char(RECONCILED_ID), decode(STATUS, 'R', 'RECONCILED', 'UNRECONCILED')||' - bank '||BANK_CODE||' - date '||to_char(RECONCILED_DATE, 'DD-MON-YYYY') from HES_BANK_RECONCILED_GROUPS where CONTROL_CONTEXT_ID = TO_NUMBER(:PARAMETER.BPX_CCI) order by RECONCILED_ID desc]'
 WHERE HES_REPORT_REF_PARAMS.RRP_MODULE_NUM = '100';

Simplified:

This is what you have:

SQL> select 'select 'a' from dual' from dual;
select 'select 'a' from dual' from dual
                 *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Two single quotes:

SQL> select 'select ''a'' from dual' from dual;

'SELECT''A''FROMDUAL
--------------------
select 'a' from dual

The q-quoting mechanism:

SQL> select q'[select 'a' from dual]' from dual;

Q'[SELECT'A'FROMDUAL
--------------------
select 'a' from dual

SQL>