Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Does Oracle OLEDB Provider for Oracle support named variables?

Re: Does Oracle OLEDB Provider for Oracle support named variables?

From: Volker Hetzer <volker.hetzer_at_ieee.org>
Date: Fri, 07 Apr 2006 18:43:47 +0200
Message-ID: <e164s3$mm4$1@nntp.fujitsu-siemens.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US