Passing an Array to an Oracle Procedure

[IT4EVR]

I'd like to pass an array of values to an Oracle procedure. I'd like the procedure to act on these values, iterating through the array in a loop.

Is this possible?

The client application is in C#. I have a feeling you could probably do this with Java, but not .NET. I know with Java you can create embedded procedures, etc.

 

[SantaMufasa]

IT,

Here is code that illustrates passing a PL/SQL table to a procedure, then processing the table:

set serveroutput on format wrap
declare
    type region_table is table of s_region%rowtype;
    regions region_table;
    procedure show_regions (x region_table) is
        begin
          for i in 1..x.count loop
                dbms_output.put_line(x(i).id||': '||x(i).name);
          end loop;
        end;
begin
    select * bulk collect into Regions from s_region;
    show_regions(regions);
end;
/

1: North America
2: South America
3: Africa / Middle East
4: Asia
5: Europe

Let us know if this resolves your question.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[IT4EVR]

Thanks for your reply. Actually I was looking to pass the array from the client application, which is in C#. I believe I found a good, working example on the Oracle web site. I believe the example uses bind arrays.

 

[IT4EVR]

I am really thrilled with this ability of Oracle to pass collections in and out of procedures. I don't believe SQL Server supports this same functionality.

For a developer, serializing data helps to reduce server round trips and enhances the performance of your applications.

And to top it off, Microsoft got smart and actually built functionality in .NET to be able to pass collections to and from Oracle through a data command.

Oracle and Microsoft working together? Wow