2 Replies Latest reply on Mar 13, 2008 4:13 PM by 618966

    Problems returning VARRAY (or  TABLE) / method  on _IOraDatabase failure

    618966
      I am trying to return a VARRAY through OO4O (currently using VB6) but am getting an error back that I don't seem to be able to resolve.

      The SQL procedure is declared as:

      PROCEDURE "RD_GET_TOPOAREAS2_SIZED" (

      minX IN NUMBER, minY IN NUMBER, maxX IN NUMBER, maxY IN NUMBER,
      maxArea IN NUMBER, dt IN DATE, timing OUT NUMBER,
      IDLIST OUT v_numArray, RETDATA OUT NOCOPY t_cursor
      );

      types are defined in the package spec as:

      TYPE t_cursor IS REF CURSOR ;
      TYPE v_numArray IS VARRAY(20000) OF NUMBER;
      TYPE nt_numArray IS TABLE OF NUMBER;

      The calling code is:

      Dim odb As OraDatabase
      Dim oparams As OraParameters
      Dim odyn As OraDynaset

      Set oparams = odb.Parameters

      oparams.Add "minx", 406000, ORAPARM_INPUT, ORATYPE_NUMBER
      oparams.Add "miny", 259000, ORAPARM_INPUT, ORATYPE_NUMBER
      oparams.Add "maxx", 410000, ORAPARM_INPUT, ORATYPE_NUMBER
      oparams.Add "maxy", 261000, ORAPARM_INPUT, ORATYPE_NUMBER
      oparams.Add "maxarea", 100, ORAPARM_INPUT, ORATYPE_NUMBER
      oparams.Add "dt", Null, ORAPARM_INPUT, ORATYPE_DATE
      oparams.Add "time", 0, ORAPARM_OUTPUT, ORATYPE_NUMBER
      oparams.Add "IDLIST", Null, ORAPARM_OUTPUT,
      ORATYPE_VARRAY, "V_NUMARRAY"
      oparams.Add "RETDATA", Null, ORAPARM_OUTPUT, ORATYPE_CURSOR

      Set odyn = odb.CreatePlsqlDynaset(sql, "RETDATA", 8)

      Calling the sql gives me the following error message:

      Run-time error: '-2147417848 (80010108)':
      Method 'CreatePlsqlDynaset' of object '_IOraDatabase' failed

      I have also tried passing the parameter as:

      oparams.AddTable "IDLIST", ORAPARM_OUTPUT, ORATYPE_NUMBER, 20000

      but this gives me a parameter type mismatch.

      Ideally, I would prefer to return the data in a nested table (type nt_numArray),
      but I can't get the call to work with that either (same error).

      I've seen the other couple of posts on this subject, and the fact that a bug giving the same error message was supposed to have been fixed in v 4.3.
      I am currently using the version that came with 11.1.0.6.20 which is v 5.0 I think.

      What's going wrong, and how can I fix it?

      Richard