Getting an error ORA-00933 while using EXECUTE IMMEDIATE

oracle

Hi i am using 1 dynamic query which has a very big length size as below.

v_DymnamicQuery :=
    'WITH TempResult as
    (
        SELECT
        f.rbase_fmla_name as FilingName, f.user_comp_eff_dt, f.user_rnwl_dt, f.user_n_bus_dt, f.pf_wkstn_oid_lng, f.pf_wkstn_oid_sh, f.fmla_flng_u_exp_dt as expDate,
        rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn) as FileTag,
        ''Formula'' as DisplayType, null as Origin, f.wkstn_oid_lng, f.wkstn_oid_sh, CAST(f.iteration_number AS varchar(10) )||''.''||CAST(f.minor_version AS varchar(10)) as version, status_indc, f.status_indc as WIPStatus,
        upper(RTrim(f.rbase_fmla_name) ||''-''|| CAST(f.user_n_bus_dt AS varchar(10)) ||''-''|| CAST(f.user_comp_eff_dt as varchar(10)) ||''-''|| CAST(f.user_rnwl_dt AS varchar(10)) ||''-''|| CAST(f.iteration_number AS varchar(10)) ||''-''|| rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn)) as NameWithDates' 
        || v_FmlaQuery ||
        ' from fmla_flng_adptn f '
        || v_FmlaCondition ||
        ' where '|| p_isFormulas ||'= 1 AND (rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn)) = COALESCE('||v_FileTag||', (rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn))) 
        and f.rbase_fmla_name like ''%''|| COALESCE('||v_name||', f.rbase_fmla_name)||''%''
    union all
        SELECT
        f.rbase_rttbl_name as FilingName, f.user_comp_eff_dt, f.user_rnwl_dt, f.user_n_bus_dt, f.pf_wkstn_oid_lng, f.pf_wkstn_oid_sh, f.rt_flng_u_exp_dt as ExpDate,
        rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn) as FileTag, 
        (case f.rt_flng_type_indc when ''C'' then ''Constant Table'' else ''Keyed Table'' end) as DisplayType, null as Origin,f.wkstn_oid_lng, f.wkstn_oid_sh, CAST(f.iteration_number AS varchar(10))||''.''||CAST(f.minor_version AS varchar(10)) as version, status_indc, f.status_indc as WIPStatus,
        upper(RTrim(f.rbase_rttbl_name) ||''-''|| CAST(f.user_n_bus_dt AS varchar(10)) ||''-''|| CAST(f.user_comp_eff_dt AS varchar(10)) ||''-''|| CAST(f.user_rnwl_dt as varchar(10) ) ||''-''|| CAST(f.iteration_number as varchar(10)) ||''-''|| rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn)) as NameWithDates' 
        || v_TableQuery ||
        ' from rt_flng_adptn f 
        where '|| p_isTables ||'= 1 AND rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn) = COALESCE('||v_FileTag||', rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn)) and f.rbase_rttbl_name like ''%''|| COALESCE('||v_name||', f.rbase_rttbl_name)||''%''
    ),
    UnionTable as 
    (   
        SELECT ROW_NUMBER() OVER(ORDER BY ' || p_SortColumn || ') as RowNumber,
        FilingName, user_comp_eff_dt, user_rnwl_dt, user_n_bus_dt, pf_wkstn_oid_lng, pf_wkstn_oid_sh, expDate,
        FileTag, DisplayType, Origin, wkstn_oid_lng, wkstn_oid_sh, version, status_indc, WIPStatus,NameWithDates '
        || v_UnionQuery ||
        ' from TempResult 
    )
    SELECT *
    FROM UnionTable
    WHERE RowNum > '||v_FirstRec||' 
    AND RowNum < '||v_LastRec||'; 
    --AND RowNum = '||v_LastRecForRow;

    dbms_output.put_line(v_DymnamicQuery);

    EXECUTE IMMEDIATE v_DymnamicQuery;

while running the EXECUTE IMMEDIATE it is throwing an error as:
ORA-00933: SQL command not properly ended
ORA-06512: at "RATABASE.GETFILINGSWITHPAGING", line 120
ORA-06512: at line 5

even when i am running the same query(string) which comes from
dbms_output.put_line(v_DymnamicQuery);
it is successfully running without any error.

can someone help me how to use this or handle this situation.

Best Answer

At the end of the string, you're concatenating a final closing semi-colon; however, you don't have a closing quote after it.

Instead of:

AND RowNum < '||v_LastRec||';

Try this:

AND RowNum < '||v_LastRec||';'
Related Question