Re: Dynamic table comparision issue
Date: Tue, 23 Feb 2010 01:32:20 -0800 (PST)
Message-ID: <a39e181f-26eb-41e5-aea7-cbd5a7200ced_at_k19g2000yqc.googlegroups.com>
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
Received on Tue Feb 23 2010 - 03:32:20 CST