Anno Domini 17-3-2006 14:47, laredotornado_at_zipmail.com sprak aldus:
> 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
>
>
Use dbms_utility.exec_ddl_statement for that.
dbms_utility.exec_ddl_statement('ALTER TABLE SUPPORT.BUGTRACKER_DATA
disable CONSTRAINT BD_PROJECT_FK');
Received on Sat Mar 18 2006 - 04:17:50 CST
