Re: Deferred constraint question

From: Njål Ekern <n.a.ekern_at_usit.uio.no>
Date: Fri, 25 Jun 1999 02:09:28 +0200
Message-ID: <3772C8B8.281D6AEB@usit.uio.no>

Jim Jenkinson wrote:
>
> That was my thought as well, but, although the statement parses and executes
> without error, the constraint remains in immediate mode.
>
> Thank you for your response.

We use this approach with success.

In a package body (not in the specification) we have an exec-ddl-procedure P_DBMSexec. In the specification we have one procedure for deferring a constraint, one for setting it immediate, and also many other ddl-procedures.

The language_flag in the exec-ddl procedure is dbms_sql.native, not v7

Maybe you have an exception-handler which catches the error and don't raise it again, like this:

   exception

      when others then
         if dbms_sql.is_open(exec_cursor) then
            dbms_sql.close_cursor(exec_cursor);
         end if;
      raise;  --> Do you have this?

   end P_dbmsexec;

Njål

>
> Jonathan Lewis wrote in message

.
.
> > n := dbms_sql.open_cursor;
> > dbms_sql.parse(
> > c =>n,
> > statement =>'set constraint ' || i_constraint || ' deferred' ,
> > language_flag => dbms_sql.v7
> > );
> > v_junk := dbms_sql.execute (n);
Received on Thu Jun 24 1999 - 19:09:28 CDT