How to pass array parameters into oracle stored procedure

There are various ways in which you could handle passing multiple records to oracle stored procedures. The old style of handling this was passing the values as xml and process the xml by the procedure. This adds lots of overhead to the oracle processors. An alternate to this is to pass the array in to the procedure and iterate and insert the values to the table. this approach may be more meaningful for the web applications with UI and middle tier separated out from database layer. This approach will significantly reduce the number of calls made to the database layer from middle tier. from the .Net development environment, ODP.Net gives good options to bind your parameter array to stored procedures . You could leverage that for better efficiency.

Steps i have scripted below are
1. Creating a table to store the values
2. Create a package with the type and the procedure to insert the values to table using the array.
3. PL SQL script to try executing the procedure from the sqlplus.

-- Drop the existing test table
Drop table CPM_TESTTABLE;
-- Create the new test table
Create table CPM_TESTTABLE
(COL_NAME varchar2(20) ,
COLN_NAME1 varchar2(10));

--Create the package with the type and the procedure
CREATE OR REPLACE PACKAGE ARRAY_PKG AS
TYPE char_array1 IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;

PROCEDURE SPM_INS_TESTTABLE (
pColName IN char_array1,
pColName1 IN char_array1);
END ARRAY_PKG;

CREATE or REPLACE package body ARRAY_PKG as
PROCEDURE SPM_INS_TESTTABLE
(
pColName IN char_array1,
pColName1 IN char_array1
)
IS

BEGIN

FOR I IN 1..PCOLNAME.COUNT
LOOP
INSERT INTO CPM_TESTTABLE
(COL_NAME,COLN_NAME1)
VALUES
(pColName(i),pColName1(i));

END LOOP;

EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END SPM_INS_TESTTABLE;

END ARRAY_PKG;

-- PL sql script to execute the procedure with array inout parameter
Declare
Customer_name ARRAY_PKG.char_array1;
Begin
-- Adding 10 rows to the array
for i in 1..10 Loop
customer_name(i) := 'Ciju '|| to_char(i);
End Loop;
delete from CPM_TESTTABLE ; -- Useful while re re-running the test
-- Call the procedure using array input
ARRAY_PKG.SPM_INS_TESTTABLE (customer_name , customer_name);
End;
commit;
SELECT * FROM CPM_TESTTABLE
-- End of script