Problem solve Get help with specific problems with your technologies, process and projects.

Enabling constraints dynamically using invoker's right

Enable all disabled constaints for any schema in the database using EXECUTE IMMEDIATE and the invoker's right option in PL/SQL.

This is a very simple script I developed to enable all disabled constaints for any schema in the database using EXECUTE IMMEDIATE (for dynamic execution of the DDL statement) and also the invoker's right option in PL/SQL. This means that execution is based on invoker right; all that is required is for the owner of the object to grant an execution privilege on the object and also create a public synonym on that object, which is optional. I developed this script because frankly, I don't know if Oracle has an inbuilt package to enable all constraints for a schema, something similar to dbms_utility.compile_schema for compiling all invalid objects. I hope you will find these useful.

  
CREATE OR REPLACE PROCEDURE pr_enable_constraint
AUTHID CURRENT_USER
 
/*
** AUTHOR : AKINODE BABATUNDE (OCP DBA)
**
**
** PURPOSE : DYNAMICALLY ENABLING CONSTRAINTS IN ANY SCHEMA
**
**
** DATE : 20-MAY-2003
**
**
*/

AS
 --
 CURSOR cr_db_objects IS
 SELECT constraint_name,table_name
 FROM all_constraints
 WHERE owner = USER
 AND status = 'DISABLED'
 ORDER BY DECODE(r_constraint_name,NULL,1,2);              
 --
 v_object_name VARCHAR2(40);
 v_object_tab VARCHAR2(40);
 v_exec_count  NUMBER := 0;
 --
 PROCEDURE pr_execute
 IS
  --
 v_sql_stmnt  VARCHAR2(300);
 --
 BEGIN
   --
  v_sql_stmnt := 'ALTER  TABLE  '||v_object_tab||' ENABLE CONSTRAINT  '||v_object_name; 
   EXECUTE IMMEDIATE v_sql_stmnt;
  --
 EXCEPTION
   WHEN OTHERS THEN
    NULL;
 END;
 --
BEGIN
  --
 WHILE v_exec_count <= 2
 LOOP
   --
    FOR cur IN cr_db_objects
    LOOP
     --
   v_object_name := cur.constraint_name;
   v_object_tab := cur.table_name;
      pr_execute;
     --
   END LOOP;
  --
  v_exec_count := v_exec_count + 1;
  --
  END LOOP;
 --
EXCEPTION
 WHEN NOT_LOGGED_ON THEN 
   RAISE_APPLICATION_ERROR (-20999 ,SQLERRM);
  --
  WHEN OTHERS THEN 
    RAISE_APPLICATION_ERROR (-20999, SQLERRM );
  --
END pr_enable_constraint;

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close