Re: Reference to a component of composite variable

Yes, I try to write a program to compare two tables column VALUES. This two tables (in my example -OLD and New) have SAME structure but DIFFERENT column VALUES. In real database I have about 400 tables and I need to perform this procedure for EVERY table in a loop, that is why I can't use actual column names but I use VARIABLE 'field_name'. The 'field_name' is populated with ACTUAL column name by cursor c3 in a loop from SYSTEM VIEW user_tab_columns, which contains all current schema tables columns. So tables OLD and NEW contains 3 fields each, for example: LineNo, First-name, Last_name. Some columns of the tables contains SAME values,
some - different. The user_tab_columns contains all 6 fields for 2 tables (NEW and OLD). I DON'T WANT to make a reference to actual CURRENT tables column names, because I want to use the SAME procedure for all 400 tables, that is why I use user_tab_columns. So I fetch a record from OLD table by cursor c1 into variable old_row of ROWTYPE, the same I perform with NEW table - I fetch a record from NEW table by cursor c2 into variable new_row of ROWTYPE. Then I have a VARIABLE field_name in which I put every column name of both tables (OLD and NEW) from user_tab_columns using cursor c3 trough a loop and I try to compare in RUN TIME the VALUES of pair of columns for both tables like:
  If old_row.field_name = new_row.field_name then ...etc.
So because in user_tab_columns the first column name is LineNo then run time comparison must be like that:
  If old_row.LineNo = new_row.LineNo Then ... But there is no passing the actual value (LineNo) of the field_name variable in run time for expression: old_row.field_name. That is why I got the error PLS-302.
So the actual question is: how to make a reference (in run time) to the component (element) of a composite variable , for example old_row, when a component name (field name) is a variable? I hope that I have expressed yourself more clear.

Thanks,
Gennady

Sybrand Bakker wrote in message
<957770118.2043.0.pluto.d4ee154e_at_news.demon.nl>...
>Just reproduced this behavior, works like a charm, no problem.
>What I did was
>create table old
>(field_name varchar2(30))
>create table new
>(field_name varchar2(30))
>Doing so, your code works! (I don't get an error message, Personal Oracle
>8.1.5)
>
>Actually, you seem to express yourself unclear (at least to me)
>because what happens is you fetch from old and new only _once_
>You loop through c3, but in this loop you don't fetch from old and new,
>_and_ you don't compare values from old and new with values from c3.
>Actually you don't do anything at all with c3.
>Don't you seem to write a program to compare two tables column by column?
>If I try to understand you it looks like you don't try to compare column
>names, but column contents.
>If that's true, why don't you just use minus
>select * from old
>minus
>select * from new
>because the other solution would be constructing different sql statements
>based upon the contents of user_tab_columns. What you seem to try to do,
>constructing a static pl/sql statement based upon the datadictionary, isn't
>possible.
>
>regards,
>
>Sybrand Bakker, Oracle DBA
>
>
>Could you please try to express yourself more clear.
>
>
>Gennady <gbesfamilny_at_home.com> schreef in berichtnieuws
>1CpR4.210926$Dv1.2692524_at_news1.rdc1.bc.home.com...
>> Hello,
>> In my PL/SQL code I have a composite variable of ROWTYPE and I try to
 access
>> the variable component using another variable, but I got an error:
>> "PLS-00302: component 'field_name' must be declared".
>> The code is:
>> declare
>> cursor c1 is select * from old;
>> old_row c1%ROWTYPE;
>> cursor c2 is select * from new;
>> new_row c2%ROWTYPE;
>> cursor c3 is select column_name from user_tab_columns where table_name =
>> 'OLD';
>> field_name c3%ROWTYPE;
>> begin
>> dbms_output.enable;
>> open c1;
>> open c2;
>> open c3;
>> loop
>> fetch c1 into old_row;
>> fetch c2 into new_row;
>> exit when (c1%notfound or c2%notfound);
>> end loop;
>> loop
>> fetch c3 into field_name;
>> If (old_row.field_name = new_row.field_name) then --error in this line!
>> dbms_output.put_line('SAME VALUE');
>> else
>> dbms_output.put_line('DIFFERENT VALUE');
>> end if;
>> exit when c3%notfound;
>> end loop;
>> end;
>> The 'field_name' is A VARIABLE and I put in this variable actual OLD
>> and NEW tables (both have SAME structure) column names which I get from
>> SYSTEM VIEW user_tab_columns, using cursor c3 (see the code above).
>> So the actual question is: how to make a reference (in run time) to the
>> component
>> (element) of a composite variable , for example old_row, when a component
>> name (field name) is a variable?
>>
>> Thanks,
>> Gennady
>>
>>
>>
>>
>
>
Received on Mon May 08 2000 - 00:00:00 CDT