This discussion is archived
2 Replies Latest reply: Mar 2, 2010 8:55 PM by 528054 RSS

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

528054 Newbie
Currently Being Moderated
Hi,

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,

Brett

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points