Hi,
I have a scenario where I need to suppress the ORA message and raise custom message.
DROP TABLE ACCESS_OP_CONS_TEMP3;
BEGIN
EXECUTE IMMEDIATE
'CREATE GLOBAL TEMPORARY TABLE ACCESS_OP_CONS_TEMP3
(
CONSTRAINT_ID NUMBER NOT NULL,
ENTITY_NAME VARCHAR2(30) NOT NULL,
CHECK_CONSTRAINT VARCHAR2(4000) NOT NULL,
VERSION_ID NUMBER,
PAR_NUM1 NUMBER,
PAR_NUM2 NUMBER,
PAR_NUM3 NUMBER,
PAR_STR1 VARCHAR2(4000),
PAR_STR2 VARCHAR2(4000),
PAR_STR3 VARCHAR2(4000),
VERSION_VALID_FROM DATE,
IDENTITY_START_DATE DATE,
IDENTITY_TERMINATION_DATE DATE,
MODIF_ACTION VARCHAR2(10)
)
ON COMMIT DELETE ROWS
NOCACHE
';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE NOT IN (-955) THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE
'CREATE UNIQUE INDEX ACCESS_OP_CONS_TEMP3_PK ON ACCESS_OP_CONS_TEMP3 (
CONSTRAINT_ID
)
';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE NOT IN (-955) THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE
'CREATE INDEX ACCESS_OP_CONS_TEMP3_I_ET ON ACCESS_OP_CONS_TEMP3
(ENTITY_NAME)
';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE NOT IN (-955) THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE
'CREATE INDEX ACCESS_OP_CONS_TEMP3_I_CC ON ACCESS_OP_CONS_TEMP3
(CHECK_CONSTRAINT)
';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE NOT IN (-955) THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE
'ALTER TABLE ACCESS_OP_CONS_TEMP3 ADD (
CONSTRAINT ACCESS_OP_CONS_TEMP3_PK
PRIMARY KEY
(CONSTRAINT_ID)
USING INDEX ACCESS_OP_CONS_TEMP3_PK)
';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE NOT IN (-2260) THEN
RAISE;
END IF;
END;
/
DROP MATERIALIZED VIEW MV_ACCESS_OP_CONS3;
BEGIN
EXECUTE IMMEDIATE
'CREATE MATERIALIZED VIEW MV_ACCESS_OP_CONS3
BUILD IMMEDIATE
USING INDEX
REFRESH COMPLETE
ON COMMIT
WITH PRIMARY KEY
AS
SELECT
a.*
FROM ACCESS_OP_CONS_TEMP3 a
';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE NOT IN (-12006) THEN
RAISE;
END IF;
END;
/
CREATE OR REPLACE PROCEDURE MV_PROC IS
BEGIN
RAISE_APPLICATION_ERROR(-20299, 'MV Dummy Error');
END;
/
CREATE OR REPLACE TRIGGER MV_ACCESS_OP_CONS3_TRG
BEFORE INSERT ON MV_ACCESS_OP_CONS3
BEGIN
mv_proc;
END;
/
------------------------------------------------Now Run the below proc-----------------
BEGIN
INSERT INTO ACCESS_OP_CONS_TEMP3(CONSTRAINT_ID,ENTITY_NAME,CHECK_CONSTRAINT)
VALUES (1234,'TEST','CHK');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
/
ORA-12008: error in materialized view refresh path
ORA-20299: MV Dummy Error
ORA-06512: at "HECR.MV_PROC", line 3
ORA-06512: at "HECR.MV_ACCESS_OP_CONS3_TRG", line 2
ORA-04088: error during execution of trigger 'HECR.MV_ACCESS_OP_CONS3_TRG'
What I need is that I should not get the first line "ORA-12008: error in materialized view refresh path".
Is there a way to achieve this?
Regards,
Vikram R