Pavel Shaposhnikov Pavel Shaposhnikov - 3 months ago 43
C# Question

Return of Oracle Stored Procedure using OracleDataReader (ODAC)

I have a problem with output data from Oracle Stored Procedure using OracleDataReader(Oracle.DataAccess.Client).

Procedure:

procedure LOAD_BL_REQ_2(P_XML CLOB, P_XML_OUT out CLOB) is
BEGIN

P_XML_OUT := 'TEST1111';

exception
when others then
P_XML_OUT := 'LOAD_BL_REQ: Error'|| SQLERRM;
END;


C# Code:

OracleCommand cmd = new OracleCommand();
cmd.Connection = OraConnection;
cmd.CommandText = "IBS.BNT_EQ.LOAD_BL_REQ_2";
cmd.CommandType = System.Data.CommandType.StoredProcedure;

OracleParameter result = new OracleParameter();
result.ParameterName = "P_XML_OUT";
result.OracleDbType = OracleDbType.Clob;
result.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(result);

OracleParameter XMLString = new OracleParameter();
XMLString.ParameterName = "P_XML";
XMLString.OracleDbType = OracleDbType.Varchar2;
XMLString.Direction = System.Data.ParameterDirection.Input;

OracleDataReader dr;

cmd.Transaction = OraConnection.BeginTransaction();
try
{
XMLString.Value = XML;
cmd.Parameters.Add(XMLString);
dr = cmd.ExecuteReader();
cmd.Transaction.Commit();
}
catch (OracleException ex)
{
cmd.Transaction.Rollback();
Log(2, "Transaction fail, exception: " + ex.ToString());
ORADisconnect();
return "";
}


At debug process i see for dr:

Depth: 0
FetchSize: 131072
FieldCount: 0
HasRows: false


For test i add count code:

while (dr.Read())
{
count++;
}


Count is 0 in any times.

This code (for example):

if(dr.IsDBNull(0))
{
//etc...
}


or

OracleClob oclob;
oclob = dr.GetOracleClob(0);


return exception:

dr.isdbnull exception: System.InvalidOperationException: Operation is not valid due to the current state of the object.
at Oracle.DataAccess.Client.OracleDataReader.IsDBNull(Int32 i)
at GlobalFunc.ORA_BlackList_Test(String XML) in c:\inetpub\project\App_Code\GlobalFunc.cs:line 474


At final i need to retrieve CLOB data from Oracle SP, but now stopped at this step.
Use: Visual Studio 2012, ASP.Net (web project, but i write this code to WinForms application and have a same problem), oracle.dataaccess.dll 4.112.3.0

Answer

I found solution! However, all is very simple:

        string XML = "XML Data";
        OracleCommand cmd = OraConnection.CreateCommand();

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "IBS.BNT_EQ.LOAD_BL_REQ_2";

        OracleParameter result = new OracleParameter();
        result.ParameterName = "P_XML";
        result.OracleDbType = OracleDbType.Clob;
        result.Value = XML;
        result.Direction = System.Data.ParameterDirection.InputOutput;
        cmd.Parameters.Add(result);

        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error: " + ex.ToString());
        }

        string str = (result.Value as OracleClob).Value;
        MessageBox.Show("Val: " + str);

        OraConnection.Close();

May be it be useful for someone.

Comments