Need help using the data dictionary

From: <idlav_at_my-deja.com>
Date: Thu, 24 Feb 2000 15:25:13 GMT
Message-ID: <893igj$qpl$1@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 - 09:25:13 CST