Re: Pass arrays into stored procedures - oracle 8i
From: Per Forsgren <forsgren_at_home.sol.se>
Date: 13 Aug 2002 18:59:12 +0100
Message-ID: <3d5948f0_at_news.wineasy.se>
**/
TYPE Num_Array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
**/
END example_lib;
/
**/
) AS
Date: 13 Aug 2002 18:59:12 +0100
Message-ID: <3d5948f0_at_news.wineasy.se>
Hi.
There is no performance penalty in using array interface.
I can give you a framework for how to do this in a package.
The BINARY_INTEGER data type can be replaced with PLS_INTEGER in Oracle 8+
for (slightly) improved speed.......
Kind regards,
/Per
CREATE OR REPLACE PACKAGE example_lib AS
/***************************************************************************
***
Global declarations goes here
**/
TYPE Num_Array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
/***************************************************************************
***
Procedure: Whatever
**/
PROCEDURE Whatever(in_data IN Num_Array, /* Numerical array */ in_data_nbr IN BINARY_INTEGER /* Number of elements in array */ );
END example_lib;
/
CREATE OR REPLACE PACKAGE BODY example_lib AS
/***************************************************************************
***
Procedure: Whatever
**/
PROCEDURE Whatever(in_data IN Num_Array, /* Numerical array */ in_data_nbr IN BINARY_INTEGER /* Number of elements inarray */
) AS
BEGIN
- Do whatever....
NULL;
-- -- Error section -- EXCEPTION WHEN OTHERS THEN NULL; END Whatever; END example_lib;Received on Tue Aug 13 2002 - 19:59:12 CEST
/
"jpl" <jonna311_at_yahoo.com> wrote in message news:bd19caaa.0208130134.9de38b6_at_posting.google.com... > Hi > I am trying to write a stored procedure where i need to pass in an > array of userids. This array needs be of dynamic size. Can we declare > an array of variant size in a stored procedure ? Can anyone please > tell me how to do it and how the performance will be ? > > Thanks in advance