In my ASP.NET page I have a list box.  For each row in the list box, I need to invoke an Oracle SP that inserts some data. How would I go about passing the contents of the list box collection to the Stored procedure so that I can loop through it?

Hi,

  If you're using Oracle's ODP.net provider, it allows you to pass PLSQL Associative Arrays, which would be the equivalent of passing an array of strings. 

  I don't believe Microsoft's ODP has that functionality though so if you're using it you'd probably need to concatenate the values into comma delimited string for example and then breaking it back up inside the stored procedure.

Hope it helps, corrections/comments welcome.
Greg

 yes u can pass items in form of array.  dsnt matter what provider u are using. its just the implementation of SP. use looping in SP to insert data.

Hope it helps. 

Hi Manas,

  Do you happen to have a code snippet of that, using System.Data.OracleClient?  I was under the impression that passing PLSQL associative arrays was Oracle ODP.net specific functionality.  I briefly tried, and wasnt able to get it to work using OracleClient. 

  Here's an example using Oracle's ODP:

http://www.oracle.com/technology/sample_code/tech/windows/odpnet/howto/arraybind/index.html

Cheers
Greg

you just bind some variables and fetch each row into those variables and then push them into an array.

Accept the array as a 'PL/SQL table' in the called procedure.

create type v_array2 as varray(2) of varchar(25);
create or replace procedure test(name_in in v_array2)
as
begin
for i in 1..2
loop
insert into worker
(name,age,lodging)
values
(name_in(i),null,null);
end loop;
commit;
end;

/

Hi,

  Actually, its the .NET snippet I was looking for, that shows System.Data.OracleClient calling the above procedure you've provided, as an example. 

  With respect to Oracle's ODP, the ability to call a proc with a PLSQL Associative Array has been around for a while.  Calling the above procedure though which takes a UDT (user defined type) requires the very latest version.

  I pasted the wrong link above with respect to passing an array into a procedure, here's the one I meant to post.
     http://www.oracle.com/technology/oramag/oracle/07-jan/o17odp.html

Cheers,
Greg

 See my post here..

http://forums.asp.net/t/1247854.aspx

 hope it helps.
 

No im not talking about a ref cursor parameter.  I am talking about an associative array parameter.  Is there a way to pass it through .net code?

I am having the same problem - tried to pass input parameter to oracle stored procedure thru System.Data.OracleClient.

Tried using associative array and varray as a parameter type but it doesn't work.

I thing that Manas is not understanding what the problem is.

It is NOT how to declare associative array as a parameter in a stored procedure. 

It is NOT how to get a cursor back as an output parameter.

It IS how to pass an array from .Net as an INPUT parameter by using System.Data.OracleClient.

Does somebody knows where can I get the oracle data provider?

Can anybody provide solution for this. I have been working on the same issue with no luck.

thanks!

 Hi,

I am currently using a workarround: I send the input parameter as a delimited string and on the oracle side I use a split function to convert the delimited string into oracle collection (usually associative array).  You can use different delimiters and achieve multiple level of splitting, which will bring you closer to mimicing an object. I know that this is not a perfect solution and also tedious but unless Oracle extends it's object oriented features to include input parameters in the picture this will still work. You can use the same idea as an output parameter as well - mimic an object thru delimiters on Oracle side and split it on the .Net side provided that cursor output doesn't work for you.

 John

John this is exactly what I'm trying to accomplish.  The following is pseudocode for my Oracle Stored Proc. 

CREATE PROCEDURE SelectFromList 
(
delimitedList varchar2(100);   -- 
--- or 
type TableValues of number; 
AS
BEGIN
    Select * From MyTable Where StoreId in (5,6,7,8) ;
      OR 
   Select * From MyTable where StoreId in (select Storeid from the table input)
END ; 

 --------------------------------------
With passing in the table,  what type of variable and how is it populated from cmd parameters in web page (or app) .

 For instance, can you pass an Oracle Array and will be CAST into table by the Oracle Stored Procedure? 

I have parsing code for delimited list. In the case of the string, I want to define a table and on each loop, Insert into the table. 

Ho do i put this is  a code window? Sorry about the formatting.  

I get the same errors when I execute this statement:

CReate type mytype as table of varchar2(100);
/ 
   select * from table (mytype('1','2','a'))
 /

ERROR:

CReate type mytype as table of varchar2(100);
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [17182], [0x59E057D4], [], [], [], [], [], []

I followed this link suggested:And got the same errors as above. When I executed on 10g, I had no problems. everything worked like a charm. I had been using the 9i client.