Hi,

I have a stored procedure with 2 out parameters, one ref cursor and one string.How can I get these into my .NET page? Can I use ExecuteNonQuery and get the values?

Thanks.

 Hi Friend,

1. Use ExecuteScalar,

2. use to parameters with,

.Add("StringParameter", OracleType.VarChar)..Direction=ParameterDirection.Output  ' this will give you your second out parameters value.

 hope this will help you.

I have a stored procedure that accepts 2 input parameters and returns about 19 values (numbers, strings) and 1 REF_CURSOR. Here is a portion of my code:

1    Private Sub getSRstats(ByRef cn As OracleConnection, ByRef cmd As OracleCommand, _
2            ByRef arr As ArrayList, ByRef ds As DataSet, ByRef da As OracleDataAdapter)
3            Dim dt As New DataTable("MSGDATA")
4            With cmd
5                .Parameters.Clear()
6                .CommandType = CommandType.StoredProcedure
7                .Connection = cn
8                .CommandText = "ASSEMBLY.GET_STOCKROLL_STATS"
9                .Parameters.Add("p_Lot", OracleType.VarChar, 4).Value = txtLot.Text
10               .Parameters.Add("p_StockRoll", OracleType.VarChar, 3).Value = txtStockroll.Text
11               .Parameters.Add("P_Status", OracleType.Number).Direction = ParameterDirection.Output
12               .Parameters.Add("v_OnHold", OracleType.Number).Direction = ParameterDirection.Output
13               .Parameters.Add("v_TestMethod", OracleType.Number).Direction = ParameterDirection.Output
14               .Parameters.Add("v_StatusDescription", OracleType.VarChar, 255).Direction = ParameterDirection.Output
15               .Parameters.Add("v_TestProcessDescription", OracleType.VarChar, 255).Direction = ParameterDirection.Output
16               .Parameters.Add("v_AuditDescription", OracleType.VarChar, 255).Direction = ParameterDirection.Output
17               .Parameters.Add("v_JumboYield", OracleType.Number).Direction = ParameterDirection.Output
18               .Parameters.Add("v_PotentialJumboYield", OracleType.Number).Direction = ParameterDirection.Output
19               .Parameters.Add("v_SampleYield", OracleType.Number).Direction = ParameterDirection.Output
20               .Parameters.Add("v_PotentialSampleYield", OracleType.Number).Direction = ParameterDirection.Output
21               .Parameters.Add("v_TotalStrands", OracleType.Number).Direction = ParameterDirection.Output
22               .Parameters.Add("v_TotalStrandsWound", OracleType.Number).Direction = ParameterDirection.Output
23               .Parameters.Add("v_TotalStrandsTested", OracleType.Number).Direction = ParameterDirection.Output
24               .Parameters.Add("v_TotalPassingStrands", OracleType.Number).Direction = ParameterDirection.Output
25               .Parameters.Add("v_TotalFailingStrands", OracleType.Number).Direction = ParameterDirection.Output
26               .Parameters.Add("v_TotalCartsWound", OracleType.Number).Direction = ParameterDirection.Output
27               .Parameters.Add("v_TotalCartsTested", OracleType.Number).Direction = ParameterDirection.Output
28               .Parameters.Add("v_TotalPassingCarts", OracleType.Number).Direction = ParameterDirection.Output
29               .Parameters.Add("v_TotalFailingCarts", OracleType.Number).Direction = ParameterDirection.Output
30               .Parameters.Add("v_TotalCartsReleased", OracleType.Number).Direction = ParameterDirection.Output
31               .Parameters.Add("v_TotalCartsInUntestedStrands", OracleType.Number).Direction = ParameterDirection.Output
32               .Parameters.Add("v_TotalUTCartsInFailingStrands", OracleType.Number).Direction = ParameterDirection.Output
33               .Parameters.Add("v_AdditionalPassingCartsNeeded", OracleType.Number).Direction = ParameterDirection.Output
34               .Parameters.Add("v_TestWIP", OracleType.Number).Direction = ParameterDirection.Output
35               .Parameters.Add("v_TotalPackRejectedCarts", OracleType.Number).Direction = ParameterDirection.Output
36               .Parameters.Add("v_TotalPackReleasedCarts", OracleType.Number).Direction = ParameterDirection.Output
37               .Parameters.Add("MsgData_Cursor", OracleType.Cursor).Direction = ParameterDirection.Output
38           End With
39           da.SelectCommand = cmd
40           Try
41               da.Fill(dt) 'populated with cursor data only. Other parameters are handled below
42           Catch ex As Exception
43               lblErrorMsg.Text = ex.Message.ToString
44   
45           End Try
46   
47           ds.Tables.Add(dt)
48           DataGrid6.DataSource = ds.Tables(5)
49           DataGrid6.DataBind()
50   
51           For i As Integer = 0 To cmd.Parameters.Count - 1
52               If IsDBNull(cmd.Parameters.Item(i).Value) Then
53                   cmd.Parameters.Item(i).Value = 0
54               Else
55                   arr.Add(cmd.Parameters.Item(i).Value)'Fill my array list with return values
56               End If
57   
58           Next
59           cn.Close()
60           lblLotStockroll.Text = cmd.Parameters.Item(0).Value & "-" & cmd.Parameters.Item(1).Value
61   
62       End Sub