Call Oracle package function using Odbc from C#
        Posted  
        
            by Paolo Tedesco
        on Stack Overflow
        
        See other posts from Stack Overflow
        
            or by Paolo Tedesco
        
        
        
        Published on 2010-06-01T12:17:03Z
        Indexed on 
            2010/06/01
            12:33 UTC
        
        
        Read the original article
        Hit count: 272
        
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?
© Stack Overflow or respective owner