Erreur dans l'exécution d'une procédure compilée sans problème [Résolu]

[certis_NDT]

Bonjour à tous,

Je travaille sous la version Oracle 11.5.2 et j'utilise la version 9.6.1 de Toad

J'ai trouvé la procédure suivante sur un forum qui permet de recherche une chaîne de caractère dans l'ensemble des champs de la base.

Code :

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;
/

Le résultat est stocké dans la table "search_db_results" (que j'ai créé pour l'occasion avec les synonymes qui vont bien).
Cette procédure se compile sans erreur et je parviens à l'utiliser sous Toad sans problème.
Toutefois lorsque je souhaite la lancer depuis l'interface d'Oracle j'ai l'erreur suivante :

Erreur ORACLE 6550 dans FDPSTP

Cause : L'exécution du sous-programme FDPSTP a échoué en raison de ORA-06550: line 1, column 24:
PLS-00103: Encountered the symbol "(" when expecting one of the following:

@ <identificateur> <identificateur entre gui

Le tout petit programme que j'ai créé pour lancer la procédure est celui-ci :

Code :

1
2
3
4
5
6
7
8
 
Begin
 
 
 APPS.search_db(p_search => '@garlock', p_type=>'VARCHAR2') ;
 
 
 End;

J'ai essayé plusieurs modifications sur ce morceau de code en pensant que je passais mal mes paramètres mais rien n'y fait l'erreur est toujours la même.

Quand aurait-il s'il vous plaît une idée pour que je puisse lancer ma procédure depuis l'interface Oracle ? Merci d'avance.

Thibaut

[certis_NDT]

Petit oubli dans le post précédent :

Merci à David Yahalom pour le code cité (trouvé sur http://it.toolbox.com/blogs/david/se...e-schema-24074)

[skuatamad]

Code :

execute immediate 'insert into search_db_results values (' || '''' || v_result_string || '''' || ')';

A mon avis tous ces quotes sont inutiles. Je pense que comme ça, ça suffit :

Code :

execute immediate 'insert into search_db_results values (' || v_result_string || ')';

Sinon la procedure devrait utiliser des variables de liaisons (bind variables), afin d'éviter les risques d'injection SQL tout en étant nettement moins consommateur en parse. Par exemple quelque chose comme :

Code :

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
CREATE OR REPLACE procedure search_db (p_search VARCHAR2, p_type VARCHAR2)
AS
  l_query varchar2(200);
  l_query_insert varchar2(100) := 'insert into search_db_results values (:schema_table)';
  l_search varchar2(100);
  l_search_result integer := 0;
begin
  FOR c IN (SELECT table_name, column_name
              FROM user_tab_columns
             WHERE data_type = p_type) loop
 
    l_query := 'select count(*) from ';
    l_query := l_query || c.table_name;        
    IF (p_type IN ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2')) then
      l_query := l_query || ' where lower(' || c.column_name || ') like :search ';
      l_search := '%'||p_search||'%';
    elsif (p_type = 'DATE') then
      l_query := l_query || ' where ' || c.column_name || ' = to_date(:search) ';
      l_search := p_search;
    elsif (p_type IN ('NUMBER', 'FLOAT')) then
      l_query := l_query || ' where ' || c.column_name || ' = cast(:search as ' || p_type ||' ) ';
      l_search := p_search;
    else 
      l_query := l_query || ' where 1 <> 1 and :search is null ';
    end IF;
    dbms_output.put_line(l_query);
    execute immediate l_query INTO l_search_result USING l_search;
    IF l_search_result > 0 then
      execute immediate l_query_insert USING c.table_name||'.'||c.column_name;
    end IF;
  end loop;
end;
/

A améliorer évidemment pour gérer plus de types de données.
Pour info :

Code :

1
2
else 
  l_query := l_query || ' where 1 <> 1 and :search is null ';

est nécessaire car la variable de liaison doit être présente (and :search is null) mais si on passe un p_type non géré alors on ne fait pas de recherche (1 <> 1 est toujours faux donc on ne scanne pas la table).

[edit] Pour la date ce serait bien de passer un format de date également sinon ce sera le format de la session qui peut être un format américain donc risque de plantage.

[certis_NDT]

Merci bien.

J'ai eu une révélation ce matin et le résultat est : une bêtise de ma part.
Le code n'avait pas de problèmes, j'avais mis un mauvais caractère dans mon nom de fichier...

En tout cas je vais regarder attentivement ton code, parce que rechercher comme ça une chaîne de caractères dans une base c'est assez violent mais malheureusement je risque d'y avoir à nouveau recours.

Merci encore.