Cacher is the code snippet organizer for pro developers

CREATE OR REPLACE PROCEDURE x_search_db_fields(
    p_search VARCHAR,
    p_type   VARCHAR)
  /*
  * This procedure will search a user's schema (all tables) for columns matching the user's input.
  *
  * ####### Please create the following table before you run this procedure:
  * create table x_search_db_results(result varchar2(256));
  *
  *
  * This table will contain the result of the procedure run so that you can view intermediate search results while the procedure is running.
  *
  * You pass two parameters to this procedure:
  *
  *
  * 1) Search string / number / date (REQUIRED)
  * 2) Search datatype (REQUIRED)
  *
  * Example:
  *
  * exec search_db('hello','VARCHAR2') -- will search for rows in all tables that have a VARCHAR2 column with "hello" as the data.
  * exec search_db('01-JAN-2008','DATE') -- will search for all rows in all tables that have a DATE column with the data '01-JAN-2008' in it.
  * exec search_db(1000,'NUMBER') -- will search for all rows in all tables that have a NUMBER column with the data 1000 in it.
  *
  *
  * Allowed data types: VARCHAR2, CHAR, DATE, NUMBER, FLOAT.
  *
  *
  *
  * ***************************************************************************************************************
  * WARNING!!!!! if you have a large schema be advised that the search can take anywhere from minutes to hours!
  * ***************************************************************************************************************
  */
IS
TYPE tab_name_arr IS VARRAY(10000) OF VARCHAR2(256);
v_tab_arr1 tab_name_arr;
/* ARRAY TO HOLD ALL TABLES IN THE USER SCHEMA */
v_col_arr1 tab_name_arr;
/* ARRAY TO HOLD ALL COLUMNS IN EACH TABLE */
v_amount_of_tables NUMBER(10);
/* this holds the amount of tables in the current user schema so that the for loop will know how many times to run */
v_amount_of_cols NUMBER(10);
/* when searching in a table, this holds the amount of columns in that table so that the for loop searching the table will know how many iterations it needs */
v_search_result NUMBER(10);
/* when searching the table, this holds the amount of results found. We use this is that if the amount of result found is greated than 0 we will print the name of the table and the column */
v_result_string VARCHAR2(254);
BEGIN
  v_tab_arr1 := tab_name_arr();
  /*INITIALIZE THE ARRAY*/
  v_col_arr1 := tab_name_arr();
  /*INITIALIZE THE ARRAY*/
  v_col_arr1.EXTEND(1000);
  /* INITIALIZE THE ARRAY to the maximum amount of columns allowed in a table */
  /* This will return the amount of tables in the user schema so that we know how many times we need to invoke the for loop */
  SELECT COUNT(table_name)
  INTO v_amount_of_tables
  FROM user_tables;
  v_tab_arr1.EXTEND(v_amount_of_tables);
  /*INITIALIZE THE ARRAY to the number of tables found in the user's schema */
  FOR i IN 1..v_amount_of_tables
  LOOP
    /*LOOP until we reach the maximum amount of tables in the user schema */
    /* start populating the tables array with table names. The data is read fomr the data dictionary */
    SELECT table_name
    INTO v_tab_arr1(i)
    FROM
      ( SELECT rownum a, table_name FROM user_tables ORDER BY table_name
      )
    WHERE a = i;
  END LOOP;
  /* now, after we have an array with all the names of the tables in the user's schmea, we'll start going
  over each table and get all of its columns so that we can search every column */
  FOR i IN 1..v_amount_of_tables
  LOOP
    /*select the amount of columns in the table where the data_type matches the data type the user passed as a parameter to the procedure */
    SELECT COUNT(*)
    INTO v_amount_of_cols
    FROM user_tab_columns
    WHERE table_name = v_tab_arr1(i)
      AND data_type  = p_type;
    /* start searching the clumns ONLY IF there is at least one column with the requested data type in the table */
    IF v_amount_of_cols <> 0 THEN
      /* do the search for every column in the table */
      FOR j IN 1..v_amount_of_cols
      LOOP
        SELECT column_name
        INTO v_col_arr1(j)
        FROM
          (SELECT rownum a,
            column_name
          FROM user_tab_columns
          WHERE table_name = v_tab_arr1(i)
            AND data_type  = p_type
          )
        WHERE a = j;
        /* each type of data_type has its own SQL query used to search. Here we execute different queries based on the user passed parameter of requested data type */
        IF p_type IN ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2') THEN
          EXECUTE immediate 'select count(*) from ' || v_tab_arr1(i) || ' where ' || lower(v_col_arr1(j)) || ' like ' || '''' || '%' || lower(p_search) || '%' || '''' INTO v_search_result;
        END IF;
        IF p_type IN ('DATE') THEN
          EXECUTE immediate 'select count(*) from ' || v_tab_arr1(i) || ' where ' || v_col_arr1(j) || ' = ' || '''' || p_search || '''' INTO v_search_result;
        END IF;
        IF p_type IN ('NUMBER', 'FLOAT') THEN
          EXECUTE immediate 'select count(*) from ' || v_tab_arr1(i) || ' where ' || v_col_arr1(j) || ' = ' || p_search INTO v_search_result;
        END IF;
        /* if there is at least one row in the table which contains data, return the table name and column name */
        IF v_search_result > 0 THEN
          v_result_string := v_tab_arr1(i) || '.' || v_col_arr1(j);
          EXECUTE immediate 'insert into x_search_db_results values (' || '''' || v_result_string || '''' || ')';
          COMMIT;
        END IF;
      END LOOP;
    END IF;
  END LOOP;
END;