VARRAY Issue [message #584557] |
Wed, 15 May 2013 07:52  |
sap_arul
Messages: 27 Registered: November 2005 Location: BANGALORE
|
Junior Member |
|
|
Hi,
When we run the below program in our testing schema we are getting the ORA-06532: Subscript outside of limit error, where as in our development enviornment it is working fine without any errors.
Both schema's are in different instance but same oracle version.
Can i know any oracle parameter needs to be tuned?
declare
-- Local variables here
i integer;
function test return number is
Type l_spicalChar_array IS VARRAY(6) OF VARCHAR2(10);
l_spchar l_spicalChar_array;
begin
l_spchar := l_spicalChar_array('&');
return 1;
end test;
begin
-- Test statements here
for x in 1 .. 7 loop
i := test;
END LOOP;
exception
when others then
dbms_output.put_line('When others ' || sqlerrm);
end;
When others ORA-06532: Subscript outside of limit
|
|
|
Re: VARRAY Issue [message #584558 is a reply to message #584557] |
Wed, 15 May 2013 08:10   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
The code you posted can't raise that error.
It runs fine for me:
SQL> declare
2 -- Local variables here
3 i integer;
4
5 function test return number is
6 Type l_spicalChar_array IS VARRAY(6) OF VARCHAR2(10);
7 l_spchar l_spicalChar_array;
8 begin
9 l_spchar := l_spicalChar_array('&');
10
11 return 1;
12 end test;
13 begin
14 -- Test statements here
15 for x in 1 .. 7 loop
16 i := test;
17 END LOOP;
18 exception
19 when others then
20 dbms_output.put_line('When others ' || sqlerrm);
21 end;
22 /
PL/SQL procedure successfully completed.
SQL>
The first thing you should to is get rid of the exception block. It does absolutely nothing except hiding possible errors.
Then post the SQLPlus session where you actually get the error. My "guess" at the moment is that you defined an array with 6 entries ( VARRAY(6) ) put are trying to put 7 variables into it. ( for x in 1 .. 7 )
|
|
|
Re: VARRAY Issue [message #584559 is a reply to message #584558] |
Wed, 15 May 2013 08:15   |
sap_arul
Messages: 27 Registered: November 2005 Location: BANGALORE
|
Junior Member |
|
|
Thomas,
Problem is the same code is working in one environment but not working in other enviornment. Looks like variable l_spchar is not re-inializing everytime when we call the function test. But want to know why it is working in some environment.
|
|
|
Re: VARRAY Issue [message #584561 is a reply to message #584559] |
Wed, 15 May 2013 08:40   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
It has absolutely nothing to to with any "environment" the error happens when you try to extend the array to beyond the declared limit.
~ $ oerr ORA 06532
06532, 00000, "Subscript outside of limit"
// *Cause: A subscript was greater than the limit of a varray
// or non-positive for a varray or nested table.
// *Action: Check the program logic and increase the varray limit
// if necessary.
//
See the difference between these two, one works the other raises your error:
SQL> declare
2 i integer;
3
4 function test return number is
5 Type l_spicalChar_array IS VARRAY(6) OF VARCHAR2(10);
6 l_spchar l_spicalChar_array;
7 begin
8 l_spchar := l_spicalChar_array();
9 l_spchar.extend(6);
10 RETURN 1;
11 end test;
12 begin
13 i := test;
14 end;
15 /
declare
PL/SQL procedure successfully completed.
SQL> SQL> SQL> 2 -- Local variables here
3 i integer;
4
5 function test return number is
6 Type l_spicalChar_array IS VARRAY(6) OF VARCHAR2(10);
7 l_spchar l_spicalChar_array;
8 begin
9 l_spchar := l_spicalChar_array();
10 l_spchar.extend(7);
11 RETURN 1;
12 end test;
13 begin
14 i := test;
15 end;
16 /
declare
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at line 10
ORA-06512: at line 14
|
|
|
Re: VARRAY Issue [message #584562 is a reply to message #584561] |
Wed, 15 May 2013 09:31   |
sap_arul
Messages: 27 Registered: November 2005 Location: BANGALORE
|
Junior Member |
|
|
Hi,
you can see the output from the two instance. Still same problem.
SQL> SHOW USER
USER is "TESTREAD"
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
TEST11G1
SQL> SET SERVEROUTPUT ON
SQL> declare
2 -- Local variables here
3 i integer;
4
5 function test return number is
6 Type l_spicalChar_array IS VARRAY(6) OF VARCHAR2(10);
7 l_spchar l_spicalChar_array;
8 begin
9 l_spchar := l_spicalChar_array('&');
10 dbms_output.put_line(l_spchar.COUNT);
11 return 1;
12 end test;
13
14 begin
15 -- Test statements here
16 for x in 1 .. 7 loop
17 i := test;
18 END LOOP;
19 EXCEPTION
20 WHEN OTHERS THEN
21 DBMS_OUTPUT.PUT_LINE('WHEN OTHERS ' || SQLERRM); -- WEBSERVICES ISSUE T
EMP
22 END;
23 /
1
2
3
4
5
6
WHEN OTHERS ORA-06532: Subscript outside of limit
PL/SQL procedure successfully completed.
SQL> CONNECT DEVDB01/****@DEV11R;
Connected.
SQL> SHOW USER
USER is "DEVDB01"
SQL> SET SERVEROUTPUT ON
SQL> declare
2 -- Local variables here
3 i integer;
4
5 function test return number is
6 Type l_spicalChar_array IS VARRAY(6) OF VARCHAR2(10);
7 l_spchar l_spicalChar_array;
8 begin
9 l_spchar := l_spicalChar_array('&');
10 dbms_output.put_line(l_spchar.COUNT);
11 return 1;
12 end test;
13
14 begin
15 -- Test statements here
16 for x in 1 .. 7 loop
17 i := test;
18 END LOOP;
19 EXCEPTION
20 WHEN OTHERS THEN
21 DBMS_OUTPUT.PUT_LINE('WHEN OTHERS ' || SQLERRM); -- WEBSERVICES ISSUE T
EMP
22 END;
23 /
1
1
1
1
1
1
1
PL/SQL procedure successfully completed.
SQL>
|
|
|
Re: VARRAY Issue [message #584563 is a reply to message #584562] |
Wed, 15 May 2013 09:52   |
cookiemonster
Messages: 13967 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You appear to have a scoping bug.
The array is local to the function and so should only exist while the function is being run.
As soon as the function returns it should be scrubed from existence.
Which is what is happening in the 2nd run - hence the row count of 1 each time
The first run appears to be persisting the array across function calls - so the row count increments until it hits the limit of 6.
Are both DB's the exact same version of oracle? Are they both running on the same OS?
Post results of
for both DBs.
|
|
|
|
Re: VARRAY Issue [message #584565 is a reply to message #584563] |
Wed, 15 May 2013 10:21   |
sap_arul
Messages: 27 Registered: November 2005 Location: BANGALORE
|
Junior Member |
|
|
Both are running in different server, but oracle version is same.
SQL> select * from v$version;
BANNER
-----------------------------------------
Oracle Database 11g Enterprise Edition Re
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Produ
NLSRTL Version 11.2.0.2.0 - Production
|
|
|
|
Re: VARRAY Issue [message #584582 is a reply to message #584569] |
Wed, 15 May 2013 15:45   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
I would also strongly suggest to run it without the exception handler, as I already said.
Then you might get a more conclusive error stack that might show something weird going on.
|
|
|
|
|
Re: VARRAY Issue [message #584764 is a reply to message #584557] |
Fri, 17 May 2013 08:18   |
mnitu
Messages: 159 Registered: February 2008 Location: Reims
|
Senior Member |
|
|
Check plsql_optimize_level parameter. It has value 3 in the database where the exception arrived.
|
|
|
Re: VARRAY Issue [message #586898 is a reply to message #584764] |
Tue, 11 June 2013 09:30   |
sap_arul
Messages: 27 Registered: November 2005 Location: BANGALORE
|
Junior Member |
|
|
Thanks mnitu, you are right plsql_optimize_level parameter value was 3. Now i changed to 2, then issue is resolved. Thanks.
Regards,
Arul.
|
|
|
Re: VARRAY Issue [message #586906 is a reply to message #586898] |
Tue, 11 June 2013 10:38  |
 |
Michel Cadot
Messages: 68763 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: 20 WHEN OTHERS THEN
21 DBMS_OUTPUT.PUT_LINE('WHEN OTHERS ' || SQLERRM); -- WEBSERVICES ISSUE T
EMP
This is the BIGGEST bug you can write in PL/SQL.
Read WHEN OTHERS.
Regards
Michel
|
|
|