Can I slice a PL/SQL collection?

Question

I've got a PL/SQL VArray that I'm filling with a BULK COLLECT query like this:

SELECT id
BULK COLLECT INTO myarray
FROM aTable

Now I'd like to pass a slice of this collection into another collection, something like this:

newarray := myarray(2..5)

This should pass the elements 2,3,4 and 5 from myarray to newarray.

I could write a loop and copy the elements, but is there a more compact way to do this?

Why would you want to do this instead of another more selective select? — David, Nov 05 '09 at 13:27
Because I want to determine the biggest "slice" of a certain condition. The check is in a more complicated part of the code, and I call it with smaller and smaller slices. — Thorsten, Nov 05 '09 at 15:29

score 3 · Answer 1 · answered Oct 29 '09 at 16:01

3

Generally, you don't want to do this. You have a large collection in memory, and now you want to make a copy of it. That would use even more memory. Usually in cases like this you pass the entire collection around (by reference, not value) and also provide a start and stop index. Leave it to the other functions to only process the range specified.

answered Oct 29 '09 at 16:01

Adam Hawkes

7,218
30
57

Well, for my specific use case I don't really see a problem as I'll be mostly dealing with pretty small collections (<10 entries - I omitted the where criteria in my query). The small memory/runtime overhead of doing the copy seems to me smaller than the code complexity it would introduce. – Thorsten Oct 29 '09 at 18:08
1

Not pointers exactly, but you can at a `NOCOPY` hint on a parameter. See http://www.dba-oracle.com/t_plsql_passing_data_structures_nocopy.htm – Adam Hawkes Oct 29 '09 at 19:02

Can I slice a PL/SQL collection?

1 Answers1