Re: Multiple independent transaction handle by a stored procedure

From: victor <victor_at_POST.pb>
Date: 3 Nov 1998 16:51:59 GMT
Message-ID: <01be074a$33ae0710$6b14abcc@victor>

> My problem is that I have to do some update in my database. If I
encounter
> problems during my processing
> I want log these problems in a seperate table XX.
> If my updating process ends out with a rollback I don't want my logging
into
> table XX to be rolled back.

you can do this in one session
like this

CREATE OR REPLACE PACKAGE NBUI AS PROCEDURE INIT_ERR_VAR; TYPE NBU_ERR_MES_TYPE IS TABLE OF VARCHAR2(250) INDEX BY BINARY_INTEGER;

---

--×ÏÚ×ÒÁÝÁÅÔ ÏÛÉÂËÉ ÓÄÅÌÁÎÎÙÅ ÐÒÉ ÓÉÎÈÒÏÎÉÚÁÃÉÉ
--ÆÏÒÍÁÔ '0#ÏÛÉÂËÁ'
--ÐÏÓÌÅÄÎÑÑ ÏÛÉÂËÁ × ×ÉÄÅ '-1#ÏÛÉÂËÁ'
-- ÉÎÁÞÅ '-1##'
-----------

FUNCTION RET_CURR_ERR RETURN VARCHAR2;

---

---úáðéóáôø ÷ PL-SQL ôáâìéþëõ óìåäõàýõà ïûéâëõ
---'0#..' - îéþåçï îå úáîïóéôôóñ
---'-1#..' -úáîïóéôóñ ÷óñ óôòïëá
-- COMMIT îå äåìáåôóñ
----------------

PROCEDURE SET_NEXT_ERR (RET VARCHAR2) ; end;/

CREATE OR REPLACE PACKAGE BODY NBUI AS

MAX_ERR_NUM INTEGER  DEFAULT 0;      -- ëïì ïûéâïë ðòé úáçòõúëå
CURR_ERR_NUM INTEGER DEFAULT 0 ;     -- ôåë ïûéâëá

NBU_ERR_MES NBU_ERR_MES_TYPE;         --ôáâìéãá óïïâýåîéê ïâ ïûéâëå

PROCEDURE INIT_ERR_VAR IS
BEGIN
CURR_ERR_NUM:=0;
MAX_ERR_NUM:=0;
END;

---

PROCEDURE SET_NEXT_ERR (RET VARCHAR2) IS RT VARCHAR2(250);
BEGIN RT:=SUBSTR(RET,1,250);   NBU_ERR_MES(MAX_ERR_NUM):=RT;
  MAX_ERR_NUM:=MAX_ERR_NUM+1;  EXCEPTION WHEN OTHERS THEN
  --DBMS_OUTPUT.PUT_LINE(ERR.ErrorMessage);   NULL;
 END; ---
FUNCTION RET_CURR_ERR RETURN VARCHAR2 IS RT VARCHAR2(250);
RT1 VARCHAR2(255);
BEGIN
RT1:='-1##';

IF CURR_ERR_NUM<MAX_ERR_NUM-1 THEN

   RT:=NBU_ERR_MES(CURR_ERR_NUM);
   RT1:='0#'||RT;
ELSIF CURR_ERR_NUM=MAX_ERR_NUM-1 THEN
   RT:=NBU_ERR_MES(CURR_ERR_NUM);
   RT1:='-1#'||RT;
ELSE
   NULL ;
END IF; CURR_ERR_NUM:=CURR_ERR_NUM+1; RETURN RT1; EXCEPTION WHEN OTHERS THEN

• RT1 := ERR.ErrorMessage; RT1 := '-1#NBUI:' || RT1 ||'#'; RETURN RT1; END;

procedure myproc is
begin

      INIT_ERR_VAR; loop

   ......
  if "error " then

        SET_NEXT_ERR(message);
  end if;
end loop;
end;

end;
/

and after this in client you can read message with function RET_CURR_ERR (commit and rollback dont apply to this vars) Received on Tue Nov 03 1998 - 10:51:59 CST