Search for oracle objects in unix scripts with specific name.

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;