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

v_DymnamicQuery :=
    'WITH TempResult as
        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
        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;


    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.

  • 2
    The error message does not match your query string. There is no "ratabase" identifier in your query. It's probably hidden in the content of the variables v_FmlaQuery, v_FmlaCondition, v_name, v_FileTag, v_TableQuery, p_isTables, v_UnionQuery, v_FirstRec or v_LastRec. And probably it's the reason from your other question: dba.stackexchange.com/questions/225332 Dec 19, 2018 at 19:00
  • As your query has embedded quoted strings you might also find it useful to use Oracles alternative quoting system. You start the assignment with q'[ and finish with ]'. You can then enter whatever you need between the square brackets without having to double/triple quote strings. Dec 20, 2018 at 10:45
  • WHERE RowNum > xyz is useless and always return zero rows. Dec 28, 2018 at 21:48
  • When you have a SELECT statement then you must use EXECUTE IMMEDIATE v_DymnamicQuery INTO ..... And you should consider parameters with USING, it will make the quoting simpler. Dec 28, 2018 at 21:54

1 Answer 1


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||';'
  • Down-votes without a comment are not good. @Andrew take a look at docs.oracle.com/cd/B19306_01/appdev.102/b14261/…. The first line in the section 'Usage Notes' is what you want. Dec 21, 2018 at 13:36
  • I didn't down vote anything. I answered the question, and RDFozz kindly edited for me to make it more in line with the house style. Sorry if I did something wrong
    – Andrew
    Jan 8, 2019 at 14:42
  • I wasn't implying that YOU downvoted I was noting that your answer shouldn't have been downvoted without a comment. The comment to you was just to point to the documentation. Sorry for the confusion. Jan 9, 2019 at 9:20
  • Ah, OK - sorry ~cheers~
    – Andrew
    Jan 9, 2019 at 12:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.