From: stevenyberg@hotmail.com (Steve Nyberg)
Newsgroups: comp.databases.oracle.misc
Subject: Deferrable constraints in stored procedures
Date: 6 Jun 2001 14:06:30 -0700
Organization: http://groups.google.com/
Lines: 14
Message-ID: <9d650a6d.0106061306.29f7fa36@posting.google.com>
NNTP-Posting-Host: 12.40.177.5
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 991861591 26204 127.0.0.1 (6 Jun 2001 21:06:31 GMT)
X-Complaints-To: groups-support@google.com
NNTP-Posting-Date: 6 Jun 2001 21:06:31 GMT


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;