Dynamic Pl/sql

[meeko]

hi,
i am trying to create a dynamic sql to compare the old and new values in a table by usind a procedure as follows
The g_o_rec and g_n_rec are global variables declared at the package level

When I execute this, it always goes into exeception with the following error
sqlerrm ORA-00904: "G_N_REC"."PHONE": invalid identifier
sqlerrm ORA-00904: "G_N_REC"."CONTACT_PHONE": invalid identifier
sqlerrm ORA-00904: "G_N_REC"."WEBPAGE_URL": invalid identifier
sqlerrm ORA-00904: "G_N_REC"."FAX": invalid identifier

Any help is appreciated here.
Thanks
---------------------------------------

Procedure comp_old_new_vendor(g_o_rec in out dw_new.dw_vendor%rowtype,
g_n_rec in dw_new.dw_vendor%rowtype, l_changed in out varchar2)
is

l_compare varchar2(4000);
l_data_type varchar2(50);
l_result number;
l_new_val varchar2(2000);


begin




for i in ( select data_type, 'g_o_rec.'|| column_name 'g_n_rec.'||column_name l_col2, column_name from user_tab_columns where table_name = 'DW_VENDOR' and column_name NOT IN ('LOADED', 'VENDOR_ID')

)
loop

begin

execute immediate('select nvl(max(1),0) from dual where ' || i.l_col1 || ' <> ' || i.l_col2 ) into l_result;

dbms_output.put_line('l_result ,,,,' || l_result);

exception
when others then
-- null;
dbms_output.put_line('sqlerrm ' || sqlerrm);

end;
end loop;
End comp_old_new_vendor;

[Dave44]

can you give us the DDL for the table dw_vender?

also i wouldn't have a variable at the package level with the same name as a parameter for a procedure within the package... needless confusion.

[meeko]

Thanks for the response

Initially the procedure call was
Procedure comp_old_new_vendor(l_o_rec in out dw_new.dw_vendor%rowtype,
l_n_rec in dw_new.dw_vendor%rowtype, l_changed in out varchar2)

begin

g_o_rec := l_o_rec;
g_n_rec := l_n_rec;
...
....
All the other statements are the same as in my initial posting

End comp_old_new_vendor;
This didn't work and hence tried it as in my intial posting




Here is the ddl for the dw_vendor.
create table dw_vendor
(VENDOR_ID NUMBER,
VENDOR_NAME VARCHAR2(1250),
BUSINESS_TYPE_CODE VARCHAR2(24),
CONTACT_FIRST_NAME VARCHAR2(300),
CONTACT_ID NUMBER,
CONTACT_LAST_NAME VARCHAR2(300),
CONTACT_MID_NAME VARCHAR2(300),
CONTACT_TITLE VARCHAR2(300),
STATE_CODE VARCHAR2(32),
ZIP_CODE VARCHAR2(24),
ADDRESS_TEXT VARCHAR2(1250),
CHANGE_DATE DATE,
CITY VARCHAR2(1250),
CONTACT_ADDRESS_TEXT VARCHAR2(1250),
CONTACT_EMAIL VARCHAR2(60),
CONTACT_PREFIX VARCHAR2(150),
CONTACT_STATE_CODE VARCHAR2(24),
CONTACT_SUFFIX VARCHAR2(150),
CONTACT_ZIP_CODE VARCHAR2(24),
PHONE VARCHAR2(30),
CONTACT_PHONE VARCHAR2(30),
WEBPAGE_URL VARCHAR2(256),
FAX VARCHAR2(30),
TOLL_FREE_PHONE VARCHAR2(30),
EMAIL VARCHAR2(100),
CONTACT_FAX VARCHAR2(30),
U_VENDOR_NAME VARCHAR2(1250),
MEMBERSHIP_LEVEL NUMBER(3),
constraint pk_dw_vendor primary key (vendor_id)
)
nologging
/

[Dave44]

Ok we have some work to do here.

are you trying to compare table definitions? or the data within a table for a given row? if it is the data, is it from updated rows or for new information being inserted to be compared to existing information?

as for what you have given, the select statement in the for loop is missing the l_col1 alias and the comma after it.

the execute immediate string is always going to return 0. the two columns will never be the same because you are prefixing them with different strings. it would be the same as saying where 1=2 (which is impossible) therefore nothing is returned, except max() forces oracle to return something (in this case null) and nvl(null,0) is 0.

nvl(max(1),2) is the same as nvl(1,2) since max(1) is always 1 and 1 is never null

i need a better definition of what you are trying to acheive please.

the two global variables being passed in are not being used