Search for oracle objects in unix scripts with specific name.
On Unix:
1. Search script with "prm_" in the text:
find . -name '*' -print | xargs grep -i 'prm_' > /tmp/all_files
On Client
2. Copy file (with winscp) to local dir.
3. Remove all 'binary shit' lines
4. Create on Oracle some table with a clob field
(table prm_clob, with 2 fields t0(varchar2(10)) and t1(clob)).
5. Copy all content of the all_files (with toad) into this Oracle table (t1 as clob and t0 as 'some descriptive value').
6. Create a table type for storing varchar2 values (in this example varchar2(500) is used. Use a size as you like.
CREATE OR REPLACE TYPE PRM.prm_jka_nt_array as table of varchar2(500)
7. Create a function in which a clob can be read line by line (I've created a package function)
CREATE OR REPLACE PACKAGE PRM.prm_jka_clob AS
-- Public Package Procedures en Functions
function clob_as_lines(p_cursor in sys_refcursor)
return prm_jka_nt_array
pipelined;
END prm_jka_clob;
CREATE OR REPLACE PACKAGE BODY PRM.prm_jka_clob AS
function clob_as_lines(p_cursor in sys_refcursor)
return prm_jka_nt_array
pipelined
is
l_clob clob;
l_pos number := 1; -- Current position in the file (file begins at position 1)
l_sum binary_integer := 0; -- Amount of characters have been read
l_buf varchar2(500); -- Read Buffer
l_end number; -- End of the current word which will be read
begin
fetch p_cursor into l_clob;
if (p_cursor%notfound)
then
close p_cursor;
raise no_data_found;
end if;
close p_cursor;
if (dbms_lob.isopen(l_clob) != 1)
then
dbms_lob.open(l_clob, 0);
end if;
loop
-- Calculate the end of the current word
l_end :=
dbms_lob.instr(l_clob
,chr(10)
,l_pos
,1);
-- Process end-of-file
if (l_end = 0)
then
l_end := length(l_clob);
l_sum := l_end - l_pos + 1;
if l_sum > 0
then
dbms_lob.read(l_clob
,l_sum
,l_pos
,l_buf);
l_buf := rtrim(l_buf, chr(13));
pipe row (l_buf);
end if;
exit;
end if;
-- Read until end-of-file
l_sum := l_end - l_pos;
dbms_lob.read(l_clob
,l_sum
,l_pos
,l_buf);
l_buf := rtrim(rtrim(l_buf, chr(10)), chr(13));
pipe row (l_buf);
l_pos := l_pos + l_sum + 1;
end loop;
if (dbms_lob.isopen(l_clob) = 1)
then
dbms_lob.close(l_clob);
end if;
end clob_as_lines;
END prm_jka_clob;
8. Now let Oracle do some regexp thingies to match any object in the unix-grep output.
declare
cursor c_objects
is
select object_name
from all_objects where owner = 'PRM';
type t_objects is table of c_objects%rowtype index by binary_integer;
a_objects t_objects;
cursor c_clob
is
SELECT column_value
FROM TABLE(prm_jka_clob.clob_as_lines(CURSOR(SELECT t1
FROM prm_clob
where t0=1)));
type t_clob is table of c_clob%rowtype index by binary_integer;
a_clob t_clob;
l_found boolean;
l_object varchar2(100);
begin
--collect all objects for comparing
open c_objects;
fetch c_objects bulk collect into a_objects;
close c_objects;
--collect the values from the clob. every line is a single array record now
open c_clob;
fetch c_clob bulk collect into a_clob;
close c_clob;
-- loop through the clob lines
-- layout of the line is output from the unix grep command: [file name]:[matching string]
for i in 1..a_clob.count
loop
l_found := false;
l_object := null;
-- for every line loop through comparing object names
for j in 1..a_objects.count
loop
-- if an (regeular expression wise) object name (from c_objects) is is found in the clob-string-line then 'bingo'
l_found := regexp_like(regexp_replace(a_clob(i).column_value,'(^.*):(.*)$','\2'),a_objects(j).object_name,'i');
if l_found then
--found, so store the object
l_object := a_objects(j).object_name;
-- exit the j-loop
exit;
end if;
end loop;
-- outside the loop, something has been found, so report it as csv line: [filename];[object_name];[line context]
-- use a simple regexp_replace for selecting the grep output part before (\1) the double-point and after (\2).
if l_found then
dbms_output.put_line(regexp_replace(a_clob(i).column_value,'(^.*):(.*)$','\1')||';'||l_object||';"'''||regexp_replace(a_clob(i).column_value,'(^.*):(.*)$','\2')||'"');
end if;
end loop;
end;