Possible to check whether a COMMIT will fail?

From: Randi W <randiwolner_at_hotmail.com>
Date: Tue, 16 Aug 2005 14:57:48 +0200
Message-ID: <ddsnsc$he9$1@services.kq.no>

Oracle v. 8.1.7:
I have a situation where some constraints are set to INITIALLY DEFERRED, as data within the transaction may come in an 'unpredictable' order. Is it possible to check whether a COMMIT will fail or succeed without actually calling COMMIT?

I know the command SET CONSTRAINTS ALL IMMEDIATE, but I need to find out what the result will be using a function or query that can be run with standard SQL. I have tried to write a function:

create or replace function ConstraintsOK return number is Result number;
begin
EXECUTE IMMEDIATE ('SET CONSTRAINTS ALL IMMEDIATE'); Result:=SQLCODE;
return(Result);
end ConstraintsOK;

This is compiled without errors, but when trying to call it I get the following error messages:

ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
ORA-06512: at "DUF.CONSTRAINTSOK", line 4
ORA-06512: at line 1

Is there any way to perform the test that I want?

Thanks,
Randi W Received on Tue Aug 16 2005 - 07:57:48 CDT