pidm_quest.sql

DECLARE
  spid  VARCHAR2(10) := '&pidm';
  spcnt NUMBER;
  tbcnt NUMBER := 0;
  tnam  VARCHAR2(100);
  cnam  VARCHAR2(100);
  newqry  VARCHAR2(255);
  sprow SPRIDEN%ROWTYPE;

  CURSOR get_p_tbls IS
    SELECT a.TABLE_NAME, b.COLUMN_NAME
      FROM ALL_TAB_COMMENTS a, ALL_TAB_COLUMNS b
      WHERE a.TABLE_NAME = b.TABLE_NAME  AND UPPER(b.COLUMN_NAME) LIKE UPPER('%pidm%')
      AND a.TABLE_TYPE = 'TABLE' AND a.OWNER IN ('SATURN', 'FAISMGR') ORDER BY a.TABLE_NAME;
BEGIN
  DBMS_OUTPUT.ENABLE;

  EXECUTE IMMEDIATE 'SELECT * FROM SPRIDEN WHERE SPRIDEN_PIDM = '''||spid||''' and SPRIDEN_CHANGE_IND IS NULL' INTO sprow;

  DBMS_OUTPUT.PUT_LINE(sprow.spriden_last_name||', '||sprow.spriden_first_name||' ('||sprow.spriden_id||')');
  DBMS_OUTPUT.PUT_LINE('---');

  OPEN get_p_tbls;
  LOOP
    FETCH get_p_tbls INTO tnam, cnam;
    EXIT WHEN get_p_tbls%NOTFOUND;

    EXECUTE IMMEDIATE 'SELECT COUNT('||cnam||') FROM '||tnam||' WHERE '||cnam||' = '''||spid||'''' INTO spcnt;

    IF spcnt > 0 THEN
      DBMS_OUTPUT.PUT_LINE(tnam||': '||cnam||' ('||spcnt||')');
      tbcnt := tbcnt + 1;
    END IF;

  END LOOP;
  CLOSE get_p_tbls;

  DBMS_OUTPUT.PUT_LINE('---');
  DBMS_OUTPUT.PUT_LINE('PIDM is in '||tbcnt||' table(s).');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || '  SQLERRM=' || SQLERRM);
    RAISE;
END;