Call Oracle package function using Odbc from C#
- by Paolo Tedesco
I have a function defined inside an Oracle package:
CREATE OR REPLACE PACKAGE BODY TESTUSER.TESTPKG as
FUNCTION testfunc(n IN NUMBER) RETURN NUMBER as
begin
return n + 1;
end testfunc;
end testpkg;
/
How can I call it from C# using Odbc? I tried the following:
using System;
using System.Data;
using System.Data.Odbc;
class Program {
static void Main(string[] args) {
using (OdbcConnection connection = new OdbcConnection("DSN=testdb;UID=testuser;PWD=testpwd")) {
connection.Open();
OdbcCommand command = new OdbcCommand("TESTUSER.TESTPKG.testfunc", connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("ret", OdbcType.Int).Direction = ParameterDirection.ReturnValue;
command.Parameters.Add("n", OdbcType.Int).Direction = ParameterDirection.Input;
command.Parameters["n"].Value = 42;
command.ExecuteNonQuery();
Console.WriteLine(command.Parameters["ret"].Value);
}
}
}
But I get an exception saying "Invalid SQL Statement".
What am I doing wrong?
