On Mon, Nov 23, 2020 at 6:52 PM Jagmohan Kaintura <jagmo...@tecorelabs.com> wrote:
> It doesn't works putting that block inside additional BEGIN END > > 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 > BEGIN > This isn't what I suggested. How are you connecting to the database to call your TEST_TRANSACTION function? Some clients will issue BEGIN silently to allow rollback. If you have a BEGIN that is called before the function starts, then the function cannot call commit. The function must be called while NOT in a transaction already. >
