new OracleTable fails with InvalidOperationException

[ralovets]

hi.

I'm trying to pass a table into package as input parameter. I generated code for package and the last thing to do is pass the instance of parameter to it.

Code: Select all

var tableType = OracleType.GetObjectType("schema_name", "table_type",  conn);
                    OracleTable table = new OracleTable(tableType);

it fails with the message: Oracle type '"schema_name"."table_type"' is not compatible with the OracleArray class. A typed VARRAY data type expected.

that's how I defined my types in oracle. They are in the same schema, where the package is.

Code: Select all

CREATE OR REPLACE TYPE schema_name.record_type iS object
(
  effective_date             DATE,
  snap_time                  VARCHAR2(10),
  id                NUMBER,
  tid                  number,
  tiid    NUMBER,
  ov               NUMBER,
  oc                NUMBER,
  tc                NUMBER,
  tv               NUMBER
  );
  
  
CREATE OR REPLACE TYPE schema_name.table_type is table of schema_name.record_type ;

I'm guessing that the problem occurs on the stage when I call

Code: Select all

var tableType = OracleType.GetObjectType("schema_name", "table_type",  conn);

because when I look at the instance that was created tableType.ItemDbType and ItemObjectType properties didn't get initialized.

Please, help me figure out how to make this work.

Devart.Data.Oracle version 9.5.454.0, oracle 11g

[Pinturiccio]

Sorry for the late response. We could not reproduce the issue. Please provide the following information:
1. Connection string (roughly, without credentials).
2. .NET Framework of your application.
3. Are table_type and record_type created inside a package or not linked to a package?

The following code does not reproduce the issue:

Code: Select all

OracleConnection conn = new OracleConnection("OCI or Direct connection");
conn.Open();

var tableType = OracleType.GetObjectType("schema_name", "table_type", conn);
OracleTable table = new OracleTable(tableType);
conn.Close();

How should we change this code to reproduce the issue?

[ralovets]

hi Pinturiccio.

Thanks for checking that for me.
1. Server=<name>;Persist Security Info=True. We connecting using DB wallet user if that makes any difference, i don't know much about it myself.
2. 4.5.1
3. I created separate types in the same schema, but not inside the package, because I read somewhere, that it doesn't support internal types.

In your provided example I only change connection string and it throws an exception. So probably connection string is the issue.

Tbh I already found a work around, but still am curious what's wrong with my initial approach.
my workaround is to create array type instead and then i pass it to code generated by oracle package wizard and it works just fine.
var table = new OracleArray("schema_name.table_name", conn);
and in this case it does work with my connection string.
If you could explain that for me, i'd appreciate that.

[Pinturiccio]

Is the issue reproduced when you connect to the same database using login and password without Oracle wallet?

[ralovets]

Yes, sorry, it's been a while since I posted it and before my first reply. I also tried connection string like
"User Id=<username>;Password=password;Server=<server>;Persist Security Info=True"
and I tried to connect with the user = schema_name, schema, where that type was created, so I ruled out access issues.

[Pinturiccio]

Please clarify the following:
1. What result do you get when connecting with the connection string "User Id=<username>;Password=password;" where user is the same as in Oracle wallet?
2. What result do you get when connecting with the connection string "User Id=<username>;Password=password;" where user is the same as schema, where that type was created?
3. What result do you get when connecting using Oracle wallet?
4. What result do you get when connecting using Oracle wallet, when the user in Oracle wallet is the same as schema, where that type was created?

[ralovets]

hi Pinturiccio.

I was trying options and discovered that this works:

Code: Select all

 var tableType = OracleType.GetObjectType("schema_name.type_name", conn);

whereas this throws an exception:

Code: Select all

 var tableType = OracleType.GetObjectType("schema_name", "type_name", conn);

the difference is only how I pass schema name and type name together or as different parameters.
This is all I need. We can close the ticket now. Many thanks for help.

[Pinturiccio]

Thank you for the details. Could you please execute one more test. Use the following code:

Code: Select all

var tableType = OracleType.GetObjectType("SCHEMA_NAME", "TYPE_NAME", conn);

Use the names of your schema and type in the upper case. Is the issue reproduced in this case?

[ralovets]

nope, works fine with upper case

[Pinturiccio]

Thank you for the assistance. We have reproduced the issue when names of the schema and type are not in the upper case. We will investigate the issue and post here about the results as soon as possible.

[ralovets]

Great! I don't mind it any longer but hopefully this post might help somebody else.