How does ERRORMESSAGE work?
Posted: 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.
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;
/