Re: Dynamic table comparision issue

From: Sudhir <>
Date: Tue, 23 Feb 2010 16:20:08 -0800 (PST)
Message-ID: <>

On Feb 23, 3:32 am, "Vladimir M. Zakharychev" <> wrote:
> On Feb 23, 2:08 am, Sudhir <> wrote:
> > I'm trying to write a stored procedure which will compare two tables
> > dynamically and will display the difference. The input parameter are
> > two table names and columnames(optional)
> Are you comparing the tables DDL or their contents?
> > I get the column names from all_tab_cols table and dynamically form a
> > select statement, when executed will give me the difference between
> > those two tables (using unionall)
> If you compare the DDL, what you need to do here is create a single
> query that, given two table names, will output the difference in DDL
> between them. This question can be answered from xxx_TAB_COL[UMN]S
> alone. No need for dynamic SQL here as table names are arguments to
> the query (bind variables.) Actually, it's a very bad idea to
> concatenate arguments into a dynamic SQL statement like you do instead
> of using bind variables: not only this approach inhibits scalability,
> but it opens the door to infamous SQL injection attacks against your
> code (simply put, I supply SQL as either TABLEA or TABLEB, you blindly
> concatenate it into your statement and execute that statement
> executing my code with it. I only need to construct my added SQL in
> such a way as to form syntactically correct statement from your SQL
> and my SQL, which is usually pretty simple.)
> You need to consider how deep the comparison should be - do you only
> want the names of the columns that do not exist in one of the tables?
> Or do you also want to identify columns with the same name but
> different definition (data type?) This will define your GROUP BY
> clause. You may actually create two different queries answering both
> questions and add an argument to your SP to choose between them.
> The purpose of the optional column list is unclear - how is it
> supposed to constrain the comparison?
> Regards,
>    Vladimir M. Zakharychev
>    N-Networks, makers of Dynamic PSP(tm)

I'm trying to compare the contents of two tables.

The purpose of the script is compare two tables contents dynamically and display the difference. Actually I'm a tester not developer, my job is to test multiple stored procedures to make sure it works fine for various input. So performance or SQL injection is not a problem.

My approach is run the stored procedure under test and write it to Table A. Run the store procedure created by us and write it to Table B. Now I will compare contents of each table, if it matches then test case passed if not the test case fails.

If its just a single stored procedure under test then I might have not gone for dynamic approach. We have around 15 stored procedures (many more to come) to test and I thought of having a single stored procedure which will compare tables when I just give the table name alone.

On a high level my goal is to automate the store procedure testing during regression. I don't want to manually run each and every testcase(more than 200 testcases) as its time consuming and error prone. Received on Tue Feb 23 2010 - 18:20:08 CST

Original text of this message