On Mon, Nov 23, 2020 at 6:52 PM Jagmohan Kaintura <jagmohan@xxxxxxxxxxxxxx> wrote:
It doesn't works putting that block inside additional BEGIN ENDCREATE 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.
