Hi,
I'm new to oracle and c#, which possibly means the code is not as efficient as it can be...
I have a program that stores data as UDTs inside Object Tables. Unfortunately I'm running into issues when using the "obj = (Obj)reader.GetValue(0);" methods. Execution of the first instance runs perfectly and I get the correct result, however, on the second execution of the same query I get AccessViolationException errors on various objects I'm trying to map.
I have deleted/re-inserted all the data a few times, this resulted in getting the error on different objects (e.g. object 511 failed last time, now object 4 fails) while the data remains the same. Initially I thought this could be due to garbage collection not cleaning up the previous data and the program is trying to overwrite the same memory, but this cant be the case. Every object list I create from Oracle data is stored as a separate list on a separate new form, therefore they should not overlap or reference each other at all.
Additionally the reader.getvalue and reader.getoraclevalue methods are the only ones which fail. If I convert the objects to their base components and map them manually to a new object everything works. However I see this as defeating the whole point of having UDTs. Please take a look at the code below and let me know if there is some obvious error:
Generating a table to look at the data:
DataTB = new DataTable();
BindingSource _bs = new BindingSource();
AddColumn("Name", typeof(string));
generatePrColumns(p_ObjDefs);
ClassTree = new CTree( p_ObjDefs, PrColumns);
if (ClassTree.Objects != null && ClassTree.Objects.Count != 0)
{
addObjectsToRow(DataType.Schema); //this flattens the object into a row and adds the row to the table
}
_bs.DataSource = DataTB;
return _bs;
The Bindingsource is then used in a DataGridView to view the data.
public class CTree:IDisposable
{
private List<Obj> m_Objects = null;
public List<Obj> Objects
{
get
{
if (m_Objects != null)
return m_Objects;
else
{
m_Objects = new List<Obj>();
return m_Objects;
}
}
set
{ m_Objects = value; }
}
public CTree(string[] p_ObjDefs, List<PrColumn> prCols)
{
string x = "Select value(o) ,( Select value(pr) From DataObjPr pr Where (pr.objid = o.objid) And (pr.discid = 'NA') And (pr.prdef = 'PrDef_ParentTag')) ,( Select value(pr) From DataObjPr pr Where (pr.objid = o.objid) And (pr.discid = 'NA') And (pr.prdef = 'PrDef_Discipline')) ,( Select value(pr) From DataObjPr pr Where (pr.objid = o.objid) And (pr.discid = 'NA') And (pr.prdef = 'PrDef_SourceApplication ')) ,( Select value(pr) From DataObjPr pr Where (pr.objid = o.objid) And (pr.discid = 'NA') And (pr.prdef = 'PrDef_Status')) ,( Select value(pr) From DataObjPr pr Where (pr.objid = o.objid) And (pr.discid = 'NA') And (pr.prdef = 'PrDef_OE')) ,( Select value(pr) From DataObjPr pr Where (pr.objid = o.objid) And (pr.discid = 'NA') And (pr.prdef = 'PrDef_MEL')) ,( Select value(pr) From DataObjPr pr Where (pr.objid = o.objid) And (pr.discid = 'NA') And (pr.prdef = 'PrDef_Comments')) ,( Select value(pr) From DataObjPr pr Where (pr.objid = o.objid) And (pr.discid = 'NA') And (pr.prdef = 'PrDef_Supplier')) ,( Select value(pr) From DataObjPr pr Where (pr.objid = o.objid) And (pr.discid = 'NA') And (pr.prdef = 'PrDef_CLASS')) ,( Select value(pr) From DataObjPr pr Where (pr.objid = o.objid) And (pr.discid = 'NA') And (pr.prdef = 'PrDef_Facility')) ,( Select value(pr) From DataObjPr pr Where (pr.objid = o.objid) And (pr.discid = 'NA') And (pr.prdef = 'PrDef_ServiceCode')) ,( Select value(pr) From DataObjPr pr Where (pr.objid = o.objid) And (pr.discid = 'NA') And (pr.prdef = 'PrDef_System')) ,( Select value(pr) From DataObjPr pr Where (pr.objid = o.objid) And (pr.discid = 'NA') And (pr.prdef = 'PrDef_EquipmentTypeCode')) ,( Select value(pr) From DataObjPr pr Where (pr.objid = o.objid) And (pr.discid = 'NA') And (pr.prdef = 'PrDef_Sequence')) ,( Select value(pr) From DataObjPr pr Where (pr.objid = o.objid) And (pr.discid = 'NA') And (pr.prdef = 'PrDef_Suffix')) From DataObj o WHERE o.objdef IN('Equipment', 'Instrument', 'Valve', 'Line')";
OracleConnection conn = new OracleConnection(TAT.Properties.Settings.Default.OracleAdminConnection);
//configure command
OracleCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = x;
//open connection and execute command
conn.Open();
//read the returned data
OracleDataReader reader = cmd.ExecuteReader();
Obj obj = null;
ObjPr objPr = null;
//convert reader to class tree
while (reader.Read()) \\<==== error can be here on 2nd execution
{
if (!reader.IsDBNull(0))
{
obj = (Obj)reader.GetValue(0);<==== error can be here on 2nd execution
//clone property schema
obj.PrColumns.AddRange(prCols.Select(p => (PrColumn)p.Clone()).ToList());
obj.Properties = new List<ObjPr>();
//property loop
for (int i = 1; i < reader.VisibleFieldCount; i++)
{
if (!reader.IsDBNull(i))
{
objPr = (ObjPr)reader.GetValue(i);<==== error can be here on 2nd execution (can also be within the ToCustomObject method of the object)
obj.Properties.Add(objPr);
}
}
Objects.Add(obj);
}
}
// Clean up
reader.Close();
conn.Close();
conn.Dispose();
reader.Dispose();
cmd.Dispose();
}
Scripts:
CREATE OR REPLACE TYPE TAT.Obj AS OBJECT
(OBJID VARCHAR2 (128 BYTE),
objname VARCHAR2 (128 BYTE),
objdef VARCHAR2 (128 BYTE),
creationUser VARCHAR2 (128 BYTE),
creationDate DATE,
UpdateUser VARCHAR2 (128 BYTE),
updateType VARCHAR2 (128 BYTE),
updateDate DATE);
CREATE OR REPLACE TYPE TAT.ObjPr AS OBJECT
(PRID VARCHAR2 (128 BYTE),
OBJID VARCHAR2 (128 BYTE),
DISCID VARCHAR2 (128 BYTE),
PRDEF VARCHAR2 (128 BYTE),
strvalue VARCHAR2 (4000 BYTE),
uom VARCHAR2 (128 BYTE),
creationUser VARCHAR2 (128 BYTE),
creationDate DATE,
UpdateUser VARCHAR2 (128 BYTE),
updateType VARCHAR2 (128 BYTE),
updateDate DATE);
CREATE TABLE TAT.DATAOBJ OF TAT.OBJ
( CONSTRAINT OBJID_PK
PRIMARY KEY
(OBJID)
USING INDEX TAT.OBJID_PK)
OBJECT ID PRIMARY KEY
TABLESPACE SPFDATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX TAT.DATAOBJ_BYID ON TAT.DATAOBJ
(OBJID, OBJNAME, OBJDEF)
LOGGING
TABLESPACE SPFDATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE UNIQUE INDEX TAT.DATAOBJ_BYNAME ON TAT.DATAOBJ
(OBJNAME, OBJDEF)
LOGGING
TABLESPACE SPFDATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE TABLE TAT.DATAOBJPR OF TAT.OBJPR
( CONSTRAINT PRID_PK
PRIMARY KEY
(PRID)
USING INDEX TAT.PRID_PK)
OBJECT ID PRIMARY KEY
TABLESPACE SPFDATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX TAT.DATAOBJPR_OBJID ON TAT.DATAOBJPR
(OBJID, DISCID, PRDEF)
LOGGING
TABLESPACE SPFDATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
Here is the sample data: http://www.2shared.com/document/NeEjaw8F/Book1.html