Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27504

sql_mode="oracle" cannot commit or rollback in stored code

    XMLWordPrintable

    Details

      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

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            manjot Manjot Singh
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.