When i tried to use the following sql (user defined SQL) in target oraoci stage,i am getting ORA-00933 error .
DELETE FROM mdm.mdm_benefit_fact
WHERE MBF_POST_PERIOD_KEY=:3
INSERT INTO MDM.MDM_BENEFIT_FACT (MC_CID_CDE,MG_GROUP_NUMBER,MBF_POST_PERIOD_KEY,MBF_EFFECT_PERIOD_KEY,MB_BENEFIT_KEY,MSI_SUBSID_CDE,MA_AFFILIATION_KEY,MMC_MCTR_KEY,MPR_PRODUCER_KEY,MPB_PRIM_BROK_KEY,MBF_BENEFIT_PROVIDER_KEY,MMC_MRKT_CO_KEY,MR_REP_KEY,MMA_MGR_KEY,MDC_DIRECTOR_KEY,MVP_VP_KEY,MGM_GM_KEY,MFC_COUNTY_KEY,MSIC_SIC_KEY,MS_SEGMENT_KEY,MDCC_DISTRIBUTE_CHANNEL_CDE,MBF_SALE_INVOICE_AMT,MBF_INF_INVOICE_AMT,MBF_CANCEL_INVOICE_AMT,MBF_SALE_AVG_AGES_FEMALE_SUB,MBF_INF_AVG_AGES_FEMALE_SUB,MBF_CANCEL_AVG_AGES_FEMALE_SUB,MBF_SALE_AVG_AGES_MALE_SUB,MBF_INF_AVG_AGES_MALE_SUB,MBF_CANCEL_AVG_AGES_MALE_SUB,MBF_INF_SUB_COUNT_MALE,MBF_SALE_SUB_COUNT_MALE,MBF_CANCEL_SUB_COUNT_MALE,MBF_INF_SUB_COUNT_FEMALE,MBF_SALE_SUB_COUNT_FEMALE,MBF_CANCEL_SUB_COUNT_FEMALE,MBF_LOAD_DATE,MBF_SALE_CONTRACT_COUNT,MBF_INF_CONTRACT_COUNT,MBF_CONTRACT_LOST_COUNT,MBF_ACCOUNT_STATUS,MBF_PSR_STATUS,MBF_SR_STATUS,MBF_SALE_CHANGE,MBF_INF_CHANGE,MBF_CANCEL_CHANGE,MSCA_STAT_CATEGORY_KEY,MBF_BENEFIT_EFFECTIVE_DT,MBR_BENEFIT_CANCEL_DT,MBF_BENEFIT_ID,MFA_FUNDING_ARRANGEMENT_KEY,MNR_NAIC_CD,MMAG_ASSOCIATION_GROUP_ID,MAS_ASSOCIATION_KEY) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,TO_DATE(:37, 'YYYY-MM-DD HH24:MI:SS'),:38,:39,:40,:41,:42,:43,:44,:45,:46,:47,TO_DATE(:48, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:49, 'YYYY-MM-DD HH24:MI:SS'),:50,:51,:52,:53,:54)
Could anydoby guide me whats is wrong with my SQL.
Thanks
ORA-00933:
Moderators: chulett, rschirm, roy
You can't have two statements. The data streaming to this stage uses prepared SQL, so you're going to have to move the DELETE to another stage or job. You'll probably be able to move back to generated SQL then to handle your inserts.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
You would be better served by gathering the distinct values for "mdm.mdm_benefit_fact" and issuing one delete statement. You do realize that for every row in your dataset you will issue that delete statement? Wouldn't you rather have a dedicated job that just takes that list of values and feeds an OCI stage with the generated SQL statement for DELETE? Rather simple and easier, don't you think?
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle