Converted a Standalone Procedure from Oracle to Postgres but not sure why there is an run error even the code is successfully compiled
Converted the below code from Oracle to Postgres
CREATE OR REPLACE FUNCTION ssp2_pcat.pop_hoa_contracts_for_prod(
)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
DECLARE
C1 CURSOR for
SELECT MARKET_CODE, CONTRACT_COMBO_ID, COUNT(*) FROM
ssp2_pcat.VPF_HOA_CONTRACTS_FOR_PROD A
WHERE start_Date IN
(SELECT MAX(start_date)
FROM VPF_HOA_CONTRACTS_FOR_PROD b
WHERE A.MARKET_CODE = b.MARKET_CODE
AND A.CONTRACT_COMBO_ID = b.CONTRACT_COMBO_ID
AND A.CONTRACT_ID = B.CONTRACT_ID
AND b.start_date <= current_date
AND b.end_date > current_date )
GROUP BY MARKET_CODE, CONTRACT_COMBO_ID
ORDER BY MARKET_CODE, CONTRACT_COMBO_ID;
C2 CURSOR(iMktCode VARCHAR, iCombo integer) for
SELECT MARKET_CODE, CONTRACT_COMBO_ID, CONTRACT_ID
FROM ssp2_pcat.VPF_HOA_CONTRACTS_FOR_PROD A
WHERE start_Date IN
(SELECT MAX(start_date)
FROM ssp2_pcat.VPF_HOA_CONTRACTS_FOR_PROD b
WHERE A.MARKET_CODE = b.MARKET_CODE
AND A.CONTRACT_COMBO_ID = b.CONTRACT_COMBO_ID
AND A.CONTRACT_ID = B.CONTRACT_ID
AND b.start_date <= current_date
AND b.end_date > current_date )
AND MARKET_CODE = iMktCode
AND CONTRACT_COMBO_ID = iCombo
ORDER BY MARKET_CODE, CONTRACT_COMBO_ID, START_DATE;
Contracts VARCHAR(32000);
Contract_Val1 VARCHAR(4000) := NULL;
Contract_Val2 VARCHAR(4000) := NULL;
Contract_Val3 VARCHAR(4000) := NULL;
Contract_Val4 VARCHAR(4000) := NULL;
Contract_Val5 VARCHAR(4000) := NULL;
Contract_Val6 VARCHAR(4000) := NULL;
Contract_Val7 VARCHAR(4000) := NULL;
Contract_Val8 VARCHAR(4000) := NULL;
Num INTEGER;
Cont_Num INTEGER;
l_start TIMESTAMP := clock_timestamp();
l_end TIMESTAMP := clock_timestamp();
Time_Taken VARCHAR(20);
i record;
j record;
BEGIN
l_start := clock_timestamp();
DELETE FROM ssp2_pcat.HOA_CONTRACTS_KH;
FOR i IN C1 LOOP
BEGIN
Num := 0;
Contracts := NULL;
Cont_Num := 1;
FOR j IN C2 (i.MARKET_CODE, i.CONTRACT_COMBO_ID) LOOP
Num := Num + 1;
IF Num = 1 THEN
Contracts := '|' || j.CONTRACT_ID || '|';
ELSE
IF LENGTH(Contracts || j.CONTRACT_ID || '|') > 4000 THEN
PERFORM ssp2_pcat.Assign (Cont_Num, SUBSTRING(Contracts, 1,
LENGTH(Contracts)-1));
Num := 1;
Contracts := '|' || j.CONTRACT_ID || '|';
Cont_Num := Cont_Num + 1;
ELSE
Contracts := Contracts || j.CONTRACT_ID || '|';
END IF;
END IF;
END LOOP;
PERFORM ssp2_pcat.Assign (Cont_Num, Contracts);
IF Cont_Num > 5 THEN
raise notice'%', ('MARKET_CODE: ' || i.MARKET_CODE || ', CONTRACT_COMBO_ID: ' || i.CONTRACT_COMBO_ID || ' has more than 32K in size. These Contracts are left out: ' || Contracts);
END IF;
INSERT INTO HOA_CONTRACTS_KH
(
MARKET_CODE,
CONTRACT_COMBO_ID,
CONTRACT_ID,
CONTRACT_ID2,
CONTRACT_ID3,
CONTRACT_ID4,
CONTRACT_ID5,
LAST_UPDATED
)
VALUES
(
i.MARKET_CODE,
i.CONTRACT_COMBO_ID,
Contract_Val1,
Contract_Val2,
Contract_Val3,
Contract_Val4,
Contract_Val5,
CURRENT_TIMESTAMP::TIMESTAMP(0)
);
Contract_Val1 := NULL;
Contract_Val2 := NULL;
Contract_Val3 := NULL;
Contract_Val4 := NULL;
Contract_Val5 := NULL;
Contract_Val6 := NULL;
Contract_Val7 := NULL;
Contract_Val8 := NULL;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
raise notice'%', ('1) POP_HOA_CONTRACTS_FOR_PROD: ' || SQLERRM);
END;
END LOOP;
RAISE NOTICE 'Function excution time Took: %', l_start;
RAISE NOTICE 'Function excution time Took: %',l_end-l_start;
SELECT l_end-l_start INTO Time_Taken;
raise notice'%',('POP_HOA_CONTRACTS_FOR_PROD Took: ' || Time_Taken );
EXCEPTION
WHEN OTHERS THEN
raise notice'%', ('2) POP_HOA_CONTRACTS_FOR_PROD: ' || SQLERRM);
END;
$BODY$;
The code is compiled successfully, but giving a run time error as follows,
NOTICE: 2) POP_HOA_CONTRACTS_FOR_PROD: cannot begin/end transactions in PL/pgSQL
Debugged the whole code and looks like still I'm unable to identify the issue, can any one help me in making me understand more about Postgres as I'm new to this Database. Found out in unit testing that its not calling the assign function mentioned in the code,
