Re: Deferrable constraints in stored procedures

Thanks for the ideas everyone. I was able to get it to work in a procedure using dbms_sql like this:

cursor_name := DBMS_SQL.OPEN_CURSOR;
strsql := 'SET CONSTRAINTS ALL DEFERRED'; DBMS_SQL.PARSE(cursor_name, strsql, DBMS_SQL.native); ret := DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
update route set routeareaid=0 where routenbr=888; update subroute set routeareaid=0 where routenbr=888; COMMIT;

• Steve Nyberg

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<u9q1it4prbian1fjqn30ncvno6468vno5g_at_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.
Received on Fri Jun 08 2001 - 16:19:18 CDT