5 Replies Latest reply: Mar 4, 2011 10:06 AM by Stephen Redmond RSS

    Oracle Macro Fun!

    Dominic Ayling

      Hi All

      I'm trying to update the value in a stored procedure using a macro, but it's failing to connect to Oracle; my code is:


      sub test()

      Dim connection, connectionString, theCommand, commandString
      connectionString = "DSN=EFIN;Uid=user;Pwd=pword;"
      Set connection = CreateObject("ADODB.Connection")

      Set theCommand = CreateObject("ADODB.Command")

      connection.Open connectionString

      If connection.State = adStateOpen Then
      MsgBox "Welcome to bnhft!"
      MsgBox "Sorry. No bnhft today."
      End If

      const cnstStoredProcedure = 4 'Command type - 4 is for stored procedure
      commandString = "PKG_PARAM.SET_FLAG"
      thecommand.CommandText = commandString
      thecommand.CommandType = cnstStoredProcedure
      thecommand.ActiveConnection = connection
      thecommand.Parameters.Append thecommand.CreateParameter("FLAG",adChar,adParamInput,"1")

      end sub


      I just get the message "Sorry. No bnhft today." indicating that the connection has failed. The DSN works fine in the reload script?

      Any advice welcome!