Oracle cursor for dynamic query

Question

I have an SP that tries to execute a cursor for a dynamic built query Here is the code

procedure echeancnew(p_entity in  varchar2,
                   ref_data OUT ref_cursor)
                   as
    v_sql VARCHAR2(2000);
    v_dblink VARCHAR2(20);
    v_entity VARCHAR2(20);
    v_test1 VARCHAR2(999);
    v_test2 VARCHAR2(999);

begin
  
  SELECT e.dblink,e.descr
          into v_dblink, v_entity 
    FROM CHM_ENTITIES e
   WHERE e.abr = p_entity;

 
  v_sql := q'|
     SELECT j.ENTRY,
     j.Curr "CUR",
     j.Ttype "TYPE",
     j.third_role "ROLE",
     t.Name_i "THIRD",
     j.third "No",
     t.Country_Descr "COUNTRY",
     To_Char(j.expire, 'MONTHYYYY') "M",
     Pkg_Jou.Ent_Bal@|' || v_dblink || q'|(Entry) "AMOUNT",
     Pkg_Jou.Ent_Bal@|' || v_dblink || q'|(Entry) * j.rate "AMNT CHF",
     Sale_Trader.Trader_Descr "RESPV",
     Purch_Trader.Trader_Descr "RESPA",
     T2.Coface "Assur.",
     CASE
       WHEN Nvl(TRUNC(SYSDATE) - TRUNC(j.expire), 0) < 15 THEN 'A<15'
       WHEN Nvl(TRUNC(SYSDATE) - TRUNC(j.expire), 0) BETWEEN 15 AND 30 THEN 'B>15<30'
       WHEN Nvl(TRUNC(SYSDATE) - TRUNC(j.expire), 0) BETWEEN 30 AND 60 THEN 'C>30<60'
       WHEN Nvl(TRUNC(SYSDATE) - TRUNC(j.expire), 0) BETWEEN 60 AND 120 THEN 'D>60<120'
       ELSE 'E>120'
    END "Past Due Inv.",'|' 
    || v_entity || q'|' as Entity

FROM Jou_Jou@|' || v_dblink || q'| j
inner join Thr_v_Third@|' || v_dblink || q'| t ON j.Ttype = t.Ttype
                                AND j.Third = t.Third
inner join Thr_Third@|' || v_dblink || q'| T2 ON j.Ttype = T2.Ttype
                               AND j.Third = T2.Third
right outer join Tra_Affair@|' || v_dblink || q'| a ON j.Entry = a.Entry_Nr
right outer join Tra_Delivery@|' || v_dblink || q'| d ON a.Delivery = d.Delivery_Id
inner join Tra_Contract@|' || v_dblink || q'| Purch ON d.From_Purchase_Contract = Purch.Contract
inner join Tra_Trader@|' || v_dblink || q'| Purch_Trader ON Purch.Trader_Transact =  Purch_Trader.Trader
inner join Tra_Contract@|' || v_dblink || q'| Sale ON d.To_Sale_Contract =Sale.Contract
inner join Tra_Trader@|' || v_dblink || q'| Sale_Trader ON Sale.Trader_Transact = Sale_Trader.Trader

WHERE j.Curr IN ('USD', 'EUR', 'GBP', 'CHF')
 AND Pkg_Jou.Ent_Bal@|' || v_dblink || q'|(Entry) != 0;|';   


v_test1 := substr(v_sql, 1 , 950);
v_test2 := substr(v_sql, 951, 950);

OPEN ref_data FOR v_sql; -- HERE I GET THE ERROR
      
end echeancnew;

The error that I get is ORA-00911: invalid character

When I concat v_test1 and v_test2 and run it as select it works What I am doing wrong?

Answer 1

If I were you, I'd remove empty lines, but - what certainly is wrong - is semi-colon that terminates the V_SQL statement:

 AND Pkg_Jou.Ent_Bal@|' || v_dblink || q'|(Entry) != 0;|'; 
                                                      ^
                                                      |
                                                   remove it!