Re: Returning Data from PL/SQL function to ACCES using Oracle ODBC

Here is an RDO example.

Name: ODBC: Using RDO 2 to Call Stored Procedures and Functions

Description: Example Visual Basic Script which uses CreateQuery and Execute rather than an OpenRecordset method to call stored procedures and functions

Private Sub cmdCheck_Click()
'This code demonstrates calling an Oracle Stored Procedure
' using the ODBC {Call...} Syntax
'And calling a stored function using an anonymous pl/sql block
' The pl/sql procedure called is
' Create or Replace Procedure RdoProc(param1 in number,param2 in out
number,
' param3 out number) is
' begin
' param2 := param1+param2;
' param3 := param1;
' end;
'/
' The pl/sql function called is
' Create or Replace Function RdoCheck(param1 in varchar2, param2 in
out varchar2,
' param3 out varchar2) return number is
' begin
' param2 := param1||param2;
' param3 := param1;
' return length(param2);
' end;
' /
'
' NOTE that this does not work with 2.0.3.1.1 but does work with
2.0.3.1.4
' and 2.0.3.1.6 neither does it work with the Microsoft Version
2.0.6325.
'

Dim er As rdoError
On Error GoTo CnEh

  Conn = "uid=SCOTT;pwd=TIGER;" _
    & "DSN=local;"

  Set En = rdoEnvironments(0)
  Set Cn = En.OpenConnection("", rdDriverPrompt, False, Conn)
'
' first call the stored procedure using the ODBC {Call... } syntax
' this is easy as RDO is able to work out the bind types
automatically
'

  QSQL = "{call rdoproc(?,?,?)}"
  Set CPw = Cn.CreateQuery("", QSQL)   

  CPw(0) = 1
  CPw(1) = 2   

  CPw.Execute

  Prompt = "Return Values from RdoProc are: " & CPw(1) & " and " & CPw(2) & "."
  Response = MsgBox(Prompt, , "Stored Procedure Result")   CPw.Close   

'
' Second call the stored function. In this case we can't use the call
syntax
' and so call it via an anonymous block - but to do this you have to
override
' the paramter's direction properties if necessary since they are all
bound
' as rdParamInput
'

  QSQL = "begin ? := RdoCheck(?,?,?); end;"   Set CPw = Cn.CreateQuery("", QSQL)   

  CPw(0).Direction = rdParamOutput
  CPw(1) = "Odd"
  CPw(2) = "Fred"
  CPw(2).Direction = rdParamInputOutput
  CPw(3).Direction = rdParamOutput
  

  CPw.Execute

  Prompt = "Return Values from RdoCheck are: " & CPw(0) & " and " & CPw(2) _

     & " and " & CPw(3) & "."
  Response = MsgBox(Prompt, , "Stored Function Result ")   En.Close
  Exit Sub
CnEh:

    Debug.Print Err, Error
    For Each er In rdoErrors

        Debug.Print er.Description, er.Number     Next er
    Resume Next

End Sub Received on Mon Mar 29 1999 - 09:16:42 CST