Problem with OracleTable Parameter

Problem with OracleTable Parameter

Postby Thomas » Wed 28 Dec 2005 09:46

I execute some PL/SQL code with one OUT Parameter of type
OracleTable.

This is how I add the parameter to the OracleCommand:
##################################

opRESULT = new OracleParameter();
opRESULT.ParameterName = ":RESULT";
opRESULT.DbType = resultType;

if( opRESULT.DbType == DbType.Object )
opRESULT.ObjectTypeName = ResultTypeName;

if( isReturnValueOracleCollection )
opRESULT.OracleDbType = CoreLab.Oracle.OracleDbType.Table;

opRESULT.Direction = ParameterDirection.Output;
command.Parameters.Add( opRESULT );

#################################

After calling ExecuteNonQuery() the parameter shows the right number of elements for the table/collection.
When trying to access the elements of the table (either using foreach(...) or using the indexer) I get the following error:

CoreLab.Oracle.OracleException: OCI-22881: Message 22881 not found; product=RDBMS; facility=OCI

at a.a.ad.b(Int32 A_0)
at CoreLab.Oracle.OracleArray.d(Int32 A_0)
at CoreLab.Oracle.OracleTable.b(Int32 A_0)
at CoreLab.Oracle.OracleArray.a(Int32 A_0)
at CoreLab.Oracle.OracleArray.get_Item(Int32 i)
at SLS.FrameWork.MiddleTier.OracleObjectBase.invokeStaticDBMethod(Transaction transaction, String className, String methodName, DbType resultType, String ResultTypeName, Boolean hasReturn, Boolean isReturnValueOracleCollection, DBMethodParameter[] parameters).

OraDirect.Net Version is 3.00.00

Any idea?

Regards,

Thomas
Thomas
 

Postby Paul » Fri 30 Dec 2005 14:28

We cannot reproduce your problem on Oracle client 9.2.0.4 and Oracle server 9.2.0.4
with the following code


Code: Select all
CREATE TYPE TOraNestedSubType AS OBJECT (
  Num NUMBER,
  Str VARCHAR2(10)
);

CREATE TYPE TOraNestedType AS OBJECT (
  Num NUMBER,
  Str VARCHAR2(30),
  Obj TOraNestedSubType,
  Dat DATE
);

CREATE TYPE TOraNestedTable AS TABLE OF TOraNestedType;

CREATE TABLE Ora_NestedTable (
  Code NUMBER PRIMARY KEY,
  Content TOraNestedTable
)
NESTED TABLE Content STORE AS Content_Table;

INSERT INTO Ora_NestedTable
  (Code, Content)
VALUES
  (1, TOraNestedTable(TOraNestedType(111, 'AAAAA', TOraNestedSubType(44, 'YYY'), NULL)));

INSERT INTO Ora_NestedTable
  (Code, Content)
VALUES
  (2, TOraNestedTable(TOraNestedType(22, 'BBB', TOraNestedSubType(456, 'UUU'), NULL),
                       TOraNestedType(333, 'TTT', TOraNestedSubType(234, 'SSSSS'), NULL)));

COMMIT;


Code: Select all
      oracleConnection1.Open();
      OracleParameter opRESULT = new OracleParameter();
      opRESULT.ParameterName = ":p";
      opRESULT.OracleDbType = CoreLab.Oracle.OracleDbType.Table;
      opRESULT.ObjectTypeName = "TOraNESTEDTABLE";
      opRESULT.Direction = ParameterDirection.Output;
      oracleCommand2.CommandText = "begin\n  SELECT T.CONTENT into :p FROM Ora_NestedTable T where t.code=1;\nend;";
      oracleCommand2.Parameters.Add( opRESULT );       
      oracleCommand2.ExecuteNonQuery();
      OracleTable t = (OracleTable)opRESULT.OracleValue;
      string result = "";
      for (int i = 0; i < t.Count; i++) {
        OracleObject o = (OracleObject)t[i];
        result = result + o["Num"] + o["Str"];
        GC.KeepAlive(o);
      }
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby Paul » Fri 30 Dec 2005 14:30

Please try the last version of OraDirect .NET. Some bugs with OracleTable are fixed there
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby DataKeeper » Fri 23 Mar 2007 13:58

Paul wrote:Please try the last version of OraDirect .NET. Some bugs with OracleTable are fixed there


oracleCommand.CreateParameters();
Still have a problem when get Parameters.

This Code repear it.

Code: Select all
oracleCommand.CreateParameters();
// BUG в OraDirect
foreach (OracleParameter oracleParameter in oracleCommand.Parameters)
{
  if (!string.IsNullOrEmpty(oracleParameter.ObjectTypeName))
  {
    OracleObject oracleObject = new OracleObject(oracleParameter.ObjectTypeName,oracleCommand.Connection);
                    oracleParameter.OracleDbType = oracleObject.ObjectType.DbType;
  }
}
[/code]
DataKeeper
 
Posts: 8
Joined: Fri 23 Mar 2007 13:43

Postby Alexey » Wed 28 Mar 2007 06:43

Still have a problem when get Parameters.
Please describe the problem in detail. What version of OraDirect .NET you use?
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby DataKeeper » Fri 27 Apr 2007 09:40

Alexey wrote:
Still have a problem when get Parameters.
Please describe the problem in detail. What version of OraDirect .NET you use?


Code: Select all
PROCEDURE get_objects_by_filter(p_obj   IN t_plc_place,
                                   p_to_date_i     IN DATE,
                                   p_ntab_o OUT nt_plc_obj) IS ....


nt_plc_obj is array but
oracleParameter.OracleDbType say Object

latest version *.21
DataKeeper
 
Posts: 8
Joined: Fri 23 Mar 2007 13:43

Postby Alexey » Sat 28 Apr 2007 12:03

We have OracleDbType.Object type for three types: Array, Table and Object.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby DataKeeper » Thu 03 May 2007 15:12

Alexey wrote:We have OracleDbType.Object type for three types: Array, Table and Object.


To be clear.

Code: Select all
CREATE TYPE nt_plc_obj
AS TABLE OF t_plc_place

PROCEDURE get_objects_by_filter(p_obj   IN t_plc_place,
                                   p_to_date_i     IN DATE,
                                   p_ntab_o OUT nt_plc_obj) IS ....

and
after
Code: Select all
oracleCommand.CreateParameters();

parameters
oracleCommand.Parameters[0].OracleDbType is OracleDbType.Object (correct)
oracleCommand.Parameters[1].OracleDbType is null (correct)
oracleCommand.Parameters[1].OracleDbType is OracleDbType.Object (error) -> must be OracleDbType.Table
DataKeeper
 
Posts: 8
Joined: Fri 23 Mar 2007 13:43

Postby Alexey » Mon 07 May 2007 07:14

I see. We are investigating this bug. I'll notify you on results as soon as possible.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Alexey » Mon 07 May 2007 08:17

This problem is fixed.
Look forward to the next build.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby DataKeeper » Tue 08 May 2007 06:39

Alexey wrote:This problem is fixed.
Look forward to the next build.


Thanks. I will....
DataKeeper
 
Posts: 8
Joined: Fri 23 Mar 2007 13:43

Postby Alexey » Wed 06 Jun 2007 10:59

New build of OraDirect .NET is available for download now.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for Oracle