Web Service and Oracle Stored procedures

[E11esar]

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:

 
CREATE OR REPLACE PROCEDURE SADAS_MANAGER.NLPGAddSearchOne
 
(
    p_postcode          in varchar2, 
    p_paostartnumber    in int,
    fullAdd            out varchar2
 
) 
 
IS 
 
BEGIN
 
select full_address into fullAdd from nlpg_uk where POST_CODE = p_postcode AND PAO_START_NUMBER = p_paostartnumber 
ORDER BY ABS(PAO_START_NUMBER) ASC
 
;
END;
/
 
 

and the corresponding method call from the Web Service:

 
[WebMethod (Description = "Return address details based on two parameters (string, int) corresponding to post code and house number and run type = NLPG")]
    public DataSet NLPGAddressSearchOne(string whereValue1, int whereValue2) 
    {
        OleDbConnection conn = GetConn();
        conn.Open();
 
        OleDbCommand sComm = new OleDbCommand("NLPGAddSearchOne", conn);
        sComm.CommandType = CommandType.StoredProcedure;
        sComm.Parameters.Add("p_postcode", OleDbType.Char).Value = whereValue1;
        sComm.Parameters.Add("p_paostartnumber", OleDbType.Integer).Value = whereValue2;
 
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sComm);
        DataSet addDataSet = new DataSet();
 
        dataAdapter.Fill(addDataSet);
 
        return addDataSet;
    }
 
 

Hopefully that is all clear and that somebody can help me on my with this please?

Thank you.

M :)

[E11esar]

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 :)

[mldisibio]

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"?

SELECT full_address 
  FROM nlpg_uk 
WHERE POST_CODE = p_postcode AND 
PAO_START_NUMBER = p_paostartnumber 
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.