Calling Oracle stored procedure from C

Question

How does one call a stored procedure in oracle from C

User · Answer

This Code works well for me calling oracle stored procedure

Add references by right clicking on your project name in solution explorer >Add Reference >.Net then Add namespaces.

using System.Data.OracleClient;
using System.Data;

then paste this code in event Handler

        string str = "User ID=username;Password=password;Data Source=Test";
        OracleConnection conn = new OracleConnection(str);
        OracleCommand cmd = new OracleCommand("stored_procedure_name", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        --Ad parameter list--
        cmd.Parameters.Add("parameter_name", "varchar2").Value = value;
        ....
        conn.Open();
        cmd.ExecuteNonQuery();

And its Done...Happy Coding with C#

User · Answer

In  Net through version 4 this can be done the same way as for SQL Server Stored Procs but note that you need    using System Data OracleClient    There are some system requirements here that you should verify are OK in your scenario   Microsoft is deprecating this namespace as of  Net 4 so third-party providers will be needed in the future  With this in mind  you may be better off using Oracle Data Provider for  Net  ODP NET  from the word go - this has optimizations that are not in the Microsoft classes   There are other third-party options  but Oracle has a strong vested interest in keeping  Net developers on board so theirs should be good

User · Answer

Instead of   cmd   new OracleCommand  ProcName   con   cmd CommandType   CommandType StoredProcedure  cmd Parameters Add  ParName   OracleDbType Varchar2  ParameterDirection Input  Value    foo     You can also use this syntax   cmd   new OracleCommand  BEGIN ProcName  p0   END    con   cmd CommandType   CommandType Text  cmd Parameters Add  ParName   OracleDbType Varchar2  ParameterDirection Input  Value    foo     Note  if you set cmd BindByName   False  which is the default  then you have to add the parameters in the same order as they are written in your command string  the actual names are not relevant  For cmd BindByName   True the parameter names have to match  the order does not matter   In case of a function call the command string would be like this   cmd   new OracleCommand  BEGIN  ret    ProcName  ParName   END    con   cmd CommandType   CommandType Text  cmd Parameters Add  ret   OracleDbType RefCursor  ParameterDirection ReturnValue       cmd Parameters Add  ParName   OracleDbType Varchar2  ParameterDirection Input  Value    foo      cmd ExecuteNonQuery    is not needed  otherwise the function is executed twice  var da   new OracleDataAdapter cmd   da Fill dt

User · Answer

It s basically the same mechanism as for a non query command with    command CommandText   the name of the stored procedure command CommandType   CommandType StoredProcedure As many calls to command Parameters Add as the number of parameters the sp requires command ExecuteNonQuery   There are plenty of examples out there  the first one returned by Google is this one  There s also a little trap you might fall into  if your SP is a function  your return value parameter must be first in the parameters collection

User · Answer

Please visit this ODP site set up by oracle for Microsoft OracleClient Developers  http   www oracle com technetwork topics dotnet index-085703 html  Also below is a sample code that can get you started to call a stored procedure from C  to Oracle  PKG COLLECTION CSP COLLECTION HDR SELECT is the stored procedure built on Oracle accepting parameters PUNIT  POFFICE  PRECEIPT NBR and returning the result in T CURSOR   using Oracle DataAccess  using Oracle DataAccess Client   public DataTable GetHeader BySproc string unit  string office  string receiptno        using  OracleConnection cn   new OracleConnection DatabaseHelper GetConnectionString                   OracleDataAdapter da   new OracleDataAdapter            OracleCommand cmd   new OracleCommand            cmd Connection   cn          cmd InitialLONGFetchSize   1000          cmd CommandText   DatabaseHelper GetDBOwner      PKG COLLECTION CSP COLLECTION HDR SELECT           cmd CommandType   CommandType StoredProcedure          cmd Parameters Add  PUNIT   OracleDbType Char  Value   unit          cmd Parameters Add  POFFICE   OracleDbType Char  Value   office          cmd Parameters Add  PRECEIPT NBR   OracleDbType Int32  Value   receiptno          cmd Parameters Add  T CURSOR   OracleDbType RefCursor  Direction   ParameterDirection Output           da SelectCommand   cmd          DataTable dt   new DataTable            da Fill dt           return dt

User · Answer

I have now got the steps needed to call procedure from C        GIVE PROCEDURE NAME    cmd   new OracleCommand  PROCEDURE NAME   con      cmd CommandType   CommandType StoredProcedure        ASSIGN PARAMETERS TO BE PASSED    cmd Parameters Add  PARAM1  OracleDbType Varchar2  Value   VAL1     cmd Parameters Add  PARAM2  OracleDbType Varchar2  Value   VAL2        THIS PARAMETER MAY BE USED TO RETURN RESULT OF PROCEDURE CALL    cmd Parameters Add  vSUCCESS   OracleDbType Varchar2  1      cmd Parameters  vSUCCESS   Direction   ParameterDirection Output        USE THIS PARAMETER CASE CURSOR IS RETURNED FROM PROCEDURE    cmd Parameters Add  vCHASSIS RESULT  OracleDbType RefCursor ParameterDirection InputOutput          CALL PROCEDURE    con Open       OracleDataAdapter da   new OracleDataAdapter cmd      cmd ExecuteNonQuery          RETURN VALUE    if  cmd Parameters  vSUCCESS   Value ToString   Equals  T                 YOUR CODE           OR      IN CASE CURSOR IS TO BE USED  STORE IT IN DATATABLE    con Open       OracleDataAdapter da   new OracleDataAdapter cmd      da Fill dt     Hope this helps

User · Answer

Connecting to Oracle is ugly  Here is some cleaner code with a using statement  A lot of the other samples don t call the IDisposable Methods on the objects they create    using  OracleConnection connection   new OracleConnection  ConnectionString        using  OracleCommand command   new OracleCommand  ProcName   connection                                command CommandType   CommandType StoredProcedure            command Parameters Add  ParameterName   OracleDbType Varchar2  Value    Your Data Here             command Parameters Add  SomeOutVar   OracleDbType Varchar2  120             command Parameters  return out   Direction   ParameterDirection Output            command Parameters Add  SomeOutVar1   OracleDbType Varchar2  120             command Parameters  return out2   Direction   ParameterDirection Output            connection Open              command ExecuteNonQuery              string SomeOutVar   command Parameters  SomeOutVar   Value ToString              string SomeOutVar1   command Parameters  SomeOutVar1   Value ToString

[c#] Calling Oracle stored procedure from C#?

Examples related to c#

Examples related to .net

Examples related to oracle

Examples related to stored-procedures

Examples related to ado.net