CODEWITHSUNDEEP
vb.netoracle-databasedataset
Thaman Gurung
Thaman Gurung

Reputation: 3

Oracle stored procedure gets executed. DataSet returns nothing from vb.net

I have following code and stored procedure in Oracle and vb.net. While I execute the the code, the dataset returns nothing.

Code behind:

grvResults.PageSize = DirectCast(Profile.GetPropertyValue("rowcount"), Integer)
grvResults.DataSourceID = "dsResults"

datasource: 

    public static DataSet Fetch(string sp, OracleParameter[] inputParams)
    {
        OracleConnection conn = new OracleConnection(System.Configuration.ConfigurationManager.AppSettings.Get("BertConnection"));
     cmd = new OracleCommand(sp, conn);
        cmd.CommandType = CommandType.StoredProcedure;

        if (inputParams != null)
        {
            cmd.Parameters.AddRange(inputParams);
        }
        cmd.Parameters.Add(new OracleParameter("resultset", OracleDbType.RefCursor, ParameterDirection.Output));

        DataSet ds = new DataSet();

    .
    .
    .

Stored procedure:

CREATE OR REPLACE PROCEDURE BERT."SP_BIA_OBLIGATION_GROUPED" (
p_bfy in varchar2,
p_fy in varchar2,
p_fm in varchar2,
p_appropriation in varchar2,
p_fund in varchar2,
p_fund_center in varchar2,
p_func_area in varchar2,
p_program in varchar2,
p_boc in varchar2,
p_from_inception in char,
p_red_only in char,
resultset out sys_refcursor
)
 AS

 BEGIN

OPEN resultset FOR
select decode(grouping(t.description), 1, 'TOTAL', t.description) as description,
    case when p_from_inception = '0' then sum(t.amount_cfy) else sum(t.amount_inc) end as amount
from mv_bia_obligation_grouped t

          .
          .
          .

    group by grouping sets ((), (t.description))
    HAVING count(*) != 0;

The stored procedure executes and returns the desired results but while calling from vb.net gives no data. while stepping through the code, I checked the params values those are correct, when I check dataset it has only headings no data. Does anyone has any Idea what is happening here?

Thanks in advance.

Upvotes: 0

Views: 1069

Answers (1)

Prescott Chartier
Prescott Chartier

Reputation: 1653

Your code is definitely C# not vb.net, but here's a vb.net function that returns a dataset for a call to a stored procedure. I use the Oracle Client (Oracle.DataAccess.Client):

'I always provide a open connection to my functions, this way I can execute many procedures on one connection
Public Function fetch(ByVal oraConnection As OracleConnection, ByVal SomeParameter As String) As DataSet
    Dim myCommand As New OracleCommand
    Dim DS As New DataSet
    Try
        With myCommand
            .Connection = oraConnection
            .CommandText = "Bert.SP_BIA_OBLIGATION_GROUPED"
            .CommandType = CommandType.StoredProcedure
            .Parameters.Clear()
             ' Add all your input parameters here ...
            .Parameters.Add(New OracleParameter("SomeParameter", OracleDbType.Varchar2, SomeParameter.Length)).Value = SomeParameter 
            ' This is the resultset output parameter
            .Parameters.Add(New OracleParameter("pDataOut", OracleDbType.RefCursor))
            ' I always give my parameters direction just for clarity and readability, no other reason as the default is input
            .Parameters(0).Direction = ParameterDirection.Input
            .Parameters(1).Direction = ParameterDirection.Output
            Dim DA As New OracleDataAdapter(myCommand)
            DA.Fill(DS, "DATA")
            fetch = DS
        End With
    Catch exc As Exception
        Throw New Exception("Error occurred while retrieving data, the error is: " & exc.Message)
    End Try
    myCommand = Nothing
End Function

C# code

public DataSet fetch(OracleConnection oraConnection, string SomeParameter)
{
    OracleCommand myCommand = new OracleCommand();
    DataSet DS = new DataSet();
    try
    {
        {
            var withBlock = myCommand;
            withBlock.Connection = oraConnection;
            withBlock.CommandText = "Bert.SP_BIA_OBLIGATION_GROUPED";
            withBlock.CommandType = CommandType.StoredProcedure;
            withBlock.Parameters.Clear();
            // Add all your input parameters here ...
            withBlock.Parameters.Add(new OracleParameter("SomeParameter", OracleDbType.Varchar2, SomeParameter.Length)).Value = SomeParameter;
            // This is the resultset output parameter
            withBlock.Parameters.Add(new OracleParameter("pDataOut", OracleDbType.RefCursor));
            // I always give my parameters direction just for clarity and readability, no other reason as the default is input
            withBlock.Parameters(0).Direction = ParameterDirection.Input;
            withBlock.Parameters(1).Direction = ParameterDirection.Output;
            OracleDataAdapter DA = new OracleDataAdapter(myCommand);
            DA.Fill(DS, "DATA");
            fetch = DS;
        }
    }
    catch (Exception exc)
    {
        throw new Exception("Error occurred while retrieving data, the error is: " + exc.Message);
    }
    myCommand = null/* TODO Change to default(_) if this is not a reference type */;
}

Upvotes: 1

Related Questions