Hi , The Block is only failing immediately at First COMMIT only. It's not supporting COMMIT. I have removed some portion of code before the second COMMIT.
On Mon, Nov 23, 2020 at 9:19 PM Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 11/23/20 12:36 AM, Jagmohan Kaintura wrote: > > Hi Team, > > > > We have many BATCH JOBS in Oracle which we are committing after > > processing few Records. These batch Jobs process in some subsets and > > call transaction control statements COMMIT in case of Success and > > ROLLBACK in case of failure. > > > > While converting to POstgreSQL we converted in Same Format with COMMIT > > and ROLLBACK. But while executing it ended up with below error message. > > ERROR: invalid transaction termination > > CONTEXT: PL/pgSQL function inline_code_block line 29 at COMMIT > > > > While reviewing the Transaction Management in PostgreSQL > > "https://www.postgresql.org/docs/12/plpgsql-transactions.html > > <https://www.postgresql.org/docs/12/plpgsql-transactions.html>" it > > speaks about a format which is not Supported. > > > > Transaction control is only possible in |CALL| or |DO| invocations from > > the top level or nested |CALL| or |DO| invocations without any other > > intervening command. For example, if the call stack is |CALL proc1()| → > > |CALL proc2()| → |CALL proc3()|, then the second and third procedures > > can perform transaction control actions. But if the call stack is |CALL > > proc1()| → |SELECT func2()| → |CALL proc3()|, then the last procedure > > cannot do transaction control, because of the |SELECT| in between. > > > > My Call has : CALL Batch Job => SELECT function Used in SQL Statements > > ==> Call Procedure. We have transaction control in "CALL Batch Job" > only. > > > > Pseudo Code is like : Highlighted in BOLD is a function call. It's > > failing when getting executed as we are using functions into this > procedure. > > *Can any help on this matter , how I can implement Batch Jobs as we > > wanted to commit in few intervals of 20000 records ?* > > We can't remove this function from the statement as its value is > > dependent on column value. > > > > CREATE OR REPLACE PROCEDURE TEST_TRANSACTION( > > ) > > LANGUAGE 'plpgsql' > > SECURITY DEFINER > > AS $BODY$ > > DECLARE > > G_LAST_UPDATE_USER_SYSTEM VARCHAR2(6) := 'SYSTEM'; > > G_LAST_UPDATE_MODULE_BATCH VARCHAR2(5) := 'BATCH'; > > > > G_CNTR_LOADING_EXPIRED TMS_CONTAINER_LOADING.STATUS_CODE%TYPE := > '90'; > > G_DG_MANIFEST_DELETED TMS_DG_MANIFEST.STATUS_CODE%TYPE := '80'; > > G_DG_MANIFEST_COMPLETED TMS_DG_MANIFEST.STATUS_CODE%TYPE := '95'; > > > > v_num_day numeric; > > v_batch_count numeric; > > v_log_count numeric := 0; > > v_local_batch_count numeric; > > BEGIN > > v_batch_count := 0; > > LOOP > > update tms_container_loading > > set status_code = G_CNTR_LOADING_EXPIRED > > , last_update_tm = clock_timestamp()::timestamp(0) > > , last_update_user_an = G_LAST_UPDATE_USER_SYSTEM > > , last_update_module_code = G_LAST_UPDATE_MODULE_BATCH > > where > > > *tms$vvd.is_vessel_departed(vessel_visit_c,trunc(clock_timestamp()::timestamp(0)))* > > > = 1 > > and coalesce(status_code,'~') <> G_CNTR_LOADING_EXPIRED > > and ctid in (select a.ctid from tms_container_loading where > > > *tms$vvd.is_vessel_departed(vessel_visit_c,trunc(clock_timestamp()::timestamp(0)))* > > > = 1 > > and coalesce(status_code,'~') <> G_CNTR_LOADING_EXPIRED > > LIMIT 20000); > > EXIT WHEN NOT FOUND; /* apply on SQL */ > > GET DIAGNOSTICS v_local_batch_count = ROW_COUNT; > > v_batch_count := v_batch_count + v_local_batch_count; > > COMMIT; > > END LOOP; > > v_log_count := v_log_count + 1; CALL > > Log(v_batch_count,'TMS_CONTAINER_LOADING',NULL, 'TMS$BATCH_JOB', > > v_log_count); > > COMMIT; > > END; > > $BODY$; > > I'm still trying to figure out transaction management in procedures, so > bear with me. Not sure what the purpose of the second COMMIT is? Also > wonder if it is no the cause of the issue? > > > > -- > > *Best Regards,* > > Jagmohan > > Senior Consultant, TecoreLabs. > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- *Best Regards,* Jagmohan Senior Consultant, TecoreLabs.
