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.

Is it possible

Is It Possible to use Inner Join -- to retrieve data from a different Database? Or is it limited to the same Database? The Example below throws me an error. Thank You.
--------------------------------------------------------------
Function GetNames(ByVal uid As Integer) As System.Data.DataSet Dim connectionString As String = (ConfigurationSettings.AppSettings("ConnectionString"))
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [Products].[uid], [Products].[ManufacturerId], [Products].[Name], [Manufacturers].[uid], [Manufacturers].[AddressID], [Test.dbo].[uid], [Test.dbo].[Company] FROM [Products] INNER JOIN [Manufacturers] ON [Products].[uid] = [Manufacturers].[uid] INNER JOIN [Test.dbo] ON [Manufacturers].[uid] = [Test.dbo].[uid]"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_uid As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_uid.ParameterName = "@.uid"
dbParam_uid.Value = uid
dbParam_uid.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_uid)
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)
Return dataSet
End FunctionYour syntax is incorrect. You are missing ] and [ when referencing the other database.
Dim queryString As String = "SELECT [Products].[uid], [Products].[ManufacturerId], [Products].[Name], [Manufacturers].[uid], [Manufacturers].[AddressID], [Test].[dbo].[uid], [Test].[dbo].[Company] FROM [Products] INNER JOIN [Manufacturers] ON [Products].[uid] = [Manufacturers].[uid] INNER JOIN [Test].[dbo] ON [Manufacturers].[uid] = [Test].[dbo].[uid]"
|||Thank You Douglas,

I did try it that way, and it threw me this error:

Invalid object name 'Test.dbo'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Invalid object name 'Test.dbo'.
Source Error:

Line 74: dataAdapter.SelectCommand = dbCommandLine 75: Dim dataSet As System.Data.DataSet = New System.Data.DataSetLine 76: dataAdapter.Fill(dataSet)Line 77: Line 78: Return dataSet

|||asp.netcat, you don't have a fully qualified table name there. You should be using this format when referring to your table:

database.owner.tablename

and this format when referring to a column in your table

database.owner.tablename.columnname

|||

Thank you Terri & Douglas,

Yes Indeed I overlooked & forgot all about adding the Table Name. I do have it working now. Big Thanks!! As always... You Guys are the Best!!!