Table parameters initialisation issue.

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
chris.pritchard
Posts: 5
Joined: Mon 24 Jun 2019 21:15

Table parameters initialisation issue.

Post by chris.pritchard » Mon 24 Jun 2019 21:29

Hi All. I am having a problem that almost looks like a bug with dotconnect, but I am hoping someone might spot an issue.

We have a oracle sql query we want to run, with one of the parameters given to it being a table parameter with multiple columns. The type definitions used are as follows:

Code: Select all

create type calculateServiceInputRecord as object (employeeId varchar2(8 byte), 
                                                  startDate  date,
                                                  endDate    date);
create type calculateServiceInputList is table of calculatServiceInputRecord;
The code that uses the above fails in two places: first, when retrieving the table type from the db where it returns a 'ORA-01403: no data found'. It does this once per connection; the next call this passes fine. However then, when creating an oracle object to insert and trying to set one of its fields, I get a 'Operation is not valid due to the current state of the object' in the C# code.

The code being used is the below:

Code: Select all

public async Task<IEnumerable<CalculatedService>> GetEmployeeSummedServiceAsync(IEnumerable<EmployeeDateRange> employees)
{
    using (var connection = (OracleConnection)_connection())
    {
        try
        {
            connection.Open();
            var query = _scriptSource.GetScript("calculateService");
            var command = new OracleCommand(query, connection);

            var parameter = new OracleParameter("InputData", OracleDbType.Table, "calculateServiceInputList"); // fails here with 'no data found...'
            var table = new OracleTable(OracleType.GetObjectType("calculateServiceInputList", connection));

            var itemType = OracleType.GetObjectType("calculateServiceInputRecord", connection);
            foreach (var employee in employees)
            {
                var item = new OracleObject(itemType) { IsNull = false };
                item["employeeId"] = employee.EmployeeId; // Fails here with 'not valid for current state...;'
                item["startDate"] = employee.StartDate;
                item["endDate"] = employee.EndDate;
                table.Add(item);
            }
            parameter.Value = table;

            command.Parameters.Add(parameter);

            var reader = await command.ExecuteReaderAsync();
            return reader.Parse<CalculatedService>().AsList();
        }
        catch(Exception ex)
        {
            System.Diagnostics.Debugger.Break();
            throw ex;
        }
    }
}
Any ideas? This is against an Oracle 11g database. The code above is C# running on dotnet core 2.2, and I am using Dapper 1.60.6 (for that parse code, not that it ever reaches it) and Devart dotConnect for Oracle 9.7.770

chris.pritchard
Posts: 5
Joined: Mon 24 Jun 2019 21:15

Re: Table parameters initialisation issue.

Post by chris.pritchard » Wed 26 Jun 2019 00:06

I have managed to get it working by doing two things:

1. First, rather than

Code: Select all

OracleType.GetObjectType("calculateServiceInputList", connection)
I had to use

Code: Select all

OracleType.GetObjectType("DEV03", "CALCULATESERVICEINPUTLIST", connection)
, specifying the schema and the type name both in uppercase.

2. Secondly, it didn't work with synonyms. The schema we use is different than dev03, above, and I had made a synonym like

Code: Select all

create synonym epu_api.calculateServiceInputList for dev03.calculateServiceInputList;
. This doesnt work, and triggers the no-data error apparently.

What a mess. Unsure if this is a dotConnect issue or an Oracle one - I suspect its an oracle issue but the devart side could use some better exception handling.

Pinturiccio
Devart Team
Posts: 2418
Joined: Wed 02 Nov 2011 09:44

Re: Table parameters initialisation issue.

Post by Pinturiccio » Tue 02 Jul 2019 15:10

Sorry for the late response. Please specify where your objects and synonyms are located. In which schema (epu_api or dev03) each object and synonym is created. And specify your query that you use in OracleCommand. If it is a stored procedure, then specify the DDL script of this stored procedure.

And specify the C# code with synonyms which reproduces the issue.

chris.pritchard
Posts: 5
Joined: Mon 24 Jun 2019 21:15

Re: Table parameters initialisation issue.

Post by chris.pritchard » Wed 03 Jul 2019 00:52

The types were in dev03, created using the following script:

Code: Select all

create type calculateServiceInputRecord as object (employeeId varchar2(8 byte), 
                                                  startDate  date,
                                                  endDate    date);
/
create type calculateServiceInputList is table of calculatServiceInputRecord;
/
create synonym epu_api.calculateServiceInputRecord for dev03.calculateServiceInputRecord;
create synonym epu_api.calculateServiceInputList for dev03.calculateServiceInputList;
/
The script being run is complicated, and also confidential, but where the types and table parameter was coming into play was just at the beginning:

Code: Select all

with
  inputs as (
    select employeeId,
           startDate,
           endDate
      from table(:InputData)
  ),
-- rest of the code using the above
The code I posted originally is what would trigger the issue, in the two places indicated by comments. Seemed to be some sort of weird misfire when the type could not be found or was accessed via a schema.

Cheers

Pinturiccio
Devart Team
Posts: 2418
Joined: Wed 02 Nov 2011 09:44

Re: Table parameters initialisation issue.

Post by Pinturiccio » Thu 04 Jul 2019 15:46

We have reproduced the following case:
1. We have three users: user1, user2 and user3.
2. The calculatServiceInputRecord and calculateServiceInputList objects are created in user1.
3. user2 has privileges to access user1 objects. I create 2 synonyms in user2, and your code works successfully.
4. user3 does not have privileges to access user1 objects. I tried to create synonyms via a user3 connection and got the error: "ORA-01031: insufficient privileges". Then I sign in as sys and run the following queries:

create synonym user3.calculateServiceInputList for user1.calculateServiceInputList;
create synonym user3.calculateServiceInputRecord for user1.calculateServiceInputRecord;

And these synonyms are successfully created inside the user3 schema. And when I run your code I got the error: "ORA-01403: no data found".

Looks like your synonyms are created by a user with more privileges than epu_api has. But when you try to use this synonyms under epu_api, you get an error.

chris.pritchard
Posts: 5
Joined: Mon 24 Jun 2019 21:15

Re: Table parameters initialisation issue.

Post by chris.pritchard » Thu 04 Jul 2019 20:58

That sounds about right. However I am pretty sure it is not true that in order for synonyms to work, that they need to be created by the user who uses them - happy to be corrected if I am wrong.

Note, that after the no data found error from Oracle, if the code is rerun (presumably reusing the same connection object) you will no longer get this error. Instead I get a devart dotconnect error when I assign a value to an OracleObject property. The error seems to indicate something inside the type was not initialised correctly.

This code is being used inside a REST endpoint, so to replicate the above I start the web service, call the endpoint once to get the nodata error, then call it again (without killing the service) to get the oracle object initialisation error.

Pinturiccio
Devart Team
Posts: 2418
Joined: Wed 02 Nov 2011 09:44

Re: Table parameters initialisation issue.

Post by Pinturiccio » Tue 09 Jul 2019 13:28

chris.pritchard wrote:Note, that after the no data found error from Oracle, if the code is rerun (presumably reusing the same connection object) you will no longer get this error. Instead I get a devart dotconnect error when I assign a value to an OracleObject property. The error seems to indicate something inside the type was not initialised correctly.
We could not reproduce such behaviour. We always get the same error "no data found" for each run. We will investigated the issue further and post here about the results.

chris.pritchard
Posts: 5
Joined: Mon 24 Jun 2019 21:15

Re: Table parameters initialisation issue.

Post by chris.pritchard » Tue 09 Jul 2019 21:01

This console app reproduces the issue neatly:

Code: Select all

using Devart.Data.Oracle;
using System;
using System.Diagnostics;
using System.Threading.Tasks;

namespace TableErrorReproducer
{
    class Program
    {
        const string epuApiConnString = "<conn string using synonymed user>";

        static async Task Main()
        {
            using (var connection = new OracleConnection(epuApiConnString))
            {
                var ex1 = await CallWithTableParams(connection);
                var ex2 = await CallWithTableParams(connection);
                Debugger.Break();
            }
        }

        static async Task<Exception> CallWithTableParams(OracleConnection connection)
        {
            var employees = new[] { new { EmployeeId = "001", StartDate = DateTime.Now, EndDate = DateTime.Now } };

            try
            {
                connection.Open();
                var command = new OracleCommand("doesnt matter for test", connection);

                var parameter = new OracleParameter("InputData", OracleDbType.Table, "calculateServiceInputList"); // fails here with 'no data found...'
                var table = new OracleTable(OracleType.GetObjectType("calculateServiceInputList", connection));

                var itemType = OracleType.GetObjectType("calculateServiceInputRecord", connection);
                foreach (var employee in employees)
                {
                    var item = new OracleObject(itemType) { IsNull = false };
                    item["employeeId"] = employee.EmployeeId; // Fails here with 'not valid for current state...;'
                    item["startDate"] = employee.StartDate;
                    item["endDate"] = employee.EndDate;
                    table.Add(item);
                }
                parameter.Value = table;

                command.Parameters.Add(parameter);

                var reader = await command.ExecuteReaderAsync();
                return null;
            }
            catch (Exception ex)
            {
                return ex;
            }
        }
    }
}
When the code above halts, the two exceptions should be different.

Contents of the csproj for the above:

Code: Select all

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp2.2</TargetFramework>
    <LangVersion>latest</LangVersion>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Devart.Data.Oracle" Version="9.7.770" />
  </ItemGroup>

</Project>

Post Reply