banjalukaforum.com

v_check := instr2(v_string_fnc, 'INSERT ');

if v_check > 0 then
 
    insert_flag  := 'C';
    v_check      := instr2(v_string_fnc, 'INTO ', v_check);
    v_check      := v_check + 5;
    result_table := substr(v_string_fnc, v_check);
    result_table := substr(result_table, 0, instr(result_table, ' '));
    result_table := rtrim(result_table);
 
    merge into result_set
    using dual
    on (tables_used = result_table)
    when matched then
      update set create_operation = insert_flag
    when not matched then
      insert
        (tables_used, create_operation)
      values
        (result_table, insert_flag);

create table dummy1(id number, value varchar2(128));

create table dummy2(id number, value varchar2(128));

create table result_set(tables_used varchar2(30), create_operation varchar2(16));

CREATE OR REPLACE FUNCTION test(
   p_id IN NUMBER,
   p_value IN VARCHAR2,
   p_id2 IN NUMBER,
   p_value2 IN VARCHAR2)
RETURN NUMBER
AS
BEGIN
  INSERT INTO dummy1(id, value)
  VALUES(p_id, p_value);
               
  INSERT INTO dummy2(id, value)
  VALUES(p_id2, p_value2);
END test;
/

declare
  lvTableFound VARCHAR2(32);
begin
  FOR func_rec IN (select text
                      from user_source
                     where name = 'TEST'
                       and type = 'FUNCTION'
                     order by line)
  LOOP           
    IF INSTR(func_rec.text, 'INSERT') > 0 THEN
      select UPPER(regexp_replace(func_rec.text, '.*(INSERT([[:blank:]]+)INTO([[:blank:]]+))([[:alnum:]]+)(.*)', '\4')) into lvTableFound from dual;
     
      dbms_output.put_line('found: '|| lvTableFound);
     
      merge into result_set
      using (select lvTableFound as result_table from dual)
      on (tables_used = result_table)
      when matched then
        update set create_operation = 'INSERT'
      when not matched then
        insert
          (tables_used, create_operation)
        values
          (result_table, 'INSERT');
         
    END IF;
  END LOOP;         
end;
/

TYPE results_rec IS record (
 
  table_ varchar2(40),
  create_ char(1) := '-',
  read_ char(1) := '-',
  update_ char(1) := '-',
  delete_ char(1) := '-');

Type varray_res is varray(100) of results_rec;
 
    va1 varray_res;
   
  TYPE results_tab IS TABLE of varray_res INDEX BY VARCHAR2;

v_check := INSTR2(v_string_fnc, 'UPDATE ');
  string_to_parse := v_string_fnc;
  WHILE v_check > 0 LOOP
      v_check := INSTR2(string_to_parse, 'UPDATE ');
      IF v_check > 0 THEN
      v_check := INSTR2(string_to_parse, 'UPDATE ', v_check) + 7;
      result_table := SUBSTR(string_to_parse, v_check);
      string_to_parse := result_table;
      result_table := RTRIM(SUBSTR(result_table,
                                   0,
                                   INSTR(result_table, ' ')));
      table_indx := result_table;
      tab_res(table_indx).table_ := result_table;
      tab_res(table_indx).update_ := 'U';
        end if;
  end loop;