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>

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 in

array */
) AS

BEGIN

---

• Do whatever....

  ---

  NULL;

 RETURN;

--
-- Error section
--
EXCEPTION

  WHEN OTHERS THEN

   NULL;

END Whatever;
END example_lib;


/






"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

Received on Tue Aug 13 2002 - 19:59:12 CEST