How to disable foreign key constraints in a stored procedure?

From: <laredotornado_at_zipmail.com>
Date: 17 Mar 2006 05:47:30 -0800
Message-ID: <1142603250.691987.299900@e56g2000cwe.googlegroups.com>

Hi,

Is there another way to do what I'm trying to do in Oracle 9i for Solaris? In a stored procedure, I want to disable some foreign key constraints, perform some actions, and then re-enable them. However, when trying to compile the package, I get the errors. The code is below that.

LINE/COL ERROR

---

---

57/2 PLS-00103: Encountered the symbol "ALTER" when expecting one of

         the following:
         begin case declare exit for goto if loop mod null pragma
         raise return select update while with <an identifier>


         <a double-quoted delimited-identifier> <a bind variable> <<

         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge


         <a single-quoted SQL string> pipe

         The symbol "lock was inserted before "ALTER" to continue.

57/46 PLS-00103: Encountered the symbol "CONSTRAINT" when expecting one

  PROCEDURE edit_project_name(

        p_old_project_name IN VARCHAR2,
        p_new_project_name IN VARCHAR2

  )
  AS
  BEGIN
        ALTER TABLE SUPPORT.BUGTRACKER_DATA disable CONSTRAINT BD_PROJECT_FK; -- line 57

        ALTER TABLE SUPPORT.BUGTRACKER_ASSIGNEES disable CONSTRAINT BC_ASSIGNED_TO_FK;
        ALTER TABLE SUPPORT.BUGTRACKER_MY_PROJECTS disable CONSTRAINT FK_BUGTRACKER_MY_PJTS;
        UPDATE SUPPORT.BUGTRACKER_DATA SET PROJECT = p_new_project_name WHERE PROJECT = p_old_project_name;

        UPDATE SUPPORT.BUGTRACKER_ASSIGNEES SET PROJECT = p_new_project_name WHERE PROJECT = p_old_project_name;

        UPDATE SUPPORT.BUGTRACKER_PROJECTS SET PROJECT =

p_new_project_name WHERE PROJECT = p_old_project_name;
        UPDATE SUPPORT.BUGTRACKER_MY_PROJECTS SET PROJECT =
p_new_project_name WHERE PROJECT = p_old_project_name;
        ALTER TABLE SUPPORT.BUGTRACKER_DATA ENABLE CONSTRAINT
BD_PROJECT_FK;
        ALTER TABLE SUPPORT.BUGTRACKER_ASSIGNEES ENABLE CONSTRAINT
BC_ASSIGNED_TO_FK;
        ALTER TABLE SUPPORT.BUGTRACKER_MY_PROJECTS ENABLE CONSTRAINT
FK_BUGTRACKER_MY_PJTS;
        COMMIT;
  EXCEPTION
        WHEN OTHERS THEN
                ROLLBACK;

  END; Your suggestions are apprecaited, - Dave Received on Fri Mar 17 2006 - 07:47:30 CST