Oracle EXECUTE IMMEDIATE
The PL/SQL code creates dynamic SQL to check for data in all the database tables that have the column "PART_NUMBER" and data count of a specific part number. The dynamic SQL uses the bind variable i_val that gets assigned with the input value v_part during query execution. The output row count value gets assigned to the variable v_count.

Oracle EXECUTE IMMEDIATE
DECLARE
 CURSOR c_tab IS
SELECT
 q_tab.table_name,
 'SELECT COUNT(*) FROM '||q_tab.Owner||'.'||q_tab.table_name||' WHERE '||
  q_tab.column_name as s_sql
FROM
 (SELECT owner, table_name, column_name
  FROM  all_tab_columns
  WHERE  column_name = 'PART_NUMBER'
  AND   table_name NOT IN (Select View_Name From All_Views)
  AND   table_name IN (SELECT table_name FROM all_tables WHERE status = 'VALID')
 ) q_tab;

 v_count    NUMBER;
 v_sql      VARCHAR2(200);
 v_part     VARCHAR2(10) := '&i_part';

BEGIN
 FOR tab_rec IN c_tab LOOP
   v_sql := tab_rec.s_sql||'= :i_val';
   EXECUTE IMMEDIATE v_sql INTO v_count USING v_part;
   DBMS_OUTPUT.PUT_LINE(tab_rec.table_name||' ['||v_part||'] -> '||v_count);
 END LOOP;
END;
/

Oracle registered trademark of Oracle Corporation.

Last Revised On: November 08, 2013

  1263