Retrieving Return Parameter

[WellsCarrie]

Thank You for the help.

jmcilhinney had my answer.


I am running the following code:

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] RunObsoluteProductBuild()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] connOhio [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleClient.OracleConnection("Data Source=Ohio.world;User Id=***************;Password=*****************;")
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cmdOhio [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OracleClient.OracleCommand
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] sb [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Text.StringBuilder
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]System.Diagnostics.Debugger.Break()
EventLog1.WriteEntry("ForcastService", "Running Obsolete Product Build(domestic)")
sb.Remove(0, sb.Length)
sb.Append("ObsoleteProduct")
connOhio.Open()
cmdOhio.Connection = connOhio
cmdOhio.CommandText = sb.ToString()
cmdOhio.CommandType = CommandType.StoredProcedure
cmdOhio.Parameters.Add("p_Inventory", OracleType.Number)
cmdOhio.ExecuteNonQuery()
[/SIZE][SIZE=2][COLOR=#0000ff]Do[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE][SIZE=2] connOhio.State = ConnectionState.Executing
[/SIZE][SIZE=2][COLOR=#008000]'forces program to pause while stored procedure is running
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Loop
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
sb.Remove(0, sb.Length)
sb.Append("Sub RunObsoluteProductBuild - ")
sb.Append(ex.Message)
sb.Append("-")
sb.Append(ex.Source)
EventLog1.WriteEntry("ForcastService", sb.ToString())
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] connOhio.State = ConnectionState.Broken [/SIZE][SIZE=2][COLOR=#0000ff]Or[/COLOR][/SIZE][SIZE=2] connOhio.State = ConnectionState.Open [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]connOhio.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Finally
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] connOhio.State = ConnectionState.Open [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]connOhio.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2]connOhio.Dispose()
cmdOhio.Dispose()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE]

This is the error I get.....

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'OBSOLETEPRODUCT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
-System.Data.OracleClient

Does anyone see what I've done wrong. I've never tired to get an out perameter from a stored proc before. They have always run inserts, updates, or just returned datasets.

Thanks!

 

[fpineda101]

For Oracle, you need to call a function.

SELECT A.col1, A.col2, function_name(A.colX, A.colX2) FROM your_table WHERE this_condition = your_parameter

The function must exist in your database.

You can also try selecting your output from the DUAL table.
ie. SELECT 3 + 2 FROM dual will return 5

 

[jmcilhinney]

There's a difference between an output parameter and a return value. Either way, you have to set the direction of your OracleParameter. Change this:

[SIZE=2]cmdOhio.Parameters.Add("p_Inventory", OracleType.Number)[/SIZE]

to this:

[SIZE=2]cmdOhio.Parameters.Add("p_Inventory", OracleType.Number).Direction = ParameterDirection.Output[/SIZE]

or this:

[SIZE=2]cmdOhio.Parameters.Add("p_Inventory", OracleType.Number).Direction = ParameterDirection.ReturnValue[/SIZE]

depending on whether you want an output parameter or a return value.

 

[WellsCarrie]

YEAH!!!!!
THANK YOU!

I didn't even think to reset the direction.