EXECUTE IMMEDIATE 'UPDATE GDW_ARC_CDM.LND_DIM_GEO
SET DESCRIPTION_COLUMN = REPLACE( DESCRIPTION_COLUMN, '`', '''' )
WHERE CNTRY_ID = 'KR'
AND SRC_ID = 'KR_RET'
AND DESCRIPTION_COLUMN LIKE '%`%'';
COMMIT;
I'm trying to add the above statement in Oracle SP, I m getting below error, the same query works in SQL Developer as a normal statement (not in SP).
Could you please suggest?
Problem is in a fact that you have to enclose that UPDATE into single quotes (what you did), but taking care about other single quotes within that code. All of them have to be "doubled", or - a simpler and easier to read & maintain option - use the q-quoting mechanism.
Something like this:
SQL> select * From lnd_dim_geo;
DES CN SRC_ID
--- -- ------
x`y KR KR_RET
SQL> begin
2 execute immediate q'[update lnd_dim_geo set description_column = replace(description_column, '`', '')
3 where cntry_id = 'KR' and src_id = 'KR_RET' and description_column like '%%']';
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select * From lnd_dim_geo;
DES CN SRC_ID
--- -- ------
xy KR KR_RET
SQL>
If this is your original query then why you are using dynamic query at all.
You can use the following directly:
UPDATE LND_DIM_GEO
SET
DESCRIPTION_COLUMN = REPLACE(DESCRIPTION_COLUMN, '`', '')
WHERE CNTRY_ID = 'KR'
AND SRC_ID = 'KR_RET'
AND DESCRIPTION_COLUMN LIKE '%%'
