Print Thread
Question about deferred constraints
#28780 12/07/07 04:06 PM
Joined: Oct 2005
Posts: 587
Belgium
Roeland Offline OP
Member
OP Offline
Member
Joined: Oct 2005
Posts: 587
Belgium
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


Roeland
Re: Question about deferred constraints
#28781 12/07/07 04:49 PM
Joined: Jul 2004
Posts: 281
The Netherlands
Member
Offline
Member
Joined: Jul 2004
Posts: 281
The Netherlands
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;


Been there, done that, Got the T-Shirt
Re: Question about deferred constraints
#28782 12/07/07 08:11 PM
Joined: Oct 2005
Posts: 587
Belgium
Roeland Offline OP
Member
OP Offline
Member
Joined: Oct 2005
Posts: 587
Belgium
Thanks for the quick answer.
That helped me a lot!

With kind regards,

Roeland


Roeland
Re: Question about deferred constraints
#28783 12/08/07 08:37 PM
Joined: Dec 2005
Posts: 2,004
Roima Denmark
Member
Offline
Member
Joined: Dec 2005
Posts: 2,004
Roima Denmark
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

Re: Question about deferred constraints
#28784 12/10/07 01:44 PM
Joined: Jul 2004
Posts: 281
The Netherlands
Member
Offline
Member
Joined: Jul 2004
Posts: 281
The Netherlands
@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.


Been there, done that, Got the T-Shirt
Re: Question about deferred constraints
Marco van der Linden #47252 06/10/13 09:02 AM
Joined: Jun 2013
Posts: 1
P
Member
Offline
Member
P
Joined: Jun 2013
Posts: 1
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.

Re: Question about deferred constraints
pimpollo #47254 06/10/13 10:03 AM
Joined: Oct 2005
Posts: 587
Belgium
Roeland Offline OP
Member
OP Offline
Member
Joined: Oct 2005
Posts: 587
Belgium
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 ;-)


Roeland

Moderated by  support 

Link Copied to Clipboard
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.1.33 Page Time: 0.065s Queries: 15 (0.033s) Memory: 2.5212 MB (Peak: 3.0394 MB) Data Comp: Off Server Time: 2024-03-28 20:29:32 UTC
Valid HTML 5 and Valid CSS