PL/SQL parser to identify the operation on table

Question

Hi All, I am writing a PL/SQL parser to identify the operations(Select,Insert,Delete) performed on the Table when I run Procedure, Function or Package.

GOAL:I Goal of this tool is to identify which all the tables will be affected by running the procedure,Fun to prepare with better test case.

Any better ideas or tool will really help a lot.

INPUT: some SQL file with procedure

     or proc file.

OUTPUT required is:

SELECT from: First_table, secondTable

-> In procedure XYZ --This is if the procedure is calling one more procedure

INSERT into: SomeTable

INSERT into: SomeDiffTable

-> END of procedure XYZ --End of one more procedure.

DELETE from: xyzTable

INSERT into: OnemoreTable

My requirement is When I am parsing porc1 if it calls another proc2. I have to go inside that proc2 to find out what all the operation is performed in that and come back to proc1 and continue.:

For this I have to store the all procedure some where and while parsing I have to check each token(word with space) in the tempStorage to find out if it is procedure or not.

As my logic's takes lot of time. Can any body suggest better logic to achieve my GOAL.

Answer 1

There's also the possiblity of triggers being involved. That adds an additional layer of complexity.

I'd say you're better off mining DBA_DEPENDENCIES with a recursive query to determine impact analysis in the abstract; it won't capture dynamic SQL, but nothing will 100% of the time. In your case, proc1 depends on proc2, and proc2 depends on whatever it depends on, and so forth. It won't tell you the nature of the dependency - INSERT, UPDATE, DELETE, SELECT - but it's a beginning.

If you're really interested in determining the actual impact of a single-variable-value run of a procedure, implement it in a non-production system, and then turn auditing on your system up to 11:

begin
  for i in (select owner, object_type, object_name from dba_objects 
             where owner in ([list of application schemas]
               and object_type in ('TABLE', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'VIEW')
  loop
    execute immediate 'AUDIT ALL ON ' || i.owner || '.' || i.object_type || 
                      ' BY SESSION';
  end loop;
end;
/

Run your test, and see what objects got touched as a result of the exectution by mining the audit trail. It's not bulletproof, as it only audits objects that got touched by that execution, but it does tell you how they got touched.