Subscript beyond count on nested table (trying to update a column in a trigger for the same table as trigger)

10 pts.
Tags:
Oracle 10g
Oracle triggers
Triggers
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; /

Answer Wiki

Thanks. We'll let you know when a new response is added.

That error usually arises when you try to access a non-existent element on a collection.

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:

<pre>for i in g_change_tab.first..g_change_tab.last loop
if <b>g_change_tab.exists(i)</b> then
select count(*)
into l_count
from ae_dt526;
– …
– …
– …
else
dbms_output.put_line(‘NON-EXISTENT ELEMENT: ‘||i);
end if;
end loop;</pre>

On the other hand, why do you need to extend the table before entering the loop ?

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following