2 Replies Latest reply: Apr 15, 2013 7:07 AM by 1002762 RSS

    Return String from Oracle stored proc using Excel 2003 VBA

    1002762
      Hi to everyone,

      I've got a problem that remains unsolved for many days. I am trying to return a value of an oracle stored procedure using Excel VBA. It might seem trivial, however it's not for someone, who has never done it before...

      OS: Win XP SP3
      Excel 2003
      Ora Client: 11g

      By trying different things I have noticed, that I could have troubles with the ODBC-connection. Maybe I am not using the right one. To store data returned from select statements I have an ODBC-Connection (Driver: Oracle in XE), which works perfectly, e.g.:

      '-----------------------
      Sub Extract_Data()

      Dim cn As ADODB.Connection
      Dim rs As ADODB.Recordset
      Dim db_name, UserName, Password As String

      cn.Open db_name, USerName, Password

      sql_statement = "SELECT some_text FROM some_table"

      Set rs = cn.Execute(sql_statement)
      rs.MoveFirst 'jump to the first entry in the data list

      Row = 2
      While Not rs.EOF
      'save the data to a worksheet
      ip.Cells(Row, 2) = rs(0).Value
      Row = Row + 1
      rs.MoveNext
      Wend
      End Sub
      '-----------------------

      Now I need to execute a stored procedure to return a semi-colon delimited string.
      I have tried the following:

      '----------------------
      Public Sub obj_class()

      Dim cn As New ADODB.Connection
      Dim strSQL, cn As String
      Dim adoCMD As ADODB.Command
      Dim adoRS As ADODB.Recordset

      Set wb = Excel.ActiveWorkbook
      Set ih = wb.Sheets("InfoSheet")

      cn.Open db_name, UserName, Password

      Set adoCMD = New ADODB.Command
      With adoCMD
      .ActiveConnection = cn
      .CommandText = "S#mdb$stg_da_extr_util.get_all_classes_of_classif"
      .CommandType = adCmdStoredProc
      .Parameters.Refresh
      '------ and here comes the error saying:
      '------ could not find the object in the collection corresponding to the name or ordinal reference requested by the application
      .Parameters("i_caller").Value = "'STG_DATA_REQUEST'"
      .Parameters("i_obj_classif_id").Value = 120

      Set adoRS = .Execute()
      End With
      End Sub
      '----------------------

      I did asked on the forum:
      http://www.access-programmers.co.uk/forums/showthread.php?p=1241667#post1241667

      but unfortunately without success.

      Could it be, that my ODBC-connection is wrong? When debugging the connection string, I find the Provider=MSDASQL5.1.

      I have moderate knowledge in VBA, but absolutely a newbie with Oracle DB. Is there any source of information that can help solving this issue? Looking forward to hearing from you, as I am almost giving up... ;(