Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Deferrable constraints in stored procedures
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