Hi Tom,

Thanks for your response on rowpointer in a refcursor. It cleared my doubts.

I have a problem like this :
1. I have a Varray as below :

CREATE OR REPLACE TYPE str_test AS object(
item_NAME VARCHAR2(40),
sale_price NUMBER(15,6));

CREATE OR REPLACE TYPE pra_test IS VARRAY(10000) OF str_test;

2. I wrote a anonymous block to load data into the structure like this :

DECLARE
la_pra_Test pra_test := pra_test(); -- holds array of ln_indx number := 1;
begin
FOR ln_indx in 1..10001 LOOP
la_pra_test.EXTEND();
--dbms_output.put_line('ln_indx ='||ln_indx);
la_pra_test(ln_indx) :=
str_test('ITEM'||ln_indx,
ln_indx||01.00);

END LOOP;
end;

here the problem is if ln_indx = 10000, the block executes fine, but if it is 10001 it gives ora-06532 subscript outside of limit. Could you please let me know how is the limit defined ?

The other day i had a bigger structure and i was populating data from a table and it failed at ln_indx = 500, is it anything to do with memory defined in oracle ?

3. One more related problem is :
i would like to select all employees whose name is in a table type:
i have read the earlier questions, it works fine if used in explicit sql statements, but my requirement is , the sql statement is a dynamic one and i am executing using execute immediate it is not working ?

if query is like this :
table_type_variable is loaded with the empno's

select * from emp where
empno in (select * from THE(select CAST(table_type_vaiable)
from dual)

if my variable is like this:

var1 := 'insert into emp_test select * from emp where
empno in (select * from THE (select '|| CAST(table_type_variable) '|| ' from dual)';
execute immediate var1;

your answers will help me to know more fundamentals as allways.
Thanks
Chaitanya