Problem with OracleTable Parameter

Thomas · Post by **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

Paul · Post by **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 · Post by **Paul** » Fri 30 Dec 2005 14:30

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

DataKeeper · Post by **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]

Alexey · Post by **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?

DataKeeper · Post by **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

Alexey · Post by **Alexey** » Sat 28 Apr 2007 12:03

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

DataKeeper · Post by **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

Alexey · Post by **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 · Post by **Alexey** » Mon 07 May 2007 08:17

This problem is fixed.
Look forward to the next build.

DataKeeper · Post by **DataKeeper** » Tue 08 May 2007 06:39

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

Thanks. I will....

Alexey · Post by **Alexey** » Wed 06 Jun 2007 10:59

New build of OraDirect .NET is available for download now.