ORA-00907: missing right parenthesis

this insert statement works fine in toad but in sqlplus it returns

ORA-00907: missing right parenthesis
database version is 11g




insert /*+ APPEND */ into sd_schema.fact_sec_tab
        (reqID, YEAR, MONTH, QUARTER, LABS, RADIOLOGY, PHARMRX, PHARMOTC, DME, TRANSPORT,
         EYECARE, LICENSE_NO, req_CD, LOADDATE, EXPORTDATE, TOTAL, HH, BILLING_DATE, LABS_CLAIM_CT,
         RADIOLOGY_CLAIM_CT, PHARMRX_CLAIM_CT, PHARMOTC_CLAIM_CT, DME_CLAIM_CT, TRANSPORT_CLAIM_CT,
             EYECARE_CLAIMS_CT, NURSE, NURSE_CLAIM_CT, HH_CLAIMS_CT, CLINPSYCH, CLINPSYCH_CLAIM_CT)
select req_id, year, month, 'Q'||to_char(to_date(month||year,'MMYYYY'),'Q') quarter, sum(lab) lab, sum(rad) rad, sum(rx) rx, sum(otc) otc, sum(dme) dme,
       sum(trn) trn, sum(eye) eye, null lic, null reqcd, sysdate loaddate, sysdate exportdate, sum(lab+rad+rx+otc+dme+trn+eye+hh) total, sum(hh) hh, to_date(month||year,'MMYYYY') billing_date,
       sum(lab_ct) lab_ct, sum(rad_ct) rad_ct, sum(rx_ct) rx_ct, sum(otc_ct) otc_ct, sum(dme_ct) dme_ct, sum(trn_ct) trn_ct, sum(eye_ct) eye_ct, sum(nurse) nurse, sum(nurse_ct) nurse_ct, sum(hh_ct) hh_ct, sum(clinpsych) clinpsych, sum(clinpsych_ct) clinpsych_ct
from
    (      
        select prescriber_req_ID req_id, year, month, EYE, DME, HH, LAB, OTC, RAD, RX, TRN, lab_ct,rad_ct,rx_ct,otc_ct,dme_ct,trn_ct,nurse,nurse_ct,hh_ct,clinpsych,clinpsych_ct,eye_ct
        fromorders@dtware
        union
        select REFER_req_ID req_id, year, month, EYE, DME, HH, LAB, OTC, RAD, RX, TRN, lab_ct,rad_ct,rx_ct,otc_ct,dme_ct,trn_ct,nurse,nurse_ct,hh_ct,clinpsych,clinpsych_ct,eye_ct
        from ORD_Ret@dtware
    )
where exists (select 1 from sd_schema.fac_tab1 m where m.reqID = req_id)
group by req_id, year, month;
LVL 11
it-rexAsked:
Who is Participating?
 
it-rexAuthor Commented:
I got
it is actually a bug for inser over a dblink
Insert Over Dblink Get Ora-00907 [ID 444002.1]
the fix is just to use /*+ NO_UNNEST */
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.