0

I need to execute a stored function in oracle or sql through vb.net.

I created a command object. Depending on the database type(oracle or SQL) i am preparing the

Command text as Select functionName(?,?,?,?,?,?,?,?) from dual; (For Oracle) Adding the parameter values of the function

Now performing the ExecuteScalar which is not working saying invalid parameter.

This works with ODBC connection string. But ODBC doesn't with 64bit.

My Requirement: Code should execute a user defined stored procedure by taking the values at runtime.

Thanks Rupesh

Rupesh
  • 983
  • 3
  • 11
  • 20

1 Answers1

1

Your command text should be just the Stored procedure name without a select, and make sure you set the command type to stored procedure. Check out this link for example:

http://support.microsoft.com/kb/321718

Oracle:

Dim cmd As New OracleCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "OracleSP"
Dim p1 As OracleParameter
Dim p2 As OracleParameter

p1 = cmd.Parameters.Add("Param1", OracleType.NVarChar)
p1.Value = "Value1"
p2 = cmd.Parameters.Add("Param2", OracleType.Double)
p2.Value = 10

cmd.ExecuteNonQuery()

SQL Server:

  cmd.Connection = conn
  cmd.CommandType = CommandType.StoredProcedure
  cmd.CommandText = "SqlSP"
  cmd.Parameters.Add("@Param1", SqlDbType.Int)
  cmd.ExecuteNonQuery()

I am not sure about Oracle as I haven't done it (I think it should work) but with Sql server you can use:

SqlCommandBuilder.DeriveParameters(cmd)

to populate the SqlParametersCollection on the command, instead of setting them like I did. MSDN documentation

After that you can loop thru them and set your values as necessary.

Jay
  • 5,897
  • 1
  • 25
  • 28
  • Thanks for that, But in that case we need to know the parameter names of the function inorder to execute the stored procedure – Rupesh Dec 13 '11 at 12:23