-
problem with pl/sql tables
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.
-
If you've "index by binary_integer" you'd better declare j as binary_integer!
-
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;
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|