How does ERRORMESSAGE work?

Discussion of open issues, suggestions and bugs regarding database management and development tools for Oracle
Post Reply
diegovis
Posts: 2
Joined: Fri 29 Nov 2013 17:48

How does ERRORMESSAGE work?

Post by diegovis » Fri 29 Nov 2013 17:57

Hi,
I'm not an Oracle db expert (usually I work with DB2), so could someone be so kind to explain me how does ERRORMESSAGE work in generated scripts?
E.g. in the script below the first block throws an error (duplicate 'insert'), so ERRORMESSAGE should be set but it seems to me that the sync-block #2 is excecuted too. I also tried to print ERRORMESSAGE at the end of script discovering it is empty... very strange for me.

Code: Select all

SET SQLBLANKLINES ON;
SET DEFINE OFF;
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/SYYYY HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'MM/DD/SYYYY HH24:MI:SS.FF TZH:TZM';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'MM/DD/SYYYY HH24:MI:SS.FF';
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';
ALTER SESSION SET NLS_NCHAR_CONV_EXCP = FALSE;
ALTER SESSION SET TIME_ZONE = '+01:00';
ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;
VARIABLE ERRORMESSAGE VARCHAR2;


-- Synchronization block #1
BEGIN
IF :ERRORMESSAGE IS NULL THEN
INSERT INTO MXM7S.WORKVIEW(WORKVIEWID) VALUES (1932467);
INSERT INTO MXM7S.WORKVIEW(WORKVIEWID) VALUES (1932467);
INSERT INTO MXM7S.WORKVIEW(WORKVIEWID) VALUES (1932000);
END IF;
EXCEPTION
  WHEN OTHERS THEN BEGIN
    ROLLBACK;
    :ERRORMESSAGE := CONCAT('An error occured in the synchronization block #1: ', SQLERRM);
  END;
END;
/

-- Synchronization block #2
BEGIN
IF :ERRORMESSAGE IS NULL THEN
INSERT INTO MXM7S.WORKVIEW(WORKVIEWID) VALUES (1932468);
INSERT INTO MXM7S.WORKVIEW(WORKVIEWID) VALUES (1932477);
END IF;
EXCEPTION
  WHEN OTHERS THEN BEGIN
    ROLLBACK;
    :ERRORMESSAGE := CONCAT('An error occured in the synchronization block #2: ', SQLERRM);
  END;
END;
/

BEGIN
  IF :ERRORMESSAGE IS NULL THEN
    COMMIT;
  END IF;
END;
/

BEGIN
  IF :ERRORMESSAGE IS NOT NULL THEN
    RAISE_APPLICATION_ERROR(-20202, :ERRORMESSAGE);
  END IF;
END;
/

alexa
Devart Team
Posts: 2424
Joined: Fri 24 Jun 2011 14:17

Re: How does ERRORMESSAGE work?

Post by alexa » Mon 02 Dec 2013 14:03

The information on the error that arises is written in the ERRORMESSAGE variable. Then, at the end, it gets checked on whether it contains an information (an error has happened) and, if so, the transaction gets rolled back. If there is no error in the ERRORMESSAGE variable, this means that there was no errors and the transaction gets committed.

diegovis
Posts: 2
Joined: Fri 29 Nov 2013 17:48

Re: How does ERRORMESSAGE work?

Post by diegovis » Wed 04 Dec 2013 21:51

Thanks for your explanation that confirms what I understood about script logic.
The problem is then about ERRORMESSAGE that is not set when exception arises. Could you be so kind to explain why this occurs?

alexa
Devart Team
Posts: 2424
Joined: Fri 24 Jun 2011 14:17

Re: How does ERRORMESSAGE work?

Post by alexa » Thu 05 Dec 2013 12:12

Could you please explain in details what exception arises and provide us any other information so we can reproduce it on our side?

You can send a reply straight to our support system at supportATdevartDOTcom or alexaATdevartDOTcom, so we will keep further correspondence with you via the e-mails.

Post Reply