Re: Need help using the data dictionary

Hi,

I got the same result. I altered your query to this:

  select column_name
    from all_tab_columns
   where owner = p_user_new
     and table_name = p_table_name
  minus
  select column_name
    from all_tab_columns
   where owner = p_user_old
     and table_name = p_table_name;

It works fine without the variables, but when I use it in a stored function I always get all columns in the new table. I also tried this query with the same result:

  select a1.column_name
    from all_tab_columns a1

   where a1.owner = p_user_new
     and a1.table_name = p_table_name
     and a1.column_name not in ( select a2.column_name
                                   from all_tab_columns a2
                                  where a1.table_name = a2.table_name
                                    and a2.owner = p_user_old);

It would be interesting to know whether this problem is just happening on my Oracle server or whether someone else is experiencing the same problem.

Thorvaldur Arnarson

In article <951412110.11427.3.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> 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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Feb 25 2000 - 02:46:32 CST