Re: Dynamic table comparision issue

> On Feb 23, 3:32 am, "Vladimir M. Zakharychev"
> <vladimir.zakharyc..._at_gmail.com> wrote:
>> On Feb 23, 2:08 am, Sudhir <nshanmu..._at_gmail.com> 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)
>>    http://www.dynamicpsp.com
>
>
> 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.

Yes, you definitely don't want to manually run the tests. As you point out, boring and error prone. Iw oul definitely be looking at a way to automate running of the tests. In the past, I've got quite good results using the perl test harnesses and DBI.

Do all your stored procedures only write to a specific table? What about procedures that query data and possibly provide support/input to other procedures? What about tests on parameters, exception handling etc.

Comparison of tables is a useful metric, but I'm not sure it is anywhere near sufficient.

In the past, I've done something similar, but because the test db instance and the prod instance are different, I tend to use things like perl to extract data from the two tables and do a comparison. sometimes, I just dump the data out to a text file and use diff etc.

There are some PL/SQL test harnesses that may be worth checking out as well. some are free.
Tim

-- 
tcross (at) rapttech dot com dot au