Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: