Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Deferrable constraints in stored procedures

Re: Deferrable constraints in stored procedures

From: Keith Jamieson <Keith.Jamieson_at_phoenix.ie>
Date: Thu, 7 Jun 2001 17:00:45 +0100
Message-ID: <9fo862$nh9$1@kermit.esat.net>

I had this problem, and teh same sql issued by SQLPLUS did not work inside a PL/SQL procedure. This is because ALtering constraints is considered DDL. So, you have to put your command inside dbms_sql and all will be OKAY.

Daniel A. Morgan wrote in message <3B1F1742.175270A1_at_exesolutions.com>...
>Steve Nyberg wrote:
>
>> Is there a way to add SQL like the following, which works fine in SQL
>> Plus and Toad, to a stored procedure? This code is used for cascading
>> updates of PK/FK fields in multiple tables. My guess is the SET
>> CONSTRAINTS line is not valid in PL/SQL, even when I've added a SET
>> TRANSACTION READ WRITE before it. Is there a workaround to do this in
>> a procedure? Thanks in advance,
>>
>> Steve
>>
>> SET CONSTRAINTS ALL DEFERRED;
>> update route set routeareaid=0 where routenbr=888;
>> update subroute set routeareaid=0 where routenbr=888;
>> COMMIT;
>
>I have used syntax similar to yours in procedures. Did you try it?
>
>SET is perfectly valid in PL/SQL.
>
>Daniel A. Morgan
>
Received on Thu Jun 07 2001 - 11:00:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US