Error: $Oracle.EXCEPTION_ORA_6532: or ORA-06532: Subscript outside of limit

Error: $Oracle.EXCEPTION_ORA_6532: or ORA-06532: Subscript outside of limit

This error occurs when I try to fill data into "String_Table" (table of varchar) with extend. In details; I was getting this error when I tried to fill value into "codeTable" (variable of "String_Table") event I am doing extend immediately.

i := 0;

codeTable (i) := "Some Value"

codeTable.extend;

Above code will throw "ORA-06532: Subscript outside of limit"

Solution is "String_Table" need to be start filling from 1 not 0, so Correct code is

i := 1;

codeTable (i) := "Some Value"

codeTable.extend;

After giving couple of hours I found this and solved by using above solution (By changing "i" value 0 to 1)

Following are the example I have tested with Oracle 11g or Oracle 10g too. These are very important and very useful stored procedure example described How to use XML type and String Array in Oracle stored procedure.

OBJECT-1 (STRING_TABLE)

Create a global package which includes Ref Cursor and String_Table to store value to create IN Clause value or others

CREATE OR REPLACE PACKAGE GLOBAL_PACKAGE AS

TYPE REF_CUR IS REF CURSOR;

TYPE "STRING_TABLE" AS TABLE OF varchar2 (100) INDEX BY BINARY_INTEGER;

END GLOBAL_PACKAGE;

OBJECT-3 (STORED PROCEDURE-1)

Below stored procedure is an example to take a value (String_Array) as an Input parameter to create In Clause value for select query and a Output parameter (REF_CUR) that will return record set (No of rows from employee table) of employee data.

PROCEDURE SELECT_DATA_FROM_EMP

(

prmIds IN Global_Package.STRING_TABLE,

prmOutCursor OUT Global_Package.REF_CUR

)

IS

codeTable string_table := string_table(NULL);

i Number;

BEGIN

FOR i IN 1..prmIds.count LOOP --Never start loop with 0

codeTable (i) := prmIds(i);

codeTable.extend;

END LOOP;

Open prmOutCursor for

SELECT *

FROM employee

WHERE empid IN (select column_value from table(codeTable));

END SELECT_DATA_FROM_EMP;

OBJECT-4 (STORED PROCEDURE-2)

Sample xml to send from code for stored procedure, this XML format you can change as per your requirement but you have to change the XML node path in the stored procedure ("XMLTYPE.EXTRACT")

<ArrayList>

<Values><Value>1000</Value></Values>

<Values><Value>2000</Value></Values>

<Values><Value>3000</Value></Values>

<Values><Value>4000</Value></Values>

</ArrayList>

Below stored procedure is an example to take a value (XMLTYPE) as an Input parameter to create In Clause value for select query and a Output parameter (REF_CUR) that will return record set (No of rows from employee table) of employee data.

CREATE OR REPLACE PROCEDURE SELECT_DATA_FROM_EMP

(

prmXmlCode IN XMLTYPE,

prmOutCursor OUT Global_Package.REF_CUR

)

AS

codeTable string_table := string_table(NULL);

i Number := 1; --Never initialized with 0

BEGIN

FOR vRow IN

(

SELECT

XMLTYPE.EXTRACT (VALUE (Code), '/Value/text()').getstringval() AS iCode

FROM TABLE

(XMLSEQUENCE (prmXmlCode.EXTRACT ('ArrayList/Values/Value'))) Code)

LOOP

codeTable (i) := vRow. iCode;

i := i + 1;

codeTable.extend;

END LOOP;

OPEN prmOutCursor FOR

SELECT * FROM EMPLOYEE

WHERE EMPID IN (select column_value from table(codeTable));

END SELECT_DATA_FROM_EMP;

Note: If you have more than one select query in a single stored procedure and you are sending XMLTYPE as an input parameter, instead of writing XMLTYPE.EXTRACT in each SQL select statement, it is better to do XMLTYPE.EXTRACT once and keep the value into "String_Table" and use "String_Table" for all your SQL select statement like "select column_value from table(codeTable));"