Re: Reference to a component of composite variable

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