2 Replies Latest reply: Mar 2, 2010 10:55 PM by 528054 RSS

    Help Pls. Need to pass in cell value (excel) into stored oracle procedure


      I'm pretty new to VB script and teaching myself oo4o.

      I found an example on how to call a Oracle Database Package/Procedure from within Excel (Macro).
      This example, however, uses a hard-coded value of 10 as input parameter.

      Could someone please tell me what the syntax would be if I need to replace that 10 with a value entered into a cell? Cell A1 for example?
      So instead of using the 10, a value entered by the user is used instead.

      This is the current example code:

      Set OO4OSession = CreateObject("OracleInProcServer.XOraSession")
      Set empDb = OO4OSession.OpenDatabase("XE", "scott/tiger", 0)

      empDb.Parameters.Add "DEPTNO", 10, ORAPARM_INPUT

      empDb.Parameters("DEPTNO").serverType = ORATYPE_NUMBER
      empDb.Parameters.Add "DNAME", 0, ORAPARM_OUTPUT
      empDb.Parameters("DNAME").serverType = ORATYPE_VARCHAR2
      empDb.Parameters.Add "DLOC", 0, ORAPARM_OUTPUT
      empDb.Parameters("DLOC").serverType = ORATYPE_VARCHAR2
      Set PlSqlStmt = empDb.CreateSql("Begin Department.GetDeptname" & _
      "(:DEPTNO, :DNAME, :DLOC); end;", 0&)
      'Display Department name and location
      MsgBox empDb.Parameters("DNAME").Value & empDb.Parameters("DLOC").Value

      Many thanks,