Re: Need help using the data dictionary

A better way to do this is
select column_name
from all_tables a1
where owner = 'USER_NEW'
minus
select column_name
from all_tables a2
where owner = 'USER_OLD'
This is NOT intended as a logical explanation for the differences in results, but generally speaking outer joins are more subject to all sorts of errors because one easily forgets to include a (+) on all applicable columns

Hth,

Sybrand Bakker, Oracle DBA

<idlav_at_my-deja.com> wrote in message news:893igj$qpl$1_at_nnrp1.deja.com...
> 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.
Received on Thu Feb 24 2000 - 10:52:48 CST