PL/SQL Help to check for specific values in specific tables of Particular schema

Question

I am using Oracle DB. I have Toad to execute my queries.

I have two columns

• typecd
• description

in the schema Product_BIS

I want a know is there is any stored procedure to retrive all tables in schema Product_BIS which has these columns Typecd and description. If so, I want to check whether Typecd ='11'and description='Nokia' is present.

How to find this? Is there any simply stored procedure to check this.Can Anyone help me out

Note:There are around 300 tables in the Schema Product_BIS. So manually checking is difficult

Answer 1

What you want is something which interrogates the data dictionary to find all the tables which have certain columns and then generate queries to find which of those tables have rows which have specific values in those columns. There is no Oracle built-in to do this.

The normal approach to do this is dynamic SQL. You say you don't have the privileges to create objects in the database. Well, okay then, just run an anonymous block.

This code can be run in SQL*Plus or any other IDE. Just make sure you have enabled SERVEROUTPUT.

Note the use of rownum = 1 in the assembled statement. This prevents the block hurling a TOO_MANY_ROWS exception if more than one record matches the criteria. This is acceptable, as the posted question only requires the program to assert the existence of one or more rows. If the actual requirement is different, then obviously you will need to amend the code. For instance if you need to display the number of matched records then select count(*) into a numeric variable and change the DBMS_OUTPUT statement accordingly.

declare
    v char(1);
begin
    for r in ( select table_name from all_tab_columns
                where owner = 'PRODUCT_BIS'
                and column_name = 'TYPECD'
                intersect
                select table_name from all_tab_columns
                where owner = 'PRODUCT_BIS'
                and column_name = 'DESCRIPTION' )
    loop
        begin
            execute immediate
                 'select null from '||r.table_name
                   ||' where typecd=''11'' and description = ''nokia'' and rownum = 1'
                 into v;
            dbms_output.put_line ( 'those values exist in '||r.table_name);
       exception
            when no_data_found then
                 dbms_output.put_line ( 'no occurrence of those values in '||r.table_name);
       end;
    end loop;
end;

This solution uses hard-coded values. I presume this is a one-off requirement, so it doesn't matter. However if is a script you'll want to run repeatedly for many different permutations of schema, column names and values then you should re-write this as a script which uses substitution variables.

Answer 2

May Be this Will Help u to Find the Specific value in Specific Schema

CREATE OR REPLACE FUNCTION FIND_IN_SCHEMA (VAL VARCHAR2)
   RETURN VARCHAR2
IS
   V_OLD_TABLE   USER_TAB_COLUMNS.TABLE_NAME%TYPE;
   V_WHERE       VARCHAR2 (4000);
   V_FIRST_COL   BOOLEAN                            := TRUE;

   TYPE RC IS REF CURSOR;

   C             RC;
   V_ROWID       VARCHAR2 (20);
BEGIN
   FOR R IN (SELECT   T.*
                 FROM USER_TAB_COLS T, USER_ALL_TABLES A
                WHERE T.TABLE_NAME = A.TABLE_NAME
                  AND T.DATA_TYPE LIKE '%CHAR%'
             ORDER BY T.TABLE_NAME)
   LOOP
      IF V_OLD_TABLE IS NULL
      THEN
         V_OLD_TABLE := R.TABLE_NAME;
      END IF;

      IF V_OLD_TABLE <> R.TABLE_NAME
      THEN
         V_FIRST_COL := TRUE;

         -- DBMS_OUTPUT.PUT_LINE('searching ' || V_OLD_TABLE);
         OPEN C
          FOR 'select rowid from "' || v_old_table || '" ' || V_WHERE;

         FETCH C
          INTO V_ROWID;

         LOOP
            EXIT WHEN C%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE ('  rowid: ' || V_ROWID || ' in '
                                  || V_OLD_TABLE
                                 );

            FETCH C
             INTO V_ROWID;
         END LOOP;

         V_OLD_TABLE := R.TABLE_NAME;
      END IF;

      IF V_FIRST_COL
      THEN
         V_WHERE := ' where ' || R.COLUMN_NAME || ' like ''%' || VAL || '%''';
         V_FIRST_COL := FALSE;
      ELSE
         V_WHERE :=
            V_WHERE || ' or ' || R.COLUMN_NAME || ' like ''%' || VAL || '%''';
      END IF;
   END LOOP;

   RETURN 'Success';
END;
/