StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POType safe binding to an Oracle Stored Procedure in C#?
text
Body
copied!<p>We are deploying multiple projects in C# with Oracle databases behind. I would like to implement all of the database logic in Oracle stored procedures as this </p> <ul> <li>keeps all of the database logic in the database</li> <li>makes it easier to maintain when database structures change</li> <li>allows re-use of the stored procedures more easily across programming languages</li> </ul> <p>I have test code running where I return rows using a SYS_REFCURSOR and I manually do the data bind on the results as SYS_REFCURSOR could be returning anything - i.e. its not type safe</p> <p>My question is - is there any way I can define correct types in the stored procedure return type and correctly bind to that type safely in my C# code?</p> <p>e.g. my PL/SQL procedure looks like this - the return part is not type safe - it could be anything. If I wanted to re-use it from another Oracle package then it will not have the correct type checking</p> <pre><code>PROCEDURE get_risk (p_process_id IN NUMBER, p_risk OUT sys_refcursor); </code></pre> <p>and my C# code looks something like the following. I have cludged this together from several classes so hopefully it makes sense. When I extract the data from the DB call I am manually defining the data types - I need to know in the C# code what the Oracle data types are</p> <pre><code>// setup procedure call _oracleCommand = new OracleCommand("risk_pkg.get_risk", _conn.OracleConnection); _oracleCommand.Parameters.Add(new OracleParameter("p_process_id", OracleDbType.Int64, processId, ParameterDirection.Input)); _oracleCommand.Parameters.Add(new OracleParameter("p_risk", OracleDbType.RefCursor, null, ParameterDirection.Output)); _oracleDataAdapter = new OracleDataAdapter(_oracleCommand); _dataSet = new DataSet(); // call Oracle _oracleDataAdapter.Fill(_dataSet); // extract data - hand coded binding Int64 dbRiskId = (Int64)_dataSet.Tables[0].Rows[0][_dataSet.Tables[0].Columns["risk_id"]]; Int64 dbClientId = (Int64)_dataSet.Tables[0].Rows[0][_dataSet.Tables[0].Columns["client_id"]]; return new Risk(dbRiskId, dbClientId); </code></pre> <p>This isn't necessarily a problem - I just want to know if there is a better way of doing this to make my PL/SQL more obvious in what it is returning, and making my C# code not have to know the Oracle data types - encapsulating me from database structure changes</p> <p>Accepted solution : this seems to be the practical solution. I'm still slightly unsatisfied that my Oracle procedure isn't defining its return type explicitly, but that life</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload