Question about deferred constraints - Allround Automations forums

Hi,

I have placed an unique contraint on a table and made it DEFERRABLE (that is initially immediate).

Now I'm writing a package where I want to make this constraint "Deferred".

So I write the following inside a procedure before the DML code:

[quote]PL/SQL: ORA-00922: missing or invalid option
[/quote]What am I doing wrong? Can't you change this constraint inside a package?

Any help is very much appreciated.

Thank you,

Roeland

You can't issue DDL inside PL/SQL try the following

Code

procedure my_proc
is
begin
  execute immediate 'SET CONSTRAINT MY_UNIQUE_CONSTRAINT DEFERRED';
  ..
  <dml code goes here>
end;

Thanks for the quick answer.
That helped me a lot!

With kind regards,

Roeland

Please note, executing DDL (altering tables, triggers, constraints etc.) always commits. Using DDL inside PL/SQL should be used with caution to avoid unwanted commits.

Best regards
Claus Pedersen

@Claus: You're right didn't think about that, but if you 'hide' the ddl statement in an autonomous transaction you won't be troubled by the implicit commit caused by the ddl.

Hi,

SET CONSTRAINT is not DDL, it is a Transaction Control Statement, as seen in Oracle docs.

Take a look at http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_1001.htm#i2099286.

It does not imply a commit, so there is no need for an autonomous transaction.

Anyway, you still need the "EXECUTE IMMEDIATE" construction.

Hi Pimpollo,

Thanks for correcting this and for enhancing my knowledge on this subject. It may come handy in the future.

Nice to see such an old post again ;-)