Calling stored procedure from VB.net

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?

    Requires Free Membership to View

    Login

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.

This was first published in July 2006

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top