Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
COMMIT/ROLLBACK statement is not allowed in package, procedure or function
CREATE FUNCTION UPD_NEXT_EXPIRE_DT(in_info_group INTEGER, |
in_use_term NUMBER) RETURN NUMBER AS |
|
CURSOR sel_auto_client IS |
SELECT CLIENT_CD, EXPIRE_DT FROM SERVICE_COMMISSION_CHARGE |
WHERE INFO_GROUP = in_info_group AND USED_STS IN (1,2) AND |
FREE_EXTENSION_FLG = 1 AND EXPIRE_DT != '02-12-31'; |
|
|
out_trade_dt DATE; |
out_contract_dt DATE; |
out_expire_dt DATE; |
today_dt VARCHAR2(10);
|
out_upd_count NUMBER;
|
|
BEGIN
|
|
FOR S_REC IN sel_auto_client LOOP |
|
SELECT TO_CHAR(S_REC.EXPIRE_DT+1,'YYYYMMDD') INTO today_dt FROM DUAL; |
INFO_CHARGE_PKG.GET_CONTRACT_DT( in_info_group, in_info_group, today_dt,
|
600, out_trade_dt, out_contract_dt );
|
|
|
SELECT ADD_MONTHS(out_contract_dt, in_use_term)-1 INTO |
out_expire_dt FROM DUAL; |
|
UPDATE SERVICE_COMMISSION_CHARGE SET NEXT_EXPIRE_DT = out_expire_dt, |
LST_UPD_DT = SYSDATE
|
WHERE INFO_GROUP = in_info_group AND CLIENT_CD = S_REC.CLIENT_CD; |
|
|
out_upd_count := sel_auto_client%ROWCOUNT;
|
|
END LOOP; |
COMMIT; |
|
RETURN out_upd_count; |
|
EXCEPTION
|
|
WHEN OTHERS THEN |
ROLLBACK; |
raise_application_error(-20201,SQLERRM);
|
|
END UPD_NEXT_EXPIRE_DT; |
|
|
/
|
errors with:
ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger