CODEWITHSUNDEEP
oracle-databaseplsql
Mike Dimmick
Mike Dimmick

Reputation: 9802

How large an associative array can I pass into an Oracle stored procedure?

Several years ago I wrote an import procedure for an Oracle database, which loads data into a table. The data is passed into the procedure as three separate associative arrays. The import code is written in C# using ODP.Net, and the procedure in PL/SQL.

The customer is now reporting a problem where an expected row is missing from the table. What is the limit on the number of elements or the size of an associative array passed into a stored procedure?

The procedure and array declarations are:

TYPE ArrayOfStoreNumber IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
TYPE ArrayOfStoreName IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
TYPE ArrayOfStoreType IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;

PROCEDURE ImportDestinationStores(
    pStoreNumbers IN ArrayOfStoreNumber,
    pStoreNames IN ArrayOfStoreName,
    pStoreTypes IN ArrayOfStoreType,
    pCount IN BINARY_INTEGER
);

Upvotes: 1

Views: 2576

Answers (3)

Kuvick
Kuvick

Reputation: 48

For operations on large collections you should consider using the NOCOPY hint. Usually parameters are call by value (a copy of the actual variable is passed to the function/procedure) but the NOCOPY hint turns it into a call by reference (the actual variable is passed to the function/procedure). Same thing applies to the return value.

You just need to extend your procedures signature like this:

PROCEDURE ImportDestinationStores(
            pStoreNumbers IN NOCOPY ArrayOfStoreNumber,
            pStoreNames IN NOCOPY ArrayOfStoreName,
            pStoreTypes IN NOCOPY ArrayOfStoreType,
            pCount IN BINARY_INTEGER
);

But since you told us that the client is missing data, I wouldn't start searching here. ;-)

Upvotes: 0

Trinimon
Trinimon

Reputation: 13957

From what I can see in the documentation (Understanding PL/SQL Collection Types) it is unlimited. So it depends on the memory of your RDBMS.

Upvotes: 3

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

According Oracle documentation there is no limit. Of course, somewhere the size is limited but you should never reach such limit in reality.

Upvotes: 1

Related Questions