limit on size of plsql table 1

[ek03]

I am using a plsql table to store records within a procedure. This table is passed to an outside procedure as an in/out parameter. The second procedure adds records using the extend method on the table and then returns. At that point I sort the table but I am finding that the last records in the table raise a no_data_found error, as though they are being references after being deleted. The table.count matches table.last, so the records are there. Also, if I loop through the table and check if each record exists, when I do this in the inner procedure immediately before it returns, all records exist, but immediately after it returns, the outer procedure can't "see" some of the records. I would appreciate any help on this...

 

[sem]

There are a number of ways to loop through pl/sql table, but not all are correct. Pl/sql table differs from ordinary arra a bit:

declare
type t is table of number index by binary_integer;
m t;
i binary_integer;

begin
i := m.first;
while i is not null loop
i := m.next(i);
end loop;
end ;

The idea is that index may use negative numbers or contain gaps, so looping in a maner (for i=0;i<m.count;i++) may raise error or miss some values. Thus I'd recommend you to check your code more carefully. I doubt that m(m.last) may raise no_data_found, but m(m.count) may.
Regards, Dima

 

[ek03]

The table is not an index-by table (in your example, you were using a table declare as index by binary_integer) - for such a table, you are right. This is a nested table, so the indexes are incremental (1, 2, 3...) and you should only see a no_data_found if a specific record has been deleted.

 

[sem]

So how can you obtain no_data_found on LAST record?
You're probably perform extra step in your loop and index value exceedes table size. BTW what approximately is your table size? Regards, Dima

 

[ek03]

I don't know how I'm getting the error - I'm definitely within the bounds of the collection. The size of the table is between 20 and 200 records. Someone here at work gave me a workaround option - instead of using a plsql table, I am using a temporary table -

create global temporary table tempTable(id number)
on commit delete rows;

and this way, instead of passing the plsql table to the different procedures, I can just select and insert as I would to a normal table. As soon as the session issues a commit, the table is emptied (as if I did a plsqltable.delete). It works well for my needs.

 

[ek03]

I don't know how I'm getting the error - I'm definitely within the bounds of the collection. The size of the table is between 20 and 200 records. Someone here at work gave me a workaround option - instead of using a plsql table, I am using a temporary table -

create global temporary table tempTable(id number)
on commit delete rows;

and this way, instead of passing the plsql table to the different procedures, I can just select and insert as I would to a normal table. As soon as the session issues a commit, the table is emptied (as if I did a plsqltable.delete). It works well for my needs.

Thanks for your help!

 

[sem]

So did you get an error on your_tab(your_tab.last) or your_tab(your_tab.count)? Does your_tab.exists() return true? Regards, Dima

 

[ek03]

Hi.

The error was on your_tab(your_tab.last), which should have existed - in the called procedure, your_tab.exists returned true, but once it returned to the main procedure, exists returned a false.

 

[sem]

Can you send me your code to sam@eximb.com? I can not beleive that variable may be changed during returning to the called procedure. Do you have exception handler(s) in procedure(s)? Regards, Dima

 

[ek03]

I will send you a pared down version - it's a rather complicated process, but I can send you snippets of the relevant code. I was very surprised to see what was happening too - but I put in a dbms_output immediately before the called procedure returned and then immediately after, and sure enough, they were different.