Renetta.nl

Soms moet je in een Oracle database een numerieke of alfanumerieke waarde zoeken. Ik heb een PL/SQL script gebouwd om dat in een Oracle database te doen.

Je kunt met dit script één database-schema per keer doorzoeken. Het script houdt rekening met het verschil tussen numerieke en alfanumerieke waarden, dus als een varchar2 kolom een numerieke waarde bevat, heb je pech. Dit zal natuurlijk nooit gebeuren bij een goed doordacht datamodel 😉 .

Je kunt het script downloaden als zip bestand: Database search script

Dit is het script:

-- ----------------------------------------------------------
-- OS : any
-- RDBMS : Oracle 9i+
-- Created by : Rene Zijlstra
-- Created on : 2004/11/29
-- Usage : Searh a string in all tables
-- Version : 1.1
-- File : search.sql
-- ----------------------------------------------------------
-- Parameters :
-- none, the input paramaters are prompted for
-- ----------------------------------------------------------
-- Remarks:
-- You will be prompted for database schema and string to search
-- Added dbms_application_info, so progress can be viewed in v$session
-- ----------------------------------------------------------
-- Rev User revision date comment
-- --- ------------- ------------- -------------------
-- 0.1 Rene Zijlstra 2004/11/09 creation
-- 1.0 Rene Zijlstra 2006/01/04 Modified and tuned (totally rewritten)
-- 1.1 Rene Zijlstra 2008/04/06 Removed views from cursor + added dbms_application_info
-- 1.2 Rene Zijlstra 2009/12/02 Added support for lowercase tables an columns (Why do people want that!?)
-- ----------------------------------------------------------
-- Almost as good as Dutch apple-pie --

— Set the following to speed up a bit
alter session set sort_area_size=20971520;

set serveroutput on size 100000;
set verify off;

accept i_schema prompt “Enter database schema to search in: ”
accept i_string prompt “Enter Text string to search for: ”

— Multiple rows fetch
— Get all tables with CHAR-like columns
declare

vg_stime pls_integer default dbms_utility.get_time;

— Check if column is numeric
function fg_is_number (i_string varchar2) return boolean
is
vl_dummy pls_integer;
begin
vl_dummy := to_number(i_string);
return true;
exception
when others then
return false;
end fg_is_number;

procedure pg_search_table( i_schema in varchar2
, i_table in varchar2
, i_column in varchar2
, i_string in varchar2
)
as
type cursor_type is ref cursor;
rl_cnt cursor_type;
vl_cnt pls_integer;
begin
open rl_cnt for ‘select count(*) from “‘||i_schema||'”.”‘||i_table||'” where lower(“‘||i_column||'”) like :b3′
using i_string;
fetch rl_cnt into vl_cnt;
case
when (vl_cnt > 0) then dbms_output.put_line (i_schema||’.’||i_table||’.’||i_column|| ‘: ‘ || vl_cnt);
else null;
end case;
close rl_cnt;
end pg_search_table;

procedure pg_search_schema( i_schema in varchar2, i_string in varchar2)
is

vl_dtp varchar2(16) := ‘VARCHAR2_TABLE’;

cursor cl_tab( i_schema in varchar2, i_data_type in varchar2)
is
select tcs.table_name
, tcs.column_name
from dba_tab_columns tcs
where data_type like i_data_type
and data_type != vl_dtp
and owner = i_schema
and not exists ( select null
from dba_views vws
where vws.view_name = tcs.table_name
and vws.owner = tcs.owner
)
order by table_name
, column_name
;

type tl_tabnm is table of all_tab_columns.table_name%type;
type tl_colnm is table of all_tab_columns.column_name%type;
vl_tables tl_tabnm;
vl_columns tl_colnm;
vl_data_type varchar2(20);

begin

— If the search-string is a numeric value search in NUMBER and VARCHAR2 columns
— Else search in all CHAR-like columns
if fg_is_number(i_string)
then
vl_data_type := ‘NUMBER’;
dbms_output.put_line(‘searching for number ‘||i_string||’ in database schema ‘||i_schema||chr(10));
else
vl_data_type := ‘%CHAR%’;
dbms_output.put_line(‘searching for string “‘||i_string||'” in schema ‘||i_schema||chr(10));
end if;

open cl_tab(i_schema, vl_data_type);
fetch cl_tab
bulk collect into vl_tables, vl_columns;
for rl_tab in 1..cl_tab%rowcount loop
dbms_application_info.set_action( ‘searching ‘ || vl_tables(rl_tab) || ‘.’ || vl_columns(rl_tab) );
pg_search_table(i_schema, vl_tables(rl_tab), vl_columns(rl_tab), i_string );
end loop;
close cl_tab;
end pg_search_schema;

begin

— Tell to uncle v$session what you are doing
dbms_application_info.set_module( ‘search.sql’,’searching for “&&i_string”‘ );
— dbms_lock.sleep(5);
pg_search_schema(upper(‘&&i_schema’), lower(‘&&i_string’));

— Reset session information
dbms_application_info.set_module( null, null );

dbms_output.put_line(‘Search time: ‘||round((dbms_utility.get_time-vg_stime)/100, 2) ||’ Seconds…’ );
end;
/




undefine i_schema;
undefine i_string;