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

Original text of this message