Re: Deferrable constraints in stored procedures

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 08 Jun 2001 11:04:40 -0400
Message-ID: <u9q1it4prbian1fjqn30ncvno6468vno5g@4ax.com>

A copy of this was sent to "Daniel A. Morgan" <dmorgan_at_exesolutions.com> (if that email address didn't require changing) On Thu, 07 Jun 2001 16:39:41 -0700, you wrote:

>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

set constraints is not supported natively in plsql:

ops$tkyte_at_ORA8I.WORLD> begin
  2 set constraints all deferred;
  3 end;
  4 /
set constraints all deferred;

    *
ERROR at line 2:

ORA-06550: line 2, column 5:
PLS-00103: Encountered the symbol "CONSTRAINTS" when expecting one of the
following:

transaction

ops$tkyte_at_ORA8I.WORLD> set constraints all deferred;

Constraint set.

ops$tkyte_at_ORA8I.WORLD> begin
  2 execute immediate 'set constraints all deferred';   3 end;
  4 /

PL/SQL procedure successfully completed.

so the dynamic sql is in fact necessary.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Fri Jun 08 2001 - 10:04:40 CDT