I have executed below block and I am getting the error message as ORA-00907: missing right parenthesis. I have closed right parenthesis but still it is giving error. I am not understating what is wrong in my query. Can anyone please suggest the solution for this error.

declare
  vv_xml_disc_query           VARCHAR2(32767) := NULL;
 cust_lang varchar2(10):='F';
   trx_id number:=2978023;
   line_so_line_id number:=4496418;
   customer_trx_line_id number:=3459305;
   
  begin
 
 vv_xml_disc_query :=  'SELECT   '||'(SELECT NVL(moct.translated_offer_name,disc_desc)
                         FROM tablel moct,
                              ozf_offers oo
                         WHERE moct.offer_id = oo.offer_id
                         AND oo.qp_list_header_id = opa.list_header_id'
                           ||'AND moct.language = '||''''||cust_lang||''''||')'||'DISC_DESC,
                        DISC_RATE, 
                        DISC_UNIT_PRICE,      
                        DISC_EXT_AMT,       
                        DISC_AMT FROM table2 WHERE CUSTOMER_TRX_ID = '||trx_id 
                          || ' AND interface_line_attribute6 = '|| line_so_line_id
                          || ' AND customer_trx_line_id = ' ||customer_trx_line_id ; 
                                                      
                                                      
EXECUTE IMMEDIATE vv_xml_disc_query; 
dbms_output.put_line('vv_xml_disc_query:'||vv_xml_disc_query);
exception when others then
dbms_output.put_line('error:'||SQLERRM);
end;

CodePudding user response：

Please print the query before execute, that will show you the issue. I believe you need to put spaces after opa.list_header_id and AND

I fixed it here and see if that works

  vv_xml_disc_query           VARCHAR2(32767) := NULL;
 cust_lang varchar2(10):='F';
   trx_id number:=2978023;
   line_so_line_id number:=4496418;
   customer_trx_line_id number:=3459305;
   
  begin
 
 vv_xml_disc_query :=  'SELECT   '||'(SELECT NVL(moct.translated_offer_name,disc_desc)
                                                     FROM tablel moct,
                                                          ozf_offers oo
                                                     WHERE moct.offer_id = oo.offer_id
                                                     AND oo.qp_list_header_id = opa.list_header_id'
                                                       ||' AND moct.language = '||''''||cust_lang||''''||')'||' DISC_DESC,
                                                    DISC_RATE, 
                                                    DISC_UNIT_PRICE,      
                                                    DISC_EXT_AMT,       
                                                    DISC_AMT FROM table2 WHERE CUSTOMER_TRX_ID = '||trx_id 
                                                      || ' AND interface_line_attribute6 = '|| line_so_line_id
                                                      || ' AND customer_trx_line_id = ' ||customer_trx_line_id ; 
                                                      
                                                      
    -- 
dbms_output.put_line('vv_xml_disc_query:'||vv_xml_disc_query);
EXECUTE IMMEDIATE vv_xml_disc_query;
exception when others then
dbms_output.put_line('error:'||SQLERRM);

CodePudding user response：

Try to use static query than dynamic queries. Use dynamic queries when there are multiple combinations in the WHERE clause and if we convert to static occupies huge number of line to handle each combination else always go with static.

Your query is simple one so go with static.

Have converted your dynamic to static. Please verify and use it.

Change INTO to BULK COLLECT INTO when the SELECT returns multiple rows

DECLARE
    Cust_lang VARCHAR2 (10) := 'F';
    Trx_id NUMBER := 2978023;
    Line_so_line_id NUMBER := 4496418;
    Customer_trx_line_id NUMBER := 3459305;
    Value1 Tablel.Translated_offer_name%ROWTYPE;
    Value2 Table2.Disc_rate%ROWTYPE;
    Value3 Table2.Disc_unit_price%ROWTYPE;
    Value4 Table2.Disc_ext_amt%ROWTYPE;
    Value5 Table2.Disc_amt%ROWTYPE;
BEGIN
    SELECT (SELECT NVL (Moct.Translated_offer_name, Disc_desc)
        FROM Tablel Moct
            ,Ozf_offers Oo
        WHERE Moct.Offer_id = Oo.Offer_id
            AND Oo.Qp_list_header_id = Opa.List_header_id
            AND Moct.Language = Cust_lang) Disc_desc
        ,Disc_rate
        ,Disc_unit_price
        ,Disc_ext_amt
        ,Disc_amt
    INTO Value1
        ,Value2
        ,Value3
        ,Value4
        ,Value5
    FROM Table2
    WHERE Customer_trx_id = Trx_id
        AND Interface_line_attribute6 = Line_so_line_id
        AND Customer_trx_line_id = Customer_trx_line_id;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.Put_line ('error:' || SQLERRM);
END;