Help with Command Text.

[Russell Verdun]

Can anyone help me with the syntax that would be used to execute an Oracle
stored procedure from vb.net. I don't recall the exact syntax but, it used a
Call statement. I gather this is similar to SQL's Execute statement.
Actually I want to mimic this text:

dim cmd as sqlcommand
cmd.CommnadText = "Execute <mySP> @Parm1 = 12, @Param2 = 'Jim' "

How can this be done with Oracle. Again I saw somewhere, the use of a call
statement, I gather it would replace the Execute as in SQL. I would
appreciate any thoughts and advice on the syntax.

 

[Russell Verdun]

I'm trying to do this on a webservice, that's not possible to pass any
command or parameter objects. I wish it was that straight forward.

Set your CommandType to CommanType.StoredProcedure

Set your CommandText to your stored procedure name

Pass in parameters as an array of parameters object OleDbParameter in case
you are using OleDb

and then do a Command.ExecuteNonQuery()

Regards,

Deepak
[I Code, therefore I am]

Can anyone help me with the syntax that would be used to execute an Oracle
stored procedure from vb.net. I don't recall the exact syntax but, it used a
Call statement. I gather this is similar to SQL's Execute statement.
Actually I want to mimic this text:

dim cmd as sqlcommand
cmd.CommnadText = "Execute <mySP> @Parm1 = 12, @Param2 = 'Jim' "

How can this be done with Oracle. Again I saw somewhere, the use of a call
statement, I gather it would replace the Execute as in SQL. I would
appreciate any thoughts and advice on the syntax.

 

[Guest]

Set your CommandType to CommanType.StoredProcedure

Set your CommandText to your stored procedure name

Pass in parameters as an array of parameters object OleDbParameter in case
you are using OleDb

and then do a Command.ExecuteNonQuery()

Regards,

Deepak
[I Code, therefore I am]

 

[rbutch]

here's what i used, but the stored proc was in a package. hopefully it's not too jumbled up. the proc was a ref_cursor.
also be sure to add a reference and these two imports below:

Imports System.Data.oracleclient.OracleParameter
Imports System.data.oracleclient


here's the proc::: basically taking one variable


Private Sub get_empl_orgs()
Dim empl As String
empl = UCase(txtEmpl.Text)
Try
DsHistory1.Clear()
DsHistory1.EnforceConstraints = False
daHistory.SelectCommand.CommandText = "pkg_pay_field_discrepancies.get_empl_orgs"
daHistory.SelectCommand.CommandType = CommandType.StoredProcedure
daHistory.SelectCommand.Parameters.Add(New OracleParameter("io_empl_id", OracleClient.OracleType.VarChar)).Value = empl
daHistory.SelectCommand.Parameters.Add(New OracleParameter("o_cursor", OracleClient.OracleType.Cursor)).Direction = ParameterDirection.Output
daHistory.Fill(DsHistory1)
If DsHistory1.ORG_HISTORY.Count = 0 Then
lblMessage.Text = "Employee Not Found"
Else
End If
Catch ex As Exception
lblMessage.Text = ex.Message
End Try

daHistory.SelectCommand.Parameters.Clear()
End Sub

**********************************************************************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

 

[Russell Verdun]

Thanks, but I need to call a web service to execute the proc. on a remote
machine. I need to pass a web method some sort of definition of the proc.
and it's parameters. Dynamically build the command object on the web
service, but not sure what the sort of def. or how to get it to the web
service. Is there anyone, that ever had to do this? You are limited as to
what variables can be passed to a web method. I'm at wits end.

Russ

here's what i used, but the stored proc was in a package. hopefully it's

not too jumbled up. the proc was a ref_cursor.

also be sure to add a reference and these two imports below:

Imports System.Data.oracleclient.OracleParameter
Imports System.data.oracleclient


here's the proc::: basically taking one variable


Private Sub get_empl_orgs()
Dim empl As String
empl = UCase(txtEmpl.Text)
Try
DsHistory1.Clear()
DsHistory1.EnforceConstraints = False
daHistory.SelectCommand.CommandText = "pkg_pay_field_discrepancies.get_empl_orgs"
daHistory.SelectCommand.CommandType = CommandType.StoredProcedure
daHistory.SelectCommand.Parameters.Add(New

OracleParameter("io_empl_id", OracleClient.OracleType.VarChar)).Value = empl

daHistory.SelectCommand.Parameters.Add(New

OracleParameter("o_cursor", OracleClient.OracleType.Cursor)).Direction =
ParameterDirection.Output

daHistory.Fill(DsHistory1)
If DsHistory1.ORG_HISTORY.Count = 0 Then
lblMessage.Text = "Employee Not Found"
Else
End If
Catch ex As Exception
lblMessage.Text = ex.Message
End Try

daHistory.SelectCommand.Parameters.Clear()
End Sub

**********************************************************************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP &

ASP.NET resources...