0

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.

  • 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 – a_horse_with_no_name Dec 19 '18 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. – BriteSponge Dec 20 '18 at 10:45
  • WHERE RowNum > xyz is useless and always return zero rows. – Wernfried Domscheit Dec 28 '18 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. – Wernfried Domscheit Dec 28 '18 at 21:54
-2

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. – BriteSponge Dec 21 '18 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 '19 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. – BriteSponge Jan 9 '19 at 9:20
  • Ah, OK - sorry ~cheers~ – Andrew Jan 9 '19 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.