Home » SQL & PL/SQL » SQL & PL/SQL » VARRAY Issue (Oracle Database 11g 11.2.0.2.0 - 64bit, Unix)
VARRAY Issue [message #584557] Wed, 15 May 2013 07:52 Go to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3099
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3099
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 10960
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
select * from v$version;
for both DBs.
Re: VARRAY Issue [message #584564 is a reply to message #584563] Wed, 15 May 2013 10:10 Go to previous messageGo to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

For me also the second case is running fine without any issue hence giving the result 1.... everytime.

So there appears to be some othere problem there.

[Updated on: Wed, 15 May 2013 10:10]

Report message to a moderator

Re: VARRAY Issue [message #584565 is a reply to message #584563] Wed, 15 May 2013 10:21 Go to previous messageGo to next message
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 #584569 is a reply to message #584565] Wed, 15 May 2013 10:47 Go to previous messageGo to next message
cookiemonster
Messages: 10960
Registered: September 2008
Location: Rainy Manchester
Senior Member
I suggest you contact oracle support - looks very much like an oracle bug.
Re: VARRAY Issue [message #584582 is a reply to message #584569] Wed, 15 May 2013 15:45 Go to previous messageGo to next message
ThomasG
Messages: 3099
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 #584614 is a reply to message #584557] Thu, 16 May 2013 04:19 Go to previous messageGo to next message
sap_arul
Messages: 27
Registered: November 2005
Location: BANGALORE
Junior Member
Tried the same. But no luck Sad
Re: VARRAY Issue [message #584621 is a reply to message #584614] Thu, 16 May 2013 06:23 Go to previous messageGo to next message
cookiemonster
Messages: 10960
Registered: September 2008
Location: Rainy Manchester
Senior Member
Time to contact oracle support.
Re: VARRAY Issue [message #584764 is a reply to message #584557] Fri, 17 May 2013 08:18 Go to previous messageGo to next message
mnitu
Messages: 140
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 59113
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Dynamic SQL binding problems
Next Topic: NULL in Left Joins
Goto Forum:
  


Current Time: Wed Sep 17 13:07:17 CDT 2014

Total time taken to generate the page: 0.12887 seconds