0 Replies Latest reply: Nov 11, 2011 1:11 AM by 899642 RSS

    Callin multiple refcursors from VB/VBA

    899642
      I have a package with a procedure and that procedure returns 2 ref cursors. I have 2 select statements in Package body one for each ref cursor. It's executing fine in Oracle.

      Now I am trying to call this package from MS Access using OO4O. (Note: I have already tested all this using one ref cursor).

      My VB code goes like this

      I am calling this package in Access using VB code

      'Cursor declaration

      oOraDatabase.Parameters.Add "in_entity_code", entityCode, ORAPARM_INPUT
      oOraDatabase.Parameters("in_entity_code").ServerType = ORATYPE_VARCHAR2

      oOraDatabase.Parameters.Add "in_effective_date", effectiveDate, ORAPARM_INPUT
      oOraDatabase.Parameters("in_effective_date").ServerType = ORATYPE_DATE

      oOraDatabase.Parameters.Add "in_full_legal_name", fullLegalName, ORAPARM_INPUT
      oOraDatabase.Parameters("in_full_legal_name").ServerType = ORATYPE_VARCHAR2

      oOraDatabase.Parameters.Add "in_result_set", resultSet, ORAPARM_INPUT
      oOraDatabase.Parameters("in_result_set").ServerType = ORATYPE_VARCHAR2



      oOraDatabase.Parameters.Add "out_approved_limits", 0, ORAPARM_OUTPUT
      oOraDatabase.Parameters("out_approved_limits").ServerType = ORATYPE_CURSOR

      oOraDatabase.Parameters.Add "out_assigned_limits", 0, ORAPARM_OUTPUT
      oOraDatabase.Parameters("out_assigned_limits").ServerType = ORATYPE_CURSOR

      oOraDatabase.Parameters.Add "out_assigned_instr_limits", 0, ORAPARM_OUTPUT
      oOraDatabase.Parameters("out_assigned_instr_limits").ServerType = ORATYPE_CURSOR

      oOraDatabase.Parameters.Add "out_cp_relationships", 0, ORAPARM_OUTPUT
      oOraDatabase.Parameters("out_cp_relationships").ServerType = ORATYPE_CURSOR

      oOraDatabase.Parameters.Add "out_coll_threshold", 0, ORAPARM_OUTPUT
      oOraDatabase.Parameters("out_coll_threshold").ServerType = ORATYPE_CURSOR


      OraDatabase.Parameters.Add "EMPCURSOR1", 0, ORAPARM_OUTPUT
      OraDatabase.Parameters("EMPCURSOR1").serverType = ORATYPE_CURSOR


      'Calling the procedure
      RowCount = oOraDatabase.DbExecuteSql("Begin pkgclm_fe_analysis.procclm_group_limits(:in_entity_code, :in_full_legal_name, :in_result_set, :in_effective_date, :out_approved_limits, :out_assigned_limits, :out_assigned_instr_limits, :out_cp_relationships, :out_coll_threshold); end;")

      Set oOraDynaSet = oOraDatabase.Parameters("out_approved_limits").Value
      Set oOraDynaSet2 = oOraDatabase.Parameters("out_assigned_limits").Value
      Set oOraDynaSet3 = oOraDatabase.Parameters("out_assigned_instr_limits").Value
      Set oOraDynaSet4 = oOraDatabase.Parameters("out_cp_relationships").Value
      Set oOraDynaSet5 = oOraDatabase.Parameters("out_coll_threshold").Value

      The above code is supposed to call the refcursors and populate data a excel table. (Note: No fetch statement in my procedure).
      When I execute it, it works fine, but when I view the table, only first row is populated.


      What the problem over here?
      By right there should be two rows of data for out_approved_limits but it only retrieve one record.