1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
| CREATE OR REPLACE procedure APPS.search_db (p_search VARCHAR2, p_type VARCHAR2)
/*
* 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 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 search_db_results values (' || '''' || v_result_string || '''' || ')';
commit;
end if;
END LOOP;
end if;
end loop;
END;
/ |
Erreur dans l'exécution d'une procédure compilée sans problème
Répondre avec citation
Partager