MySQL Forums
Forum List  »  Other Migration

Migrating Oracle Procedure to MySQL
Posted by: Vips Ch
Date: March 17, 2015 05:10AM

I am not able to migrate this Oracle Procedure to MYSQL db. Can anyone help me out to migrate this to MySql db.
Below is the oracle procedure to be migrated:

PROCEDURE INFO_UPDATE(vUEI IN VARCHAR2, vOLD_NAME IN VARCHAR2,
vOLD_PREMISES IN VARCHAR2, vOLD_STREET IN VARCHAR2,
vOLD_LOCALITY IN VARCHAR2, vOLD_POST_CODE IN VARCHAR2, vOLD_COUNTRY IN VARCHAR2,
vOLD_ACTIVATION IN VARCHAR2)
IS
vDelFlag NUMBER := 2;
BEGIN

MERGE INTO PENDING_UPDATES_INFO tgt
USING
(
SELECT
trim(ns.PREFIX||ns.Num) AS NUM
,ns.COUNTRY_CODE AS COUNTRY_CODE
,NVL2(ns.OWNER, CASE SUBSTR(ns.OWNER,0,3) WHEN 'sub' THEN si.ID ELSE ns.OWNER END,si.ID) AS UEI
,NVL(vOLD_NAME,SUBSTR(trim(cu.NAME),0,50)) AS oLAST_NAME
,NVL(vOLD_PREMISES, SUBSTR(trim(saddr.PREMISES),0,60)) AS oPREMISES
,NVL(vOLD_STREET, SUBSTR(trim(saddr.STREET),0,55)) AS oSTREET
,NVL(vOLD_LOCALITY, SUBSTR(trim(saddr.TOWN),0,30)) AS oLOCALITY
,NVL(vOLD_POST_CODE, SUBSTR(trim(saddr.POST_CODE),0,9)) AS oPOST_CODE
,NVL(vOLD_COUNTRY, SUBSTR(trim(saddr.COUNTRY),0,50)) AS oCOUNTRY
,NVL(vOLD_ACTIVATION, ps.ACTIVATION_STATUS_VAL) AS oACTIVATION
,SUBSTR(trim(cu.NAME),0,50) AS LAST_NAME
,SUBSTR(trim(saddr.PREMISES),0,60) AS PREMISES
,SUBSTR(trim(saddr.STREET),0,55) AS STREET
,SUBSTR(trim(saddr.TOWN),0,30) AS LOCALITY
,SUBSTR(trim(saddr.POST_CODE),0,9) AS POST_CODE
,SUBSTR(trim(saddr.COUNTRY),0,50) AS COUNTRY
,ps.ACTIVATION_STATUS_VAL AS ACTIVATION
,NULL AS FLAGS
FROM CUSTOMER cu
join SITE si ON si.PARENT_ID = cu.ID
join NUM_RANGES nr ON nr.SITE_ID = si.ID
join NUMBERS ns ON ns.RANGE_ID = nr.RANGE_ID
join ADDRESSES saddr ON si.ID = saddr.UEI
join PROVISION_SETTING ps ON ps.UEI = si.ID
WHERE (cu.ID = vUEI OR si.ID = vUEI)
AND ns.NUMBER_CLASS in ( 9, 7, 8)
) src
ON ( tgt.NUM = src.NUM AND tgt.COUNTRY_CODE = src.COUNTRY_CODE AND LOCKED = 0 AND bitand(tgt.FLAGS,vDelFlag) = 0)
WHEN MATCHED THEN UPDATE SET
UEI = src.UEI
,NEW_LAST_NAME = src.LAST_NAME
,NEW_PREMISES = src.PREMISES
,NEW_STREET = src.STREET
,NEW_LOCALITY = src.LOCALITY
,NEW_POST_CODE = src.POST_CODE
,NEW_COUNTRY = src.COUNTRY
,NEW_ACTIVATION = src.ACTIVATION
WHEN NOT MATCHED
THEN INSERT
(NUM, COUNTRY_CODE, UEI,
OLD_LAST_NAME, OLD_PREMISES, OLD_STREET, OLD_LOCALITY, OLD_POST_CODE, OLD_COUNTRY, OLD_ACTIVATION,
NEW_LAST_NAME, NEW_PREMISES, NEW_STREET, NEW_LOCALITY, NEW_POST_CODE, NEW_COUNTRY, NEW_ACTIVATION,
FLAGS, LOCKED
)
VALUES
( src.NUM
,src.COUNTRY_CODE
,src.UEI
,src.oLAST_NAME
,src.oPREMISES
,src.oSTREET
,src.oLOCALITY
,src.oPOST_CODE
,src.oCOUNTRY
,src.oACTIVATION
,src.LAST_NAME
,src.PREMISES
,src.STREET
,src.LOCALITY
,src.POST_CODE
,src.COUNTRY
,src.ACTIVATION
,0
,0
);
END INFO_UPDATE;

Thanks in advance

Options: ReplyQuote


Subject
Views
Written By
Posted
Migrating Oracle Procedure to MySQL
3630
March 17, 2015 05:10AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.