View Not Compiling with Single quotes using execute immediate

B

Brad P

Member
In a view, trouble with single quotes... I'm getting a pls-00103 encounterd the symbol "COUNTY" when expecting one of the following... on the line as follow: , NVL(RTRIM(SUBSTR(C.ROW_DATA,1,30)),'County Deleted Value') AS ADR_VRF_SRC_DESC

The vendor shares this code and I'm trying to compile this on pl/sql version 8.2 to run as a job on 11g. Please bear with me on the line wrapping... some comments spill to next line.

Thanks in advance... b

--prompt
--prompt Creating view RT_ADR_VRF_SRC
--prompt ============================
--prompt
execute immediate 'CREATE OR REPLACE VIEW CIS42.RT_ADR_VRF_SRC AS
SELECT
RTRIM(SUBSTR(A.ROW_DATA,5,2))AS ADR_VRF_SRC_CD
, NVL(RTRIM(SUBSTR(C.ROW_DATA,1,30)),'County Deleted Value') AS ADR_VRF_SRC_DESC
, DECODE(C.USR_SELCT_SW,'Y',A.USR_SELCT_SW,'N') USR_SELCT_SW
, MAX(C.EFF_BGN_DT) AS EFF_BGN_DT
, C.EFF_END_DT
FROM
RT_DATA A --rt_vrf_chklst_src.
, RT_DATA_K B -- K for rt_vrf_chklst_src.
, RT_DATA C -- rt_vrf_src.
WHERE
B.REFR_TBL_ID = 962 -- rt_vrf_chklst_src.
AND A.REFR_TBL_ID = B.REFR_TBL_ID -- rt_vrf_chklst_src.
AND C.REFR_TBL_ID(+) = 967 -- rt_vrf_src.
AND A.DATA_KEY = B.DATA_KEY -- Match Key to Base.
AND RTRIM(SUBSTR(A.ROW_DATA,1,2)) = 'AD' -- a.item_cd, the RT is for.
AND RTRIM(C.DATA_KEY(+)) = RTRIM(SUBSTR(A.ROW_DATA,5,2)) -- c.vrf_src_cd=a.src_cd
AND c.eff_bgn_dt=(SELECT MAX(d.eff_bgn_dt) FROM RT_DATA d
WHERE d.refr_tbl_id=c.refr_tbl_id
AND d.data_key=c.data_key )
GROUP BY
RTRIM(SUBSTR(A.row_data,5,2)) -- a.src_cd
, RTRIM(SUBSTR(c.row_data,1,30)) -- c.vrf_src_desc
, DECODE(C.USR_SELCT_SW,'Y',A.USR_SELCT_SW,'N')
, C.EFF_END_DT';
 
When using a quote inside a quoted string, you need to replace it with 2 quotes. For example:

Code: 
execute immediate
 'CREATE OR REPLACE VIEW CIS42.RT_ADR_VRF_SRC AS
  SELECT RTRIM(SUBSTR(A.ROW_DATA,5,2))AS ADR_VRF_SRC_CD,
  NVL(RTRIM(SUBSTR(C.ROW_DATA,1,30)),''County Deleted Value'') AS ADR_VRF_SRC_DESC
  ...
 
Thanks, that's the solution. I placed single quotes around all the occurances of single quotes... thanks again... b
 
You must log in or register to reply here.
Back
Top