locked
Web Service using Oracle Stored Procedure Which returns a number and a varchar RRS feed

  • Question

  • User1052173279 posted

     

    Hi,
    I am creating a webservice which will call an Oracle Stored Procedure.
    The stored procedure returns a number and varchar for example:
     
    create or replace procedure anil_test(x_user_name OUT varchar, x_email_id OUT varchar, x_user_id OUT number, p_user_name IN varchar)
    is
    begin
    select user_name,email_id,user_id
    into x_user_name,x_email_id,x_user_id
    from all_users
    where user_name = p_user_name;
    end;
    I created a webservice which kind of looks as below. I tried to consume the web service but no data shows up.
    I was researching and all the examples I found were using a procedure for which the out put is a cursor. The procedures were too simple and the cursor is just a select statement.
     
    I would appreciate it if any one of you can point me to some direction or give me a hint on how to solve this issue.
     
    Thanks
    Anil

     

     

    [WebMethod]

    public DataSet UserEnvInfo() {

    OracleConnection con = new OracleConnection();

    DataSet UserInfo = new DataSet();

    try

    {

    con.ConnectionString = "User ID=username;Password=password;Data Source=devdb";

    con.Open();

    OracleCommand comm = new OracleCommand("ANIL_TEST", con);

    comm.CommandType = CommandType.StoredProcedure;

    OracleParameter param = new OracleParameter( x_user_name , Oracletype.Varchar, 30);

    param.Direction = ParameterDirection.Output;

    comm.Parameters.Add(param);

    comm.ExecuteNonQuery();

     

     

    OracleDataAdapter adapter = new OracleDataAdapter(comm);

    adapter.SelectCommand = comm;

    adapter.Fill(UserInfo);

     

     

    }

    catch (OracleException oex)

    {

    string error = oex.Message;

    }

     

    return UserInfo;

    }

    Saturday, May 26, 2007 8:51 AM

Answers

  • User-2005691517 posted

    Suppose param is the parameter you added to the command object.

    After the command is executed, the value will be available in the same param object.

    param.value

    Since value is of type object, you will have to cast to the required type. 

    As for rewriting your stored procedure to enable it to return a DataSet, please refer to the links I posted above. The one from Microsoft has a complete sample.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, May 27, 2007 8:07 PM

All replies

  • User-2005691517 posted

    This is a working WebService that returns a DataSet with 2 tables. You will need to add a reference to System.Web.Extensions in your project
     

    <%@ WebService Language="C#" Class="WebService" %>
    
    using System;
    using System.Configuration;
    using System.Web;
    using System.Web.Services;
    using System.Web.Services.Protocols;
    using System.Data;
    using System.Data.SqlClient;
    
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.Web.Script.Services.ScriptService()]
    public class WebService  : System.Web.Services.WebService {
        [WebMethod]
        public DataSet GetData()
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DocArchiveConnectionString"].ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter("Select * from da_libraries", con);
            DataSet ds = new DataSet();
            da.Fill(ds,"table1");
            da.SelectCommand.CommandText = "Select * from da_lib_cats";
            da.Fill(ds,"table2");
            return ds;
        }
        
    }

      

     

    Saturday, May 26, 2007 9:38 PM
  • User-2005691517 posted

    The code that you have used to call the stored procedure from your code is not correct.

    1. Procedure expects 4 parameters, and you have passed only 1

    2. comm.ExecuteNonQuery(); is not required

    3. The values returned by the stored procedure are being returned as output parameters and so will have to be retrieved from the parameters. Values won't be returned as a DataTable.

    4. When you are using DataAdapters, you don't need to open the connection explicitly (unless the connection is being used by some other parts of your code) . The DataAdapter will open and close the connection for you.

     

    Please refer to the following to learn how to call Oracle stored procedures corrctly.

    http://msdn.microsoft.com/en-us/library/ms971506.aspx

    http://www.oracle.com/technology/pub/articles/mastering_dotnet_oracle/williams_sps.html 

    Saturday, May 26, 2007 10:18 PM
  • User1052173279 posted

    Prashant,

    Thanks for your reply. In the code I listed, I put in only one parameter to illustrate my problem.

    I think the solution to my problem lies in points 3 and 4 of your reply.

    How exactly do I retrieve the output parameters from the procedure.

    And how do I re-write the Stored Procedure , so that I can retrieve the outputs as a databset.

     

    Thanks

    Anil

    Sunday, May 27, 2007 12:03 PM
  • User-2005691517 posted

    Suppose param is the parameter you added to the command object.

    After the command is executed, the value will be available in the same param object.

    param.value

    Since value is of type object, you will have to cast to the required type. 

    As for rewriting your stored procedure to enable it to return a DataSet, please refer to the links I posted above. The one from Microsoft has a complete sample.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, May 27, 2007 8:07 PM
  • User-1529538686 posted

    Can you please illustrate the step wise procedure of calling an asp.net webservice from oracle, which just performs the addition of two numbers but which we supply from oracle. What shall be the PL/SQL code to call an asp.net web method that performs the addition?

    Many Thanks

    Thursday, May 30, 2013 5:18 AM