Re: Deferrable constraints in stored procedures

Keith Jamieson wrote:

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

DBMS_SQL or Native Dynamic SQL. Either will work.

But SET does not require anything other than just typing it into the procedure body.

Daniel A. Morgan Received on Thu Jun 07 2001 - 18:39:41 CDT