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 '''



sql_stmt :=

'SELECT MIN(TableName) as TableName, '

DBMS_OUTPUT.put_line ('sql_stmt' || sql_stmt);

On Feb 23, 2:08*am, Sudhir <nshanmu... (AT) gmail (DOT) com> wrote:
Are you comparing the tables DDL or their contents?

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

On Feb 23, 3:32*am, "Vladimir M. Zakharychev"
<vladimir.zakharyc... (AT) gmail (DOT) com> wrote:

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.

On Feb 24, 3:20*am, Sudhir <nshanmu... (AT) gmail (DOT) com> wrote:
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

Sudhir <nshanmugam (AT) gmail (DOT) com> writes:

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

In article <87pr3v2i3d.fsf (AT) lion (DOT) rapttech.com.au>, timx (AT) nospam (DOT) dev.null
says...>

<on automated testing>

We are looking for automated testing tools or techniques for mod_plsql-
based (i.e. web apps powered by pl/sql) applications, can anyone suggest
anything?


--
jeremy

On Feb 24, 2:27*am, Jeremy <jeremy0... (AT) gmail (DOT) com> wrote:

Thanks for all your input.

let me check on PL/SQL Unit Testing Framework (utPLSQL) and Quest Code
Tester and will keep you all posted.

On Feb 24, 8:01*pm, Sudhir <nshanmu... (AT) gmail (DOT) com> wrote:
I was able to do the comparison dynamically by creating temp table.
Right now I'm at home and dont have the scripts handy. I will posted
it from work.