Accessing arrays returned by Oracle Stored Procedures using Oracle

microsoft.public.dotnet.framework

18 Nov 2007 4:06 PM

Kumar.A.P.P

Hi All,
We are working on a web application being developed in Visual Studio 2005
with
oracle 9i database. One of our oracle stored procedure returns table objects.
While executing this stored procedure using OracleCommand
(System.Data.OracleClient)
object, we are having difficulty in adding an output parameter corresponding
to
this, to the OracleCommand object. Appreciate any solution for the same.

Please find below the code snippet of the stored procedure:

CREATE OR REPLACE PACKAGE Investment_Component AS
TYPE NumArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
...
PROCEDURE CTE_INVEST_BALANCE_STATUS
                    (cur_dt_in IN DATE, -- current system business date
                     ccy_in IN VARCHAR2, -- currency code selection
                     lob_in IN VARCHAR2, -- Line of business code selection
                     t_date_cur IN OUT t_cursor,
                     ArraySize IN INTEGER,
                     BalAmtArr OUT NumArray,
                     InvAmtArr OUT NumArray,
                     DiffAmtArr OUT NumArray,
                     err_msg_inout IN OUT VARCHAR2);
END Investment_Component;

--
Kumar.A.P.P
Software Engineer, Infosys Technologies Limited.
http://techaid.wordpress.com/