Dynamic table comparision issue
in [Oracle]
|
Prev: Ways to Generate References to Specific Character String in a Schema
Next: location of oracle asmlib disks in solaris
From: Sudhir on 22 Feb 2010 18:08 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) 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) Since I dont know the table and column type before hand, how do I run the SQL statement and fetch the result? Here is the script I have. sql_stmt is the variable that have the complete sql statement when executed will give the difference. Can anyone of you help me to run the sql statement and fetch the result to a table or DBMS_OUTPUT window? CREATE OR REPLACE PROCEDURE cstouser.sp_comparetables (tablea IN varchar2,tableb IN varchar2,columnlist IN VARCHAR2 DEFAULT NULL ) IS sql_stmt VARCHAR2 (32767); TYPE r_cursor IS REF CURSOR; c_1 r_cursor; i NUMBER := 0; j NUMBER := 0; t1columnlist VARCHAR2 (32767) := NULL; t1 VARCHAR2 (32767) := NULL; t2columnlist VARCHAR2 (32767) := NULL; groupby VARCHAR2 (5000); cname VARCHAR2 (20); str1 VARCHAR2 (32767); str2 VARCHAR2 (32767); table1 VARCHAR2 (100); table2 VARCHAR2 (100); CURSOR c1 (table1 IN varchar2) IS SELECT column_name, data_type, data_length FROM all_tab_cols WHERE table_name = table1 ORDER BY column_name; CURSOR c2 (table2 IN varchar2) IS SELECT column_name, data_type, data_length FROM all_tab_cols WHERE table_name = table2 ORDER BY column_name; BEGIN table1 := UPPER (tablea); table2 := UPPER (tableb); IF columnlist IS NULL THEN FOR c_c1 IN c1 (table1) LOOP t1columnlist := t1columnlist || c_c1.column_name || ','; i := i + 1; END LOOP; FOR c_c2 IN c2 (table2) LOOP t2columnlist := t2columnlist || c_c2.column_name || ','; j := j + 1; END LOOP; -- To remove the comma in the end t1columnlist := SUBSTR (t1columnlist, 1, LENGTH (t1columnlist) - 1); t2columnlist := SUBSTR (t2columnlist, 1, LENGTH (t2columnlist) - 1); -- compare if both the tables have same number of columns IF i = j THEN groupby := t1columnlist; ELSE DBMS_OUTPUT.put_line(' The column list doesnt match between these two tables. Script terminates'); END IF; ELSE t1columnlist := columnlist; t2columnlist := columnlist; groupby := columnlist; END IF; sql_stmt := 'SELECT ''' || table1 || ''' AS TableName, ' || t1columnlist || ' FROM ' || table1 || ' UNION ALL SELECT ''' || table2 || ''' As TableName, ' || t2columnlist || ' FROM ' || table2; sql_stmt := 'SELECT MIN(TableName) as TableName, ' || t2columnlist || ' FROM (' || sql_stmt || ') A GROUP BY ' || groupby || ' HAVING COUNT(*) = 1'; DBMS_OUTPUT.put_line ('sql_stmt' || sql_stmt);
From: Vladimir M. Zakharychev on 23 Feb 2010 04:32 On Feb 23, 2:08 am, Sudhir <nshanmu...(a)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
From: Sudhir on 23 Feb 2010 19:20 On Feb 23, 3:32 am, "Vladimir M. Zakharychev" <vladimir.zakharyc...(a)gmail.com> wrote: > On Feb 23, 2:08 am, Sudhir <nshanmu...(a)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.
From: Vladimir M. Zakharychev on 24 Feb 2010 01:36 On Feb 24, 3:20 am, Sudhir <nshanmu...(a)gmail.com> wrote: > On Feb 23, 3:32 am, "Vladimir M. Zakharychev" > > > > <vladimir.zakharyc...(a)gmail.com> wrote: > > On Feb 23, 2:08 am, Sudhir <nshanmu...(a)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
From: Tim X on 24 Feb 2010 02:44
Sudhir <nshanmugam(a)gmail.com> writes: > On Feb 23, 3:32 am, "Vladimir M. Zakharychev" > <vladimir.zakharyc...(a)gmail.com> wrote: >> On Feb 23, 2:08 am, Sudhir <nshanmu...(a)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 |