How to get the Name of the Table with SELECT DELETE INSERT UPDATE operation

Question

Hi all,

I want a tool or solution to find out the affected table on running the procedure|Function or package Given the PL/SQL code.

This is require for me to comeup with the better testcase by knowing which all the tables will be affected by running the code and what all the operation performed on them.

The solution should even work for Procedure calling Procedure.

OutPut may be:

SELECT FROM: TABLE1

DELETE FROM: TABLE2

INSERT INTO: TABLE3

CALL AnotherPROC:

SELECT FROM: TABLE4

DELETE FROM: TABLE5

Thanks in Advance:

Answer 1

In MySql you can get information on the tables that are being affected by adding the keyword EXPLAIN in the start of your Query. It will give you different information's listed as columns. Check if there is a feature like this in Oracle might help in your scenario.

Answer 2

For a pre-run analysis if you are running a stored procedure/package/function then the DBA_DEPENDENCIES table can tell you which objects "depend" on it, but that doesn't mean they may necessarily be affected because the program control can take different directions.

Post-run analysis you could use AUDITing or tracing to see what tables were affected.

Answer 3

There are several different ways you can get some or all of this information, but I can't think of any method that will give you the information in the exact format you specified.

Tracing

A trace file can record everything, but it's all stored in a text file meant to be read by a human. There are lots of examples for how to do this, here's one that just worked for me: http://tonguc.wordpress.com/2006/12/30/introduction-to-oracle-trace-utulity-and-understanding-the-fundamental-performance-equation/

Profiling

You can use DBMS_PROFILER to record which line numbers are called by the procedure. Then you'd have to join the line numbers to DBA_SOURCE to get the actual commands.

V$SQL

This records SQL statements executed. You could search for SQL by PARSING_SCHEMA_NAME and order by LAST_UPDATE_TIME. But this won't get the PL/SQL, and V$SQL can be difficult to use. (SQL may age out, or could get loaded by someone else, etc.)

But to get exactly what you want, all of these solutions require you to write a program to parse SQL and PL/SQL. I'm sure there are tools to do this, but I have no experience with them.

You can always write your own custom logging, but that's a huge amount of work. The best solution may be to ask the developers to adequately document every function, and list the purpose, inputs, outputs, and side-effects of all their code.