How can I find out all constraints from an given table in PL/SQL?

Question

I have a procedure witch takes a table as parameter and i must write into an sql file all details from that table like column names , type and constraints.Here is what I tried(This is a test code just to find out how to print all the info):

v_cursor_id  := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cursor_id , 'SELECT * FROM marks', DBMS_SQL.NATIVE);
  v_ok := DBMS_SQL.EXECUTE(v_cursor_id );
  DBMS_SQL.DESCRIBE_COLUMNS(v_cursor_id, v_total_coloane, v_rec_tab);

  v_nr_col := v_rec_tab.first;
  IF (v_nr_col IS NOT NULL) THEN
    LOOP
      DBMS_OUTPUT.PUT(v_rec_tab(v_nr_col).col_name);
      DBMS_OUTPUT.PUT(' ');
      v_tipColoana :=(v_rec_tab(v_nr_col).col_type);
      case v_tipColoana
           when 1 then  DBMS_OUTPUT.PUT('varchar2');
           when 2 then DBMS_OUTPUT.PUT('Number');
           when 12 then DBMS_OUTPUT.PUT('date');
           when 96 then DBMS_OUTPUT.PUT('CHAR');
      end case;
      EXIT WHEN (v_nr_col IS NULL);
    END LOOP;
  END IF;
  DBMS_SQL.CLOSE_CURSOR(v_cursor_id);

score 0 · Answer 1 · answered May 13 '18 at 18:31

You can just look at the Oracle Metadata table ALL_CONSTRAINTS. See details here: https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1037.htm#i1576022

You should be able to filter by TABLE_NAME to look at all the constraints for a table including their type.

I am not sure what you want to put in the SQL file but if you need to get the DDL of each constraint look at ALL_SOURCE or even dbms_metadata.get_ddl().

How can I find out all constraints from an given table in PL/SQL?

1 Answers1