Oracle: Dynamic SQL query error for subquery right parenthesis(ORA-00907: missing right parenthesis)

Question

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;

Log (or print) the SQL statement you've built before you try to execute it. Separate from the error you're getting building the SQL statement, your `execute immediate` is incorrect. You'd need to select the result `into` something (a `bulk collect` if the query can return multiple rows). It also doesn't make sense to use dynamic SQL here-- static SQL would be more efficient, easier to code, easier to maintain, and vastly easier to debug — Justin Cave, Jul 08 '22 at 17:07
there is an error when you contacted '''' , after parsing that raised error. — Farshid Shekari, Jul 08 '22 at 19:30
Apart from the error you are getting - any reason you are not using bind variables ? It will perform better, is more secure and puts less work on your database. — Koen Lostrie, Jul 09 '22 at 08:31
It was an existing dynamic query and my requirement is just add NVL and sub query to existing query. I am not using bind variables because the dynamic executes with in the loop and we are passing cursor for loop record type column name. — Devendra S, Jul 11 '22 at 02:51

score 1 · Accepted Answer · answered Jul 08 '22 at 17:14

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);

The fix is working fine. Thank you Brahmanand Singh. – Devendra S Jul 09 '22 at 17:45 — Devendra S, Jul 09 '22 at 17:45

score 1 · Answer 2 · answered Jul 09 '22 at 09:24

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;

Yes. I agree with you. I can use static but it is exiting query and I am adding only NVL and sub query to existing query. The existing dynamic query variable is used in xml tags generation logic. — Devendra S, Jul 11 '22 at 02:44

Oracle: Dynamic SQL query error for subquery right parenthesis(ORA-00907: missing right parenthesis)

2 Answers2