By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,918 Members | 1,520 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,918 IT Pros & Developers. It's quick & easy.

Web Service and Oracle Stored procedures

100+
P: 132
Hi there.

I am writing a web service in C# that connects to an Oracle database via OleDb and wish for the service to return a dataset for use by a client application. I have achieved this using dynamic sql but now there is a requirement to use stored procedures instead, thus calling these from the web service.

As I am using OleDb to connect to the Oracle database, what method is the best way forward here to achieve the results I am after, please? I have been reading about the use of REF CURSOR but I'm not sure if this is the best approach.

Here is an example of one of the stored procedures I am using:

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE OR REPLACE PROCEDURE SADAS_MANAGER.NLPGAddSearchOne
  3.  
  4. (
  5.     p_postcode          in varchar2, 
  6.     p_paostartnumber    in int,
  7.     fullAdd            out varchar2
  8.  
  9.  
  10. IS 
  11.  
  12. BEGIN
  13.  
  14. select full_address into fullAdd from nlpg_uk where POST_CODE = p_postcode AND PAO_START_NUMBER = p_paostartnumber 
  15. ORDER BY ABS(PAO_START_NUMBER) ASC
  16.  
  17. ;
  18. END;
  19. /
  20.  
  21.  
and the corresponding method call from the Web Service:

Expand|Select|Wrap|Line Numbers
  1.  
  2. [WebMethod (Description = "Return address details based on two parameters (string, int) corresponding to post code and house number and run type = NLPG")]
  3.     public DataSet NLPGAddressSearchOne(string whereValue1, int whereValue2) 
  4.     {
  5.         OleDbConnection conn = GetConn();
  6.         conn.Open();
  7.  
  8.         OleDbCommand sComm = new OleDbCommand("NLPGAddSearchOne", conn);
  9.         sComm.CommandType = CommandType.StoredProcedure;
  10.         sComm.Parameters.Add("p_postcode", OleDbType.Char).Value = whereValue1;
  11.         sComm.Parameters.Add("p_paostartnumber", OleDbType.Integer).Value = whereValue2;
  12.  
  13.         OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sComm);
  14.         DataSet addDataSet = new DataSet();
  15.  
  16.         dataAdapter.Fill(addDataSet);
  17.  
  18.         return addDataSet;
  19.     }
  20.  
  21.  
Hopefully that is all clear and that somebody can help me on my with this please?

Thank you.

M :)
Jan 6 '09 #1
Share this Question
Share on Google+
2 Replies


100+
P: 132
Hi there. I have found a solution to this which can be found via the following link:

Oracle Ref Cursor - ASP.NET Forums

Hopefully this may be of use to somebody else.

Thank you.

M :)
Jan 6 '09 #2

Expert 100+
P: 190
It's been awhile since I've pulled data into DataSets from Oracle, but would your first example/post work if you removed the output parameter and the "SELECT INTO"?

Expand|Select|Wrap|Line Numbers
  1. SELECT full_address 
  2.   FROM nlpg_uk 
  3. WHERE POST_CODE = p_postcode AND 
  4. PAO_START_NUMBER = p_paostartnumber 
  5. ORDER BY ABS(PAO_START_NUMBER) ASC
should return a dataset with one table and one column called "full_address".

The REF Cursor is a way to pass a set of rows as a variable or output parameter, and this is useful when calling one stored procedure from another where you want a set of rows to be returned instead of a single value.

However, since your web service is simply returning a dataset...I think that a simple SELECT statement will fill the DataSet. Your client calling the Web Service does not necessarily appear to need to manipulate the resultset as an output parameter.
Jan 6 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.