This discussion is archived
2 Replies Latest reply: Jan 23, 2013 7:13 AM by 985695 RSS

Problem getting data from a stored procedure

985695 Newbie
Currently Being Moderated
On the Oracle DB there's a stored proc defined like:


PROCEDURE pGetHashes ( iFrom IN NUMBER, iTo IN NUMBER, sHash1 OUT CHAR, sHash2 OUT CHAR );


When I call this procedure from within my app, I only get a value for the sHash2 parameter. The value of the sHash1 parameter is always null. (Running the same stored proc from sqldeveloper gives a result for both hash values.)
Underneath I have added the code which I use to call the stored proc. Does anybody see anything I might have done wrong?


int iFrom = 0;
int iTo = 1000;
using (IDbCommand command = dbConnection.CreateCommand())
{
OracleCommand orclCommand = command as OracleCommand;
orclCommand.CommandText = "pGetHashes";
orclCommand.CommandType = CommandType.StoredProcedure;

orclCommand.Parameters.Clear();
orclCommand.Parameters.Add("iFrom", OracleDbType.Int32, iFrom, ParameterDirection.Input);
orclCommand.Parameters.Add("iTo", OracleDbType.Int32, iTo, ParameterDirection.Input);

OracleParameter orclParam = new OracleParameter("sHash1", OracleDbType.Char, 100);
orclParam.Direction = ParameterDirection.Output;
orclCommand.Parameters.Add(orclParam);
orclParam = new OracleParameter("sHash2", OracleDbType.Char, 100);
orclParam.Direction = ParameterDirection.Output;
orclCommand.Parameters.Add(orclParam);

orclCommand.BindByName = true;

orclCommand.ExecuteNonQuery();

// after this the orclCommand.Parameters["sHash1"].Value is always null.
// the orclCommand.Parameters["sHash2"].Value has the correct value.
}


For extra documentation. Running the following PLSQL from within sqldeveloper results in both a value for Hash1 and Hash2:


SET SERVEROUTPUT ON;
DECLARE
sHash1 CHAR(67);
sHash2 CHAR(67);
nFrom NUMBER := 0;
nTo NUMBER := 1000;
BEGIN

pGetHashes( nFrom, nTo, sHash1, sHash2 );

dbms_output.put_line('Hash1: '|| sHash1);
dbms_output.put_line('Hash2: '|| sHash2);
END;


Thanks for any light you can shed on this problem.
  • 1. Re: Problem getting data from a stored procedure
    gdarling - oracle Expert
    Currently Being Moderated
    I can only assume that something is "going wrong" inside your procedure that is resulting in NULL actually being returned, although I don't see any reason the code should be causing it.

    Try the folllowing "pGetHashes2" procedure, and let me know if you see the same results with your code. It works fine for me anyway..

    Greg
    /*
    create or replace procedure pGetHashes2 (iFrom number, iTo number, sHash1 out char, sHash2 out char)
    as
    begin
    shash1 := 'foo';
    shash2 :='bar';
    end;
    /
    */
    using System;
    using System.Data;
    using Oracle.DataAccess.Client;
    
    namespace otnpost
    {
        class Program
        {
            static void Main(string[] args)
            {
                OracleConnection dbConnection = new OracleConnection("data source=orcl;user id=scott;password=tiger");
                dbConnection.Open();
                int iFrom = 0;
                int iTo = 1000;
                using (IDbCommand command = dbConnection.CreateCommand())
                {
                    OracleCommand orclCommand = command as OracleCommand;
                    orclCommand.CommandText = "pGetHashes2";
                    orclCommand.CommandType = CommandType.StoredProcedure;
    
                    orclCommand.Parameters.Clear();
                    orclCommand.Parameters.Add("iFrom", OracleDbType.Int32, iFrom, ParameterDirection.Input);
                    orclCommand.Parameters.Add("iTo", OracleDbType.Int32, iTo, ParameterDirection.Input);
    
                    OracleParameter orclParam = new OracleParameter("sHash1", OracleDbType.Char, 100);
                    orclParam.Direction = ParameterDirection.Output;
                    orclCommand.Parameters.Add(orclParam);
                    orclParam = new OracleParameter("sHash2", OracleDbType.Char, 100);
                    orclParam.Direction = ParameterDirection.Output;
                    orclCommand.Parameters.Add(orclParam);
    
                    orclCommand.BindByName = true;
    
                    orclCommand.ExecuteNonQuery();
    
                    // after this the orclCommand.Parameters["sHash1"].Value is always null.
                    // the orclCommand.Parameters["sHash2"].Value has the correct value.
                    Console.WriteLine(orclCommand.Parameters["sHash1"].Value);
                    Console.WriteLine(orclCommand.Parameters["sHash2"].Value);
                }
            }
        }
    }
    OUTPUT
    ==========
    foo

    bar

    Press any key to continue . . .
  • 2. Re: Problem getting data from a stored procedure
    985695 Newbie
    Currently Being Moderated
    Looked into the stored procedure and there was indeed an error in the procedure.

    Within the stored procedure another stored proc was called for each hash value.

    The first hash value was retrieved from table_a, which had a date column. The way the hash was created caused a crash due to different default NLS_DATE_FORMAT settings in the .NET environment. The exception within the procedure was captured however and the return value simply set to null => That's why there was no meaningfull feedback to any error occurring from the .NET side.

    Because in sqldeveloper the default NLS_DATE_FORMAT was the same as whoever wrote that stored proc assumed it should be, the procedure was running correct from within sqldeveloper.

    Anyway adjusting the stored procedure fixed the issue finally for me. Tricky one to find...
    Thanks for the reply

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points