Programmers Blog

I found this very helpful procedure http://www.orafaq.com/node/2024. But it does not work when copy-pased. Here you can download the compile-ready for Oracle 10g version of this procedure. disable_constraints.sql

CREATE OR REPLACE PROCEDURE MANAGE_USER_FK_PK_UK(
    OPERATION VARCHAR2 ,
    FK        BOOLEAN DEFAULT TRUE,
    PK        BOOLEAN DEFAULT TRUE,
    UK        BOOLEAN DEFAULT TRUE)
IS
  ST VARCHAR2(255);
  CURSOR R
  IS
    SELECT TABLE_NAME,
      CONSTRAINT_NAME
    FROM USER_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'R';
  CURSOR P
  IS
    SELECT TABLE_NAME,
      CONSTRAINT_NAME
    FROM USER_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'P';
  CURSOR U
  IS
    SELECT TABLE_NAME,
      CONSTRAINT_NAME
    FROM USER_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'U';
  
  E R%ROWTYPE;
  
BEGIN
  IF UPPER(OPERATION) IN ('DROP','DISABLE') THEN
    IF FK THEN
      BEGIN
        FOR E IN R
        LOOP
          ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;
          EXECUTE IMMEDIATE(ST);
          DBMS_OUTPUT.PUT_LINE(ST);
        END LOOP;
      END;
    END IF;
    IF PK THEN
      BEGIN
        FOR E IN R
        LOOP
          ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;
          EXECUTE IMMEDIATE(ST);
          DBMS_OUTPUT.PUT_LINE(ST);
        END LOOP;
      END;
      BEGIN
        FOR E IN P
        LOOP
          ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;
          EXECUTE IMMEDIATE(ST);
          DBMS_OUTPUT.PUT_LINE(ST);
        END LOOP;
      END;
    END IF;
    IF UK THEN
      BEGIN
        FOR E IN U
        LOOP
          ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;
          EXECUTE IMMEDIATE(ST);
          DBMS_OUTPUT.PUT_LINE(ST);
        END LOOP;
      END;
    END IF;
  ELSIF UPPER(OPERATION) IN ('ENABLE') THEN
    IF PK THEN
      BEGIN
        FOR E IN P
        LOOP
          ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;
          EXECUTE IMMEDIATE(ST);
          DBMS_OUTPUT.PUT_LINE(ST);
        END LOOP;
      END;
    END IF;
    IF FK THEN
      BEGIN
        FOR E IN P
        LOOP
          ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;
          EXECUTE IMMEDIATE(ST);
          DBMS_OUTPUT.PUT_LINE(ST);
        END LOOP;
      END;
      BEGIN
        FOR E IN R
        LOOP
          ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;
          EXECUTE IMMEDIATE(ST);
          DBMS_OUTPUT.PUT_LINE(ST);
        END LOOP;
      END;
    END IF;
    IF UK THEN
      BEGIN
        FOR E IN U
        LOOP
          ST := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || OPERATION || ' CONSTRAINT ' || E.CONSTRAINT_NAME;
          EXECUTE IMMEDIATE(ST);
          DBMS_OUTPUT.PUT_LINE(ST);
        END LOOP;
      END;
    END IF;
  ELSE
    DBMS_OUTPUT.PUT_LINE('the first parameter of the procedure must be DROP or ENABLE or DISABLE');
  END IF;
END;

This code will look through all tables in the current schema and find a column in a table that has a row with the specified value “XYZ”. The column name must include the strings “ID” and “OFFER”.

DECLARE
  CURSOR all_columns
  IS
     SELECT table_name,
      column_name
       FROM user_tab_cols
      WHERE upper(column_name) LIKE '%ID%'
    AND upper(column_name) LIKE '%OFFER%';
  
  wartosc  VARCHAR(200);
  sql_stmt VARCHAR(200);
BEGIN
  FOR t IN all_columns
  LOOP
    sql_stmt := 'begin select ' || t.column_name || ' into :a from ' || t.TABLE_NAME || ' where ' || t.column_name || ' = ''XYZ''; end;';
    --DBMS_OUTPUT.PUT_LINE(sql_stmt);
    BEGIN
      EXECUTE IMMEDIATE sql_stmt USING OUT wartosc;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      GOTO next_one;
      -- DBMS_OUTPUT.PUT_LINE('Table: ' || t.TABLE_NAME || ' Column: ' || t.column_name || ' NO DATA');
    END;
    DBMS_OUTPUT.PUT_LINE('FOUND-> Table: ' || t.TABLE_NAME || ' Column: ' || t.column_name);
    <<next_one>> NULL;
  END LOOP;
END;

Sample dynamic SQL using PL/SQL. I had some problems constructing dynamic SQL but I finnaly got it working. In case somebody has a use of it I put it here. The code finds all NUMBER columns from the current user tables.

Things worth a notice:

• when using into keyword in the dynamic SQL we have to use begin and end keywords since into is PL/SQL, not SQL
• EXECUTE IMMEDIATE can use IN and OUT parameters

The code:

DECLARE

 CURSOR all_my_tables IS
SELECT TABLE_NAME,
  column_name,
  data_precision
FROM user_tab_columns
WHERE data_type LIKE 'NUMBER%';

max_col_val NUMBER(20);
sql_stmt VARCHAR(200);

BEGIN

  FOR t IN all_my_tables
  LOOP
    sql_stmt := 'begin select max(' || t.column_name || ') into :a from ' 
|| t.TABLE_NAME || '; end;';

    EXECUTE IMMEDIATE sql_stmt USING OUT max_col_val;

    DBMS_OUTPUT.PUT_LINE('Table: ' || t.TABLE_NAME || ' Column: ' 
|| t.column_name || ' type: NUMBER(' || t.data_precision || ') max: ' 
|| max_col_val);

  END LOOP;

END;