Page 1 of 1
new OracleTable fails with InvalidOperationException
Posted: Fri 06 Apr 2018 10:13
by 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
Re: new OracleTable fails with InvalidOperationException
Posted: Wed 11 Apr 2018 14:57
by 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?
Re: new OracleTable fails with InvalidOperationException
Posted: Wed 11 Apr 2018 15:14
by 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.
Re: new OracleTable fails with InvalidOperationException
Posted: Tue 17 Apr 2018 14:03
by Pinturiccio
Is the issue reproduced when you connect to the same database using login and password without Oracle wallet?
Re: new OracleTable fails with InvalidOperationException
Posted: Tue 17 Apr 2018 14:29
by 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.
Re: new OracleTable fails with InvalidOperationException
Posted: Thu 19 Apr 2018 13:27
by 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?
Re: new OracleTable fails with InvalidOperationException
Posted: Mon 23 Apr 2018 09:41
by 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.
Re: new OracleTable fails with InvalidOperationException
Posted: Fri 27 Apr 2018 09:56
by 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?
Re: new OracleTable fails with InvalidOperationException
Posted: Fri 27 Apr 2018 10:00
by ralovets
nope, works fine with upper case
Re: new OracleTable fails with InvalidOperationException
Posted: Fri 27 Apr 2018 12:19
by 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.
Re: new OracleTable fails with InvalidOperationException
Posted: Fri 27 Apr 2018 12:22
by ralovets
Great! I don't mind it any longer but hopefully this post might help somebody else.