Methods and Practices for Passing Array Parameters to Oracle Stored Procedures

Introduction

In Oracle database development, there is often a need to pass multiple data items of the same type to stored procedures. Traditional parameter-by-parameter passing becomes cumbersome and inflexible with large data volumes. Based on practical development requirements, this article systematically introduces two efficient array parameter passing solutions.

VARRAY Array Method

VARRAY (variable-size array) is a collection type provided by Oracle, which can be defined at the database level and used across sessions. First, create the VARRAY type:

CREATE OR REPLACE TYPE MyType AS VARRAY(200) OF VARCHAR2(50);

This statement defines a string array type with a maximum capacity of 200. Then create a stored procedure using this type:

CREATE OR REPLACE PROCEDURE testing(t_in MyType) IS
BEGIN
  FOR i IN 1..t_in.count LOOP
    dbms_output.put_line(t_in(i));
  END LOOP;
END;

The procedure traverses array elements via a FOR loop, where t_in.count retrieves the element count and t_in(i) accesses specific elements. Example invocation:

DECLARE
  v_t MyType;
BEGIN
  v_t := MyType();
  v_t.EXTEND(10);
  v_t(1) := 'this is a test';
  v_t(2) := 'A second test line';
  testing(v_t);
END;

Key steps include: initializing the array, extending capacity, and assigning elements. This method suits scenarios requiring persistent array definitions.

Package Associative Array Method

When independent database type creation is undesirable, associative arrays can be defined within packages. First, create the package specification:

CREATE OR REPLACE PACKAGE p IS
  TYPE p_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
  PROCEDURE pp(inp p_type);
END p;

p_type is defined as an index-by table type. Then implement the package body:

CREATE OR REPLACE PACKAGE BODY p IS
  PROCEDURE pp(inp p_type) IS
  BEGIN
    FOR i IN 1..inp.count LOOP
      dbms_output.put_line(inp(i));
    END LOOP;
  END pp;
END p;

Invocation directly uses the type defined in the package:

DECLARE
  v_t p.p_type;
BEGIN
  v_t(1) := 'this is a test of p';
  v_t(2) := 'A second test line for p';
  p.pp(v_t);
END;

This method eliminates separate type creation but requires all users to access the package.

Method Comparison and Selection Advice

The VARRAY method provides independent type definitions, suitable for scenarios requiring reuse across multiple procedures. The associative array method is encapsulated within packages, better suited for modular design. Developers should choose the appropriate solution based on project architecture and reuse requirements.