Is it possible to capture an OUT type parameter from a PL/SQL stored procedure?

When a stored PL/SQL procedure in my Oracle database is called from ASP.NET, is it possible to retrieve the OUT parameter from the PL/SQL procedure? For example, if I have a simple procedure as below to insert a row into the database. Ideally I would like it to return back the parameter namedNewId to my ASP.NET server. I'd like to capture this in the VB.NET code.

1createorreplace procedure WriteName(FirstNamein varchar2, LastNamein varchar2,NewIdout pls_integer)is
2
3NameId pls_integer;
4
5begin
6
7 select name_seq.nextvalinto NameIdfrom dual;
8
9insert into all_names(id, first_name, last_name)
10values(NameId, FirstName, LastName);
11
12NewId := NameId;
13
14end WriteName;

1<asp:SqlDataSource
2 ID="SqlDataSaveName"
3 runat="server"
4 ConnectionString="<%$ ConnectionStrings:ConnectionString%>"
5 ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName%>"
6 SelectCommand="WRITENAME"
7 SelectCommandType="StoredProcedure">
8 <SelectParameters>
9 <asp:ControlParameter ControlID="TextBoxFirstName" Name="FIRSTNAME" PropertyName="Text" Type="String" />
10 <asp:ControlParameter ControlID="TextBoxLastName" Name="LASTNAME" PropertyName="text" Type="String" />
11 </SelectParameters>
12</asp:SqlDataSource>

This is then called in the VB.NET code as below. It is in this section that I would like to capture the PL/SQL OUT parameterNewId returned from Oracle. 

1 SqlDataSaveName.Select(DataSourceSelectArguments.Empty)

If anybody can help me with the code I need to add to the VB.NET section to capture and then use the returned OUT parameter then I'd be very grateful.

This Select looks an awfully lot like an Insert, doesn't it? :)

My most honest suggestion is that you skip the SqlDataSource altogether for this kind of task. The SqlDataSource has some merit when it comes to binding databound controls to a datasource, but in this case it's a just simple insert from two textboxes.

In other words, use a simple OracleCommand, with a defined output parameter.

If you really want to use the SqlDataSource, then you should first use its Insert method, and corresponding InsertCommand etc. Next, define

<asp:Parameter Name="NewId" Direction="ReturnValue" />

and subscribe to the Inserted event of the SqlDataSource, where you'll be able to retrieve the value from the SqlDataSourceStatusEventArgs.

|||

Hi, thanks for replying. Yes, the example is overly simplified and an INSERT would have been better - I just used it to highlight what I was trying to achieve and that was to accept a returned value from the PL/SQL proc back in ASP.NET. However I think that you have answered my questions to thank you for showing me the Direction="ReturnValue" which is the bit I was missing.