Cursor Variables vs. Collections...

[Susan]

Hi there,

I'm just wondering what the advantages of using cursor variables are over using collection types(eg: pl/sql tables) to handle multi-row queries(I would be passing the results back to a Pro*C application).

I was working on a system which uses pl/sql tables(
TYPE CHAR_ARRAY IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER
but I'm thinking of switching to cursor variables for a new system. Also - is there a 32K limit on the amount of data which can be passed from a pl/sql stored program to a Pro*C
application(I have been told that there is but can't seem to find any documentation on it)and if so would cursor variables avoid having to make repeated calls to the pl/sql
code(if the data was over 32K) as it is simply a pointer to the data?

Thanks in advance for any advice/suggestions...
Susan.

 

[CRoberts]

Depending on what you want, an Index-By table (or PL/SQL table) is stored in memory. So, for quick access (or persistance if in a Package for a session) an Index-By table is great because multiple calls to the database are eliminated once you data is retrieved. If only one call is being made and you do not need to access a row previously called, then cursor variables are probably easier to implement and use.

If you are using a Long or Long Raw, then 32k is you upper limit. PL/SQL variables are difference from those in the database and can therefore have different maximum sizes. Any attempt to load a too large data element into a PL/SQL variable will give an error. Clobs and Blobs are a difference story, there are pointers, not the data itself. If loading or manipulating these datatypes, then you must use the DBMS_LOB package.