How to do run merge statement in orcle through APOC

(Tanmoymoulik) #1

Dear All,

I my project there is need to push the data in oracle DB based on condition.SO currenly I am pushing the data in oracle temp table and later writting seperate script to merge statement in oracle db to do the needful.Is is possible to do oracle merge statement through APOC ?

The script to push the data in temp table in oracle is given below:

Match (s:Site)-[r:Site2eq]-(c:EQLte)-[:Eq2shelf]-(x:SHLte)-[:Shelf2Card]-(e:CALte)
where e.EPOC_MODIFIED_DATE>timestamp()-28800000
with e,s,c
CALL apoc.load.jdbcUpdate('jdbc:oracle:thin:smdb/smdb@10.66.146.143:1521/rilsmdb',
'Insert into DEVICE2M2
(
LOGICAL_NAME,
VENDOR,
LOCATION,
TYPE,
PSTATUS,
UPDATED_BY,
ISTATUS,
ASSET_TAG,
CORP_STRUCTURE,
CREATED_BY,
RIL_CIRCLE,
RIL_AREA,
RIL_ZIP,
RIL_MNTPOINT,
RIL_CITY,
RIL_MAKEMODEL,
RIL_REGION,
PARENTCI,
RIL_GRANITETYPE,
RIL_R4GSTATE,
RIL_SAPID,
RIL_SITEOWNER,
RIL_SITELEASEDOWNER,
RIL_FRIENDLYNAME,
RIL_DEVICETYPE,
RIL_JIOCENTRE,
RIL_SITETYPE
)
values
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
LOG ERRORS INTO err$_DEVICE2M2 ('DEVICE2M2')
REJECT LIMIT UNLIMITED ',
[
e.CARD_CI_NAME,
c.EQUIP_VENDOR,
e.EQUIP_SITE_NAME,
c.DEVICE_TYPE,
s.JIO_CENTER_CODE,
'GRAPHDB',
e.CARD_STATUS,
e.EQUIP_NATIVENAME,
s.SITE_DISTRICT,
'GRAPHDB',
s.SITE_CIRCLE,
s.SITE_AREACODE,
s.POST_CODE,
s.SITE_MAINTENANCEPOINT,
s.SITY_CITY,
e.EQUIP_MODEL,
s.SITE_REGION,
'LTE Network',
e.EQUIP_MODEL,
s.SITE_R4G_STATE,
s.SAPID,
s.SITE_OWNER,
s.SITE_LEASED_REFERENCE_CODE,
s.SITE_FRIENDLY_NAME,
'CARD',
s.JIO_CENTER,
s.SITE_TYPE
]) YIELD row
return row;

Later the script exeucted in ORACLE DB is given below to do the needful.

MERGE INTO DEVICE2M1 d1
USING (SELECT * from DEVICE2M2 ) d2
ON (d1.LOGICAL_NAME = d2.LOGICAL_NAME)
WHEN MATCHED THEN UPDATE SET
d1.VENDOR=d2.VENDOR,
d1.LOCATION=d2.LOCATION,
d1.TYPE=d2.TYPE,
d1.PSTATUS=d2.PSTATUS,
d1.UPDATED_BY=d2.UPDATED_BY,
d1.ISTATUS=d2.ISTATUS,
d1.ASSET_TAG=d2.ASSET_TAG,
d1.CORP_STRUCTURE=d2.CORP_STRUCTURE,
d1.CREATED_BY=d2.CREATED_BY,
d1.RIL_CIRCLE=d2.RIL_CIRCLE,
d1.RIL_AREA=d2.RIL_AREA,
d1.RIL_ZIP=d2.RIL_ZIP,
d1.RIL_MNTPOINT=d2.RIL_MNTPOINT,
d1.RIL_CITY=d2.RIL_CITY,
d1.RIL_MAKEMODEL=d2.RIL_MAKEMODEL,
d1.RIL_REGION=d2.RIL_REGION,
d1.PARENTCI=d2.PARENTCI,
d1.RIL_GRANITETYPE=d2.RIL_GRANITETYPE,
d1.RIL_R4GSTATE=d2.RIL_R4GSTATE,
d1.RIL_SAPID=d2.RIL_SAPID,
d1.RIL_SITEOWNER=d2.RIL_SITEOWNER,
d1.RIL_SITELEASEDOWNER=d2.RIL_SITELEASEDOWNER,
d1.RIL_FRIENDLYNAME=d2.RIL_FRIENDLYNAME,
d1.RIL_DEVICETYPE=d2.RIL_DEVICETYPE,
d1.RIL_JIOCENTRE=d2.RIL_JIOCENTRE,
d1.RIL_SITETYPE=d2.RIL_SITETYPE,
d1.LAST_UPDATE=sysdate,
d1.SYSMODTIME=sysdate,
d1.DEVICEMODTIME=sysdate
WHEN NOT MATCHED THEN INSERT (LOGICAL_NAME,
VENDOR,
LOCATION,
TYPE,
PSTATUS,
UPDATED_BY,
ISTATUS,
ASSET_TAG,
CORP_STRUCTURE,
CREATED_BY,
RIL_CIRCLE,
RIL_AREA,
RIL_ZIP,
RIL_MNTPOINT,
RIL_CITY,
RIL_MAKEMODEL,
RIL_REGION,
PARENTCI,
RIL_GRANITETYPE,
RIL_R4GSTATE,
RIL_SAPID,
RIL_SITEOWNER,
RIL_SITELEASEDOWNER,
RIL_FRIENDLYNAME,
RIL_DEVICETYPE,
RIL_JIOCENTRE,
RIL_SITETYPE,
LAST_UPDATE,
SYSMODTIME,
DEVICEMODTIME,
CREATED_BY_DATE
) VALUES
(
d2.LOGICAL_NAME,
d2.VENDOR,
d2.LOCATION,
d2.TYPE,
d2.PSTATUS,
d2.UPDATED_BY,
d2.ISTATUS,
d2.ASSET_TAG,
d2.CORP_STRUCTURE,
d2.CREATED_BY,
d2.RIL_CIRCLE,
d2.RIL_AREA,
d2.RIL_ZIP,
d2.RIL_MNTPOINT,
d2.RIL_CITY,
d2.RIL_MAKEMODEL,
d2.RIL_REGION,
d2.PARENTCI,
d2.RIL_GRANITETYPE,
d2.RIL_R4GSTATE,
d2.RIL_SAPID,
d2.RIL_SITEOWNER,
d2.RIL_SITELEASEDOWNER,
d2.RIL_FRIENDLYNAME,
d2.RIL_DEVICETYPE,
d2.RIL_JIOCENTRE,
d2.RIL_SITETYPE,
sysdate,
sysdate,
sysdate,
sysdate
);

Is it possible to achieve the same through APOC onlty rather than writing 2 scirpt in both system?

Thanks,
Tanmoy

0 Likes