Finding Any Character / NULL in All tables, Columns in 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(32766);
  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;
/


set serveroutput on size 1000000 format wrapped

select find_in_schema('KSHITIJ') from dual;

it provide the rowid with table

select find_in_schema(NULL) from dual;

select find_in_schema(NULL) from TABLE_NAME;


---------------------------------------------------
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "LDBO.FIND_IN_SCHEMA", line 45
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:  
*Action:
--------------------------------------------------------------------
v_where     Varchar2(4000);       to        v_where     Varchar2(32766);
you might try to load a column in a record field that is defined with a smaller size than the data returned from the select.