0 Replies Latest reply on Nov 13, 2013 8:15 AM by user11800215

    What's wrong with this code ???

    user11800215

      Hi all,

      When running the code below, it gets all the records I want from the table,  but will only return 1 record. Where am I going wrong ?

      Regards

      Ric

       

      Sub test()

         
          Set OraSession = CreateObject("OracleInProcServer.XOraSession")
          Set OraDatabase = OraSession.OpenDatabase("******", "*****", 0)
         
      CC = "421"
      AC = "06"
      PRG = "32"
      ACT = "GI5"
      PROJ = "3401"
      JOB = "LGS"

         
          querystring = querystring + " select code_combination_id,segment1,segment10,segment11, "
          querystring = querystring + " segment12,segment13,segment14,segment15 from "
          querystring = querystring + " gl.gl_code_combinations"
          querystring = querystring + " where segment1 = :P_CC "
          querystring = querystring + " and segment10 = :P_AC"
          querystring = querystring + " and segment13 between '0700' and '3999'"
          querystring = querystring + " and segment11 = :P_PRG"
          querystring = querystring + " and segment12 = :P_ACT"
          querystring = querystring + " and segment14 = :P_PROJ"
          querystring = querystring + " and segment15 = :P_JOB "
         
      OraDatabase.Parameters.Add "P_CC", CC, ORAPARM_INPUT, 1
      OraDatabase.Parameters.Add "P_AC", AC, ORAPARM_INPUT, 1
      OraDatabase.Parameters.Add "P_PRG", PRG, ORAPARM_INPUT, 1
      OraDatabase.Parameters.Add "P_ACT", ACT, ORAPARM_INPUT, 1
      OraDatabase.Parameters.Add "P_PROJ", PROJ, ORAPARM_INPUT, 1
      OraDatabase.Parameters.Add "P_JOB", JOB, ORAPARM_INPUT, 1

       

        Set Gl_CCID_dynaset = OraDatabase.DBCreateDynaset(querystring, 0) 'returns 43 records
         
      For Each Param In OraDatabase.Parameters
         OraDatabase.Parameters.Remove Param.Name
      Next
             

      ' this should create an array parameter which I can use to return the 43 records

             

      OraDatabase.Parameters.AddTable "P_CCID_ARRAY", ORAPARM_INPUT, ORATYPE_NUMBER, Gl_CCID_dynaset.RecordCount, 22
          If Gl_CCID_dynaset.RecordCount > 0 Then
                  Gl_CCID_dynaset.MoveFirst
                  For y = 0 To Gl_CCID_dynaset.RecordCount - 1
                      OraDatabase.Parameters("P_CCID_ARRAY").put_Value Gl_CCID_dynaset.Fields(0).Value, y
                      Gl_CCID_dynaset.MoveNext
                  Next
          End If

      x = 1

       

      ' the next line uses the OraDatabase.Parameters.AddTable "P_CCID_ARRAY"  variable

      ' but only returns 1 row


          Set OraDynaset = OraDatabase.CreateDynaset("SELECT * FROM GL.GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID IN(:P_CCID_ARRAY)", 0)
              If OraDynaset.RecordCount > 0 Then
                  For Each Fld In OraDynaset.Fields
                    Cells(1, x) = Fld.Name
                    x = x + 1
                  Next
                 
                  OraDynaset.MoveFirst
                  For y = 0 To OraDynaset.RecordCount - 1
                      For x = 0 To OraDynaset.Fields.Count - 1
                          Cells(y + 2, x + 1).Value = OraDynaset.Fields(x).Value
                      Next
                      OraDynaset.MoveNext
                  Next
              End If


      For Each Param In OraDatabase.Parameters
         OraDatabase.Parameters.Remove Param.Name
      Next


      End Sub