Toad World® Forums

Comparing Nested Table SQL datatype does not work


#1

I would like to test function that return Nested table sql datatype:

CREATE TYPE “TYP_CLCN” AS TABLE OF Varchar2(10)
/

AS an outcome I would like to comapre return value (nested table) with Query.

When I run a test I have got an error:
ORA-06533: Subscript beyond count

I have checked the code generated and found out that variable declared as :
l_exp_collection “TYP_CLCN” := TYP_CLCN();

but work with it like with pl/sql associative arrays without EXTENDING it:

OPEN l_cursor FOR ‘SELECT ‘‘a’’ FROM DUAL’;
LOOP
FETCH l_cursor INTO l_exp_collection (l_exp_collection.COUNT + 1);
EXIT WHEN l_cursor%NOTFOUND;
END LOOP;
CLOSE l_cursor;

What am I doing wrong?


#2

begin

OPEN l_cursor FOR ‘SELECT ‘‘a’’ FROM DUAL’;

FETCH l_cursor BULK COLLECT INTO l_exp_collection;

CLOSE l_cursor;

end;


#3

Hi, in order to let me reproduce exactly the same situation could you provide me with source code of tested function you use please?