Question about deferred constraints

[Roeland]

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:

PL/SQL: ORA-00922: missing or invalid option

What am I doing wrong? Can't you change this constraint inside a package?

Any help is very much appreciated.

Thank you,

Roeland

 

[Marco van der Linden]

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

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

 

[Roeland]

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

With kind regards,

Roeland

 

[Claus Pedersen]

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

 

[Marco van der Linden]

@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.

 

[pimpollo]

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.

 

[Roeland]

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 ;-)