Re: Need help using the data dictionary

Hi.

 Try using
 SELECT column_id, column_name
 FROM all_tab_columns
 WHERE owner = p_user1 AND

       table_name = p_tab_name
 MINUS
 SELECT column_id, column_name
 FROM all_tab_columns
 WHERE owner = p_user2 AND

       table_name = p_tab_name;

 It may help you to identify tables with different order of columns and  ( by adding other fields from all_tab_columns ) another diffrences as  well ( data types, length, etc. ).

 HTH. Michael.

In article <893igj$qpl$1_at_nnrp1.deja.com>,   idlav_at_my-deja.com wrote:
> Hi,
>
> I'm trying to create a script to show me the differences
> between two tables owned by two different users, USER_OLD
> and USER_NEW. The tables have the same name but the tables
> owned by USER_NEW have got new columns and I would like to
> get a listing of those differences. Here is an example:
>
> USER_OLD owns a table test which is defined as:
>
> create table test
> ( value1 int
> )
>
> USER_NEW owns a table test which is defined as:
>
> create table test
> ( value1 int
> , value2 int
> , value3 int
> )
>
> You can get the column differences by running this select
> query:
>
> select a1.column_name
> from all_tab_columns a1, all_tab_columns a2
> where a2.table_name (+) = a1.table_name
> and a1.owner = 'USER_NEW'
> and a2.owner (+) = 'USER_OLD'
> and a1.COLUMN_NAME = a2.column_name (+)
> and a2.column_name is null
> and a1.table_name = 'TEST'
>
> The result is:
>
> COLUMN_NAME
> -----------
> VALUE2
> VALUE3
>
> Then I wanted to create a general function with the users
> and table name as parameters. The function I created looks
> like this:
>
> create or replace function sp_table_diff
> ( p_user_old varchar2
> , p_user_new varchar2
> , p_table_name varchar2
> )
> return int
> is
>
> p_column_name varchar2(30);
>
> cursor cColumn_Diff is
> select a1.column_name
> from all_tab_columns a1, all_tab_columns a2
> where a1.table_name = p_table_name
> and a2.table_name (+) = a1.table_name
> and a1.owner = p_user_new
> and a2.owner (+) = p_user_old
> and a1.COLUMN_NAME = a2.column_name (+)
> and a2.column_name is null;
>
> begin
>
> open cColumn_Diff;
>
> loop
> p_column_name := NULL;
>
> fetch cColumn_Diff into p_column_name;
> exit when cColumn_Diff%notfound;
>
> dbms_output.put_line(p_column_name);
>
> end loop;
>
> close cColumn_Diff;
>
> return 0;
>
> end;
>
> The server output gives me this result:
>
> VALUE1
> VALUE2
> VALUE3
>
> when I expected,
>
> VALUE2
> VALUE3
>
> The select queries are the same except from the use of
> variables in the function. Any ideas?
>
> Thorvaldur Arnarson
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Feb 26 2000 - 09:00:10 CST