Hi
I have the following PLSQL code - if run 1st time - it works fine - running 2nd or 3rd time it fails with "Subscript beyond count" error
If I make the declaration of g_response private to the procedure (not globally in the package) - it works fine
Please can you explain why it doesn't work if g_response if declared globally - and why does it work ok first time and then fail ?
-----------------------------------------------------------------------------------------
set serverout on size 999999;
-----------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE MK_RESPONSE_OBJ AS OBJECT (
line_number number,
return_code number,
return_msg varchar2(4000)
);
/
show errors
-----------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE MK_RESPONSE_TAB_TYPE FORCE AS TABLE OF MK_RESPONSE_OBJ;
/
show errors
-----------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE MK_TEST AS
PROCEDURE test;
END;
/
show errors
-----------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY MK_TEST AS
g_response mk_response_tab_type := mk_response_tab_type();
g_error_count NUMBER := 0;
PROCEDURE test IS
BEGIN
g_response.delete;
g_response.extend;
g_response := mk_response_tab_type();
g_error_count := g_error_count + 1;
g_response.extend;
g_response(g_error_count) := mk_response_obj(1, 1, 'test1');
dbms_output.put_line('Populated array element '||g_error_count||' - this is working OK');
g_error_count := g_error_count + 1;
g_response.extend;
g_response(g_error_count) := mk_response_obj(1, 2, 'test2');
dbms_output.put_line('Populated array element '||g_error_count||' - this is working OK');
g_error_count := g_error_count + 1;
g_response.extend;
g_response(g_error_count) := mk_response_obj(2, 1, 'test3');
dbms_output.put_line('Populated array element '||g_error_count||' - this is working OK');
g_error_count := g_error_count + 1;
g_response.extend;
g_response(g_error_count) := mk_response_obj(3, 1, 'test4');
dbms_output.put_line('Populated array element '||g_error_count||' - this is working OK');
g_error_count := g_error_count + 1;
g_response.extend;
g_response(g_error_count) := mk_response_obj(3, 2, 'test5');
dbms_output.put_line('Populated array element '||g_error_count||' - this is working OK');
END;
END;
/
show errors;
-----------------------------------------------------------------------------------------
DECLARE
x number;
BEGIN
dbms_output.put_line('Running test first time');
MK_TEST.test;
END;
/
-----------------------------------------------------------------------------------------
DECLARE
x number;
BEGIN
dbms_output.put_line('Running test second time');
MK_TEST.test;
END;
/
-----------------------------------------------------------------------------------------
DECLARE
x number;
BEGIN
dbms_output.put_line('Running test third time');
MK_TEST.test;
END;
/
Many Thanks
Malcolm
You've re-initialized the g_response array. But not the g_error_count index variable!
So the second time you run this, you're trying to assign a value in position 6. To an array with one element.
CREATE OR REPLACE PACKAGE BODY MK_TEST AS
g_response mk_response_tab_type := mk_response_tab_type();
g_error_count NUMBER := 0;
PROCEDURE test IS
BEGIN
g_response.delete;
g_response.extend;
g_response := mk_response_tab_type();
g_error_count := g_error_count + 1;
g_response.extend;
dbms_output.put_line(
'Adding element ' || g_error_count || ' into array size ' || g_response.count
);
g_response(g_error_count) := mk_response_obj(1, 1, 'test1');
dbms_output.put_line('Populated array element '||g_error_count||' - this is working OK');
g_error_count := g_error_count + 1;
g_response.extend;
g_response(g_error_count) := mk_response_obj(1, 2, 'test2');
dbms_output.put_line('Populated array element '||g_error_count||' - this is working OK');
END;
END;
/
DECLARE
x number;
BEGIN
dbms_output.put_line('Running test first time');
MK_TEST.test;
END;
/
Running test first time
Adding element 1 into array size 1
Populated array element 1 - this is working OK
Populated array element 2 - this is working OK
DECLARE
x number;
BEGIN
dbms_output.put_line('Running test second time');
MK_TEST.test;
END;
/
Running test second time
Adding element 3 into array size 1
ORA-06533: Subscript beyond count
ORA-06512: at "CHRIS.MK_TEST", line 21Fix this by re-initializing g_error_count at the start of the procedure. Or moving it's declaration inside the procedure:
CREATE OR REPLACE PACKAGE BODY MK_TEST AS
g_response mk_response_tab_type := mk_response_tab_type();
PROCEDURE test IS
g_error_count NUMBER := 0;
BEGIN
g_response.delete;
g_response.extend;
g_response := mk_response_tab_type();
g_error_count := g_error_count + 1;
g_response.extend;
dbms_output.put_line(
'Adding element ' || g_error_count || ' into array size ' || g_response.count
);
g_response(g_error_count) := mk_response_obj(1, 1, 'test1');
dbms_output.put_line('Populated array element '||g_error_count||' - this is working OK');
g_error_count := g_error_count + 1;
g_response.extend;
g_response(g_error_count) := mk_response_obj(1, 2, 'test2');
dbms_output.put_line('Populated array element '||g_error_count||' - this is working OK');
END;
END;
/
DECLARE
x number;
BEGIN
dbms_output.put_line('Running test first time');
MK_TEST.test;
END;
/
Running test first time
Adding element 1 into array size 1
Populated array element 1 - this is working OK
Populated array element 2 - this is working OK
DECLARE
x number;
BEGIN
dbms_output.put_line('Running test second time');
MK_TEST.test;
END;
/
Running test second time
Adding element 1 into array size 1
Populated array element 1 - this is working OK
Populated array element 2 - this is working OK