Re: Dynamic table comparision issue
From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Tue, 23 Feb 2010 22:36:37 -0800 (PST)
Message-ID: <1536e67f-3f36-4129-b487-8f1b87ce0d68_at_15g2000yqi.googlegroups.com>
On Feb 24, 3:20 am, Sudhir <nshanmu..._at_gmail.com> wrote:
> 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.
Date: Tue, 23 Feb 2010 22:36:37 -0800 (PST)
Message-ID: <1536e67f-3f36-4129-b487-8f1b87ce0d68_at_15g2000yqi.googlegroups.com>
On Feb 24, 3:20 am, Sudhir <nshanmu..._at_gmail.com> wrote:
> 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.
There are tools that can do this for you. There's PL/SQL Unit Testing Framework (utPLSQL) written and maintained by Steven Feuerstein, which is free afaik. There's Quest Code Tester which utilizes the framework and automates testing (not free, but available for trial.) There's PLUTO framework. There may be other tools as well - automated testing is quite popular topic...
The way you chose will most probably lead you nowhere (but it might eventually lead you to a product comparable to QCT if you are persistent enough.) Give QCT a try - even if your company will not spend on it, you might pick up a few good ideas from it.
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Received on Wed Feb 24 2010 - 00:36:37 CST