2

I've got a fairly large and complex Oracle stored procedure (~2200 lines) which I'm able to call successfully from SQL Developer, and executes in 2-3 seconds, using the following:

var o_my_data refcursor
begin
    SCHEMA.pkg.get_data(i_p1 => 'val1',
                        i_p2 => 'p2',
                        i_p3 => 0,
                        i_p4 => 100000,
                        o_my_data => :o_my_data);
end;
/
print o_my_data

However, when I call this from ODP.NET, it throws the following exception:

unable to extend temp segment by 128 in tablespace TEMP1

The way the stored procedure is being called is fairly straightforward:

using (var connection = new OracleConnection(connectionString))
{
    using (var command = connection.CreateCommand())
    {
        connection.Open();

        command.CommandText = "SCHEMA.pkg.get_data";
        command.CommandType = CommandType.StoredProcedure;
        command.BindByName = true;

        command.Parameters.Add("i_p1", "val1");
        command.Parameters.Add("i_p2", "p2");
        command.Parameters.Add("i_p3", 0);
        command.Parameters.Add("i_p4", 1000000);
        command.Parameters.Add("o_my_data", OracleDbType.RefCursor, ParameterDirection.Output);

        // Fails here, before the result can even be read from the output parameter
        command.ExecuteNonQuery();

        connection.Close();
    }
}

When called from ODP.NET, it seems like the temp space gets used up very quickly, hits the 5gb limit, and then throws the exception above where it can't increase the space.

However, if it's an issue with the stored procedure, why can it be executed successfully from SQL Developer with no errors (and memory usage is significantly lower) ?

Mun
  • 14,098
  • 11
  • 59
  • 83
  • Is it possible the user running while in SQL Developer versus the user executing the procedure from ODP.NET are different and have different space limitations? – Nick Apr 12 '17 at 16:37
  • @NicholasV. Thought that might be a possible cause and tried connecting to the DB using the same credentials, but didn't seem to make any difference. The odd thing is that when called from SQL Developer, the query only seems to take up a few MB of temp space, so it's unclear how it's even possible to hit 5gb when called from ODP.NET. – Mun Apr 12 '17 at 16:41
  • Bizarre. That was kind of a shot in the dark for me... beyond that I'm not too DBA-ish. Perhaps posting on the [DBA](http://dba.stackexchange.com/) could get you more assistance? – Nick Apr 12 '17 at 16:42
  • Thanks, will try posting there. It's very odd bug indeed. The other puzzling thing is that this has been working fine for nearly a year and just stopped working a couple of days ago. Still using the same version of ODP.NET too. Can't help but think it's something data-related, but doesn't explain why SQL Developer can call it successfully. – Mun Apr 12 '17 at 16:49
  • Good luck. Somewhat related here: http://stackoverflow.com/questions/6791764/strange-memory-usage-pattern-in-c-sharp-windows-form-app, but unfortunately nothing that will solve your problem, I'm afraid. Just proves that others have also had strange memory consumption through ODP.NET, versus directly calling in Sql Dev. – Nick Apr 12 '17 at 16:51
  • Triple check the connect string (maybe you have several test databases?) and also the user. Remember that tnsaliases of the same name can have different database credentials. Perhaps use EZ connect connect string to be more explicit (passing IP, port, Service name). You say you it's a "very complex" SP, so maybe you could use PL/SQL debugging to ensure both scenarios are following the same PL/SQL code path instead of say, one of them repeatedly throwing exceptions and handling them, etc. – Christian Shay Apr 12 '17 at 18:40
  • You're passing different values for `i_p4` in your two code samples. Your PL/SQL block passes the value `100000` (one hundred thousand) for this parameter whereas your ODP.NET call passes `1000000` (one million). What happens if you run your PL/SQL block with `i_p4` set to one million? – Luke Woodward Apr 12 '17 at 19:12
  • @LukeWoodward That's actually just an error in this code sample as I simplified some of the naming and parameters for this post, but in the actual code, the parameter values are exactly the same. – Mun Apr 12 '17 at 19:22
  • @ChristianShay Connection strings are definitely the same between SQL Developer and the application, and both are running from my local machine (though this error is also occurring in the development environment too). Will look into using PL/SQL debugging to investigate further. – Mun Apr 12 '17 at 19:25
  • What result you see in SQL Developer print ? Is it ok ? 5 GB it's not small size for temp. Problem can be in way of fetching data. – Seyran Apr 12 '17 at 19:55
  • @Seyran Yep, SQL Developer returns a single row and shows all of the expected data. The messages tab doesn't show any output. Memory usage also appears to be fine when calling the stored procedure from SQL Developer. It's only a problem when calling it from ODP.NET. – Mun Apr 12 '17 at 20:04

0 Answers0