I would like to know how I can call an Oracle stored procedure from VB.net. How will I pass an array variable to that stored procedure? How can I get the out parameters from the stored procedure?
I am not 100% sure about how to call a procedure from VB.net. But I recommend instead of calling a procedure, you should call a function. Although you can pass an out parameter to a procedure, it is much easier to understand the return feature of a function. Try this:
Public static
arraytest(
)
{
}
In order to create a function with a varray datatype parameter, you should create a varray datatype. I have created two varrays, 1)return_array; 2)named_array.
CREATE TYPE return_array AS VARRAY(10) of VARCHAR2(10);
CREATE TYPE named_array AS VARRAY(10) of VARCHAR2(10);
After creating your arrays, you can now create your stored function arrayTest.
CREATE or REPLACE FUNCTION arrayTest(array1 named_array)
RETURN return_array
as
begin
-- some data manipilation
null;
exception
when others then
null;
end;
For more info on VARRAY see Chapter 6 in "Oracle Database 10g PL/SQL Programming" by Oracle Press.
Dig Deeper on Using Oracle PL-SQL