Finding the total number of constraints on all tables of the database

I have written a dynamic query (cursor) for finding the total number of constraints for each constraint type on all tables of the database. Here I am submitting the cursor for your reference. It is not working.

 declare
     totcons  number(12);
     tabname  varchar2(30);
     constype varchar2(1);
     str      varchar2(200);
     cursor test_ROW is
            select table_name,constraint_type  from 
 dba_constraints;
  BEGIN
     OPEN TEST_ROW;
     FETCH TEST_ROW INTO TABNAME,constype;
     LOOP
       exit when test_row %notfound;
       str := 'select count( * ) from dba_constraints
 
      where table_NAME = ' || TABNAME ||
      ' AND CONSTRAINT_TYPE = '||  CONSTYPE  ;
       DBMS_OUTPUT.PUT_LINE( STR ) ;
       execute immediate str into totcons;
      DBMS_OUTPUT.PUT_LINE( TO_CHAR(TOTCONS) ) ;
       insert into tab_cons  values 
 (tabname,constype,totcons);
       FETCH TEST_ROW INTO TABNAME,constype;
     END LOOP;
     CLOSE TEST_ROW;
  END;

This can be done much more easily with straight SQL. Try the following query:

SELECT constraint_type, count(*) AS num_constraints
FROM dba_constraints
GROUP BY constraint_type;

For More Information

• What do you think about this answer? E-mail the editors at [email protected] with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.