Q:
Subscript beyond count on nested table (trying to update a column in a trigger for the same table as trigger)
CREATE OR REPLACE PACKAGE trigger_api AS
PROCEDURE tab1_row_change (p_id IN ae_dt526.field1%TYPE,
p_action IN varchar2,
p_field13 IN varchar2,
p_blanket_ind IN varchar2
);
PROCEDURE tab1_statement_change;
END trigger_api;
/
CREATE OR REPLACE PACKAGE BODY trigger_api AS
TYPE t_change_rec is record (
field1 ae_dt526.field1%TYPE,
action tab1_audit.action%TYPE,
field13 tab1_audit.field13%TYPE,
blanket_ind tab1_audit.blanket_ind%TYPE
);
TYPE t_change_tab is table of t_change_rec;
g_change_tab t_change_tab := t_change_tab();
PROCEDURE tab1_row_change (p_id IN ae_dt526.field1%TYPE,
p_action IN varchar2,
p_field13 IN varchar2,
p_blanket_ind IN varchar2) IS
BEGIN
g_change_tab.extend;
g_change_tab(g_change_tab.last).field1 := p_id;
g_change_tab(g_change_tab.last).action := p_action;
g_change_tab(g_change_tab.last).field13 := p_field13;
g_change_tab(g_change_tab.last).blanket_ind := p_blanket_ind;
END tab1_row_change;
procedure tab1_statement_change is
l_count number(10);
v_blanket_ind varchar2(1);
begin
g_change_tab.extend;
--for i in g_change_tab.first..g_change_tab.last loop
-- I am getting the subscript beyond count error here
-- I realy need the first and last values to work.
for i in 1..6 loop
select count(*)
into l_count
from ae_dt526;
if i <= l_count then
select NVL(fpbpohd_blanket_ind,'N')
into v_blanket_ind
from fpbpohd
where fpbpohd_code = g_change_tab(i).field13;
g_change_tab(i).blanket_ind := v_blanket_ind;
--v_blanket_ind := 'N';
insert into tab1_audit(field1, action, field13, record_count,created_time, blanket_ind)
VALUES (g_change_tab(i).field1,g_change_tab(i).action, g_change_tab(i).field13,l_count,
sysdate, g_change_tab(i).blanket_ind);
end if;
end loop;
g_change_tab.delete;
end tab1_statement_change;
END trigger_api;
/
CREATE OR REPLACE TRIGGER tab1_aru_trg
after update on ae_dt526
for each row
begin
trigger_api.tab1_row_change(p_id=>:new.field1,p_action=>'UPDATE',p_field13=>:new.field13,p_blanket_ind=>:new.field20);
end;
/
show errors
CREATE OR REPLACE TRIGGER tab1_asu_trg
AFTER UPDATE ON ae_dt526
BEGIN
trigger_api.tab1_statement_change;
END;
/
PROCEDURE tab1_row_change (p_id IN ae_dt526.field1%TYPE,
p_action IN varchar2,
p_field13 IN varchar2,
p_blanket_ind IN varchar2
);
PROCEDURE tab1_statement_change;
END trigger_api;
/
CREATE OR REPLACE PACKAGE BODY trigger_api AS
TYPE t_change_rec is record (
field1 ae_dt526.field1%TYPE,
action tab1_audit.action%TYPE,
field13 tab1_audit.field13%TYPE,
blanket_ind tab1_audit.blanket_ind%TYPE
);
TYPE t_change_tab is table of t_change_rec;
g_change_tab t_change_tab := t_change_tab();
PROCEDURE tab1_row_change (p_id IN ae_dt526.field1%TYPE,
p_action IN varchar2,
p_field13 IN varchar2,
p_blanket_ind IN varchar2) IS
BEGIN
g_change_tab.extend;
g_change_tab(g_change_tab.last).field1 := p_id;
g_change_tab(g_change_tab.last).action := p_action;
g_change_tab(g_change_tab.last).field13 := p_field13;
g_change_tab(g_change_tab.last).blanket_ind := p_blanket_ind;
END tab1_row_change;
procedure tab1_statement_change is
l_count number(10);
v_blanket_ind varchar2(1);
begin
g_change_tab.extend;
--for i in g_change_tab.first..g_change_tab.last loop
-- I am getting the subscript beyond count error here
-- I realy need the first and last values to work.
for i in 1..6 loop
select count(*)
into l_count
from ae_dt526;
if i <= l_count then
select NVL(fpbpohd_blanket_ind,'N')
into v_blanket_ind
from fpbpohd
where fpbpohd_code = g_change_tab(i).field13;
g_change_tab(i).blanket_ind := v_blanket_ind;
--v_blanket_ind := 'N';
insert into tab1_audit(field1, action, field13, record_count,created_time, blanket_ind)
VALUES (g_change_tab(i).field1,g_change_tab(i).action, g_change_tab(i).field13,l_count,
sysdate, g_change_tab(i).blanket_ind);
end if;
end loop;
g_change_tab.delete;
end tab1_statement_change;
END trigger_api;
/
CREATE OR REPLACE TRIGGER tab1_aru_trg
after update on ae_dt526
for each row
begin
trigger_api.tab1_row_change(p_id=>:new.field1,p_action=>'UPDATE',p_field13=>:new.field13,p_blanket_ind=>:new.field20);
end;
/
show errors
CREATE OR REPLACE TRIGGER tab1_asu_trg
AFTER UPDATE ON ae_dt526
BEGIN
trigger_api.tab1_statement_change;
END;
/
ASKED:
Feb 1 2009 11:39 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
To debug this, you could place all the code inside the for loop in an if-then-else block to make sure you are referencing only existent elements.
Something like this:
for i in g_change_tab.first..g_change_tab.last loop
if g_change_tab.exists(i) then
select count(*)
into l_count
from ae_dt526;
-- ...
-- ...
-- ...
else
dbms_output.put_line('NON-EXISTENT ELEMENT: '||i);
end if;
end loop;
On the other hand, why do you need to extend the table before entering the loop ?
