Re: Dynamic table comparision issue
Date: Wed, 24 Feb 2010 18:44:38 +1100
Sudhir <nshanmugam_at_gmail.com> writes:
> 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?
>> 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
> 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
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.
-- tcross (at) rapttech dot com dot auReceived on Wed Feb 24 2010 - 01:44:38 CST