problem with pl/sql tables

[prasen999]

hi,
i am trying to insert a value in a pl/sql table inside a cursor. the code is as below:

create or replace procedure err_logging(p_owner varchar2)
--declare
--p_owner varchar2(20):= 'DRA';
is

TYPE rec_diff_cols IS table of varchar2(20)
index by binary_integer;
rec_cols rec_diff_cols;
j number := 1;
.
.
.
begin
..........
........
for i in cur_diff_cols_tab_mview(c_rec.dra_mview_name,c_rec.src_tblname)
loop
l_cols := i.column_name;
rec_cols(j) := l_cols;
--fetch cur_diff_cols_mview_tab into rec_cols;
j:=j+1;
end loop;

here when i am debugging the code i am getting this in TOAD...

undeclared identifier rec_cols..and no value is going into that I guess.
KIndly help ASAP.

regards.

[DaPi]

If you've "index by binary_integer" you'd better declare j as binary_integer!

[prasen999]

thanks, but it doesn't work.

this is the entire code, if possible just have a look.


create or replace procedure err_logging(p_owner varchar2)
--declare
--p_owner varchar2(20):= 'DRA';
is

TYPE rec_diff_cols IS table of varchar2(20)
index by binary_integer;
rec_cols rec_diff_cols;

cursor cur_sel_tbl_mviews is select owner,src_tblname,dra_mview_name from resync_src_master where owner = p_owner;

cursor cur_diff_cols_tab_mview(l_mview_name varchar2,l_tbl_name varchar2) is
select column_name from dba_tab_cols where owner = p_owner and table_name = l_tbl_name
minus
select column_name from dba_tab_cols where owner = p_owner and table_name = l_mview_name;

cursor cur_diff_cols_mview_tab(l_mview_name varchar2,l_tbl_name varchar2) is
select column_name from dba_tab_cols where owner = p_owner and table_name = l_mview_name
minus
select column_name from dba_tab_cols where owner = p_owner and table_name = l_tbl_name;



l_mview_cnt_col_name number(3);
l_tbl_cnt_col_name number(3);
j binary_integer := 0;
l_cols varchar2(20);

begin

for c_rec in cur_sel_tbl_mviews
loop
select count(column_name) into l_mview_cnt_col_name from dba_tab_cols where owner = p_owner and table_name = c_rec.dra_mview_name;
select count(column_name) into l_tbl_cnt_col_name from dba_tab_cols where owner = p_owner and table_name = c_rec.src_tblname;
IF l_mview_cnt_col_name > l_tbl_cnt_col_name THEN

for i in cur_diff_cols_mview_tab(c_rec.dra_mview_name,c_rec.src_tblname)
loop
l_cols := i.column_name;
rec_cols(j) := l_cols;
--fetch cur_diff_cols_mview_tab into rec_cols;
j:=j+1;
end loop;
forall j in rec_cols.first..rec_cols.last
insert into uiiadmin values (p_owner, c_rec.src_tblname,rec_cols(j),'DELETED', 'Replication must be done');
raise_application_error(-20101, '''Columns deleted from '||p_owner||'.'||c_rec.src_tblname||', check UIIADMIN table for details''');

ELSIF l_mview_cnt_col_name < l_tbl_cnt_col_name THEN

for i in cur_diff_cols_tab_mview(c_rec.dra_mview_name,c_rec.src_tblname)
loop
l_cols := i.column_name;
rec_cols(j) := l_cols;
--fetch cur_diff_cols_mview_tab into rec_cols;
j:=j+1;
end loop;
forall j in rec_cols.first..rec_cols.last
insert into uiiadmin values (p_owner, c_rec.src_tblname,rec_cols(j),'INSERTED', 'Replication may be needed');
raise_application_error(-20101, '''Columns inserted into '||p_owner||'.'||c_rec.src_tblname||', check UIIADMIN table for details''');

END IF;
end loop;
end;

[prasen999]

thanks a lot, got the solution quite some time back, but didn't find time to shoot the mail. the thing was working fine, only i cudn't see it in the table after the insert as i hadn't committed the transaction.

thanks and regards.