Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Does Oracle OLEDB Provider for Oracle support named variables?
Tzanko schrieb:
> I do not think this works.
> While the syntax is accepted, I still need to put my parameters in
> order. It means I am still using positional parameters - the parameter
> names are ignored.
But I use them all the time!
> I put the question to Oracle and they confirmed their OleDB driver
> supports only positional parameters. Pity it is, but that should settle
> it.
Please look up the OO4O stuff in the documentation and try out the examples there.
Here is one:
'Declare variables
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
'Create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'Create the OraDatabase Object.
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
'Add EMPNO as an Input/Output parameter and set its initial value. OraDatabase.Parameters.Add "EMPNO", 7369, ORAPARM_INPUT OraDatabase.Parameters("EMPNO").ServerType = ORATYPE_NUMBER
'Add ENAME as an Output parameter and set its initial value. OraDatabase.Parameters.Add "ENAME", 0, ORAPARM_OUTPUT OraDatabase.Parameters("ENAME").ServerType = ORATYPE_VARCHAR2
'Add SAL as an Output parameter and set its initial value. OraDatabase.Parameters.Add "SAL", 0, ORAPARM_OUTPUT OraDatabase.Parameters("SAL").ServerType = ORATYPE_NUMBER
'Execute the Stored Procedure Employee.GetEmpName to retrieve ENAME. ' This Stored Procedure can be found in the file ORAEXAMP.SQL. OraDatabase.ExecuteSQL ("Begin Employee.GetEmpName (:EMPNO, :ENAME); end;") 'Display the employee number and name.
'Execute the Stored Function Employee.GetSal to retrieve SAL.
' This Stored Function can be found in the file ORAEXAMP.SQL. OraDatabase.ExecuteSQL ("declare SAL number(7,2); Begin :SAL:=Employee.GetEmpSal (:EMPNO); end;")
'Display the employee name, number and salary. MsgBox "Employee " & OraDatabase.Parameters("ENAME").value & ", #" & OraDatabase.Parameters("EMPNO").value & ",Salary=" & OraDatabase.Parameters("SAL").value
'Remove the Parameters. OraDatabase.Parameters.Remove "EMPNO" OraDatabase.Parameters.Remove "ENAME"
OraDatabase.Parameters.Remove "SAL" Received on Fri Apr 07 2006 - 11:43:47 CDT