Re: RDO for Oracle stored functions

On Fri, 02 Jan 1998 11:14:17 -0500, Sameer Utrankar <sameeru_at_exchnage.ml.com> wrote:

following code worked for me with Oracle driver, (did not work with Intersolv driver however):

Private Sub Command1_Click()
' NOTE: Oracle 7 cannot return a result set from a stored procedure.

' create procedure test (c1 in out number)
' as
' begin
' c1 := c1 + 15;
' end;

Dim RdoEnvironment As RdoEnvironment
Dim RdoDatabase As rdoConnection
Dim RdoRecordset As rdoResultset
Dim RdoQrydef As RdoQuery

Set RdoEnvironment = rdoEnvironments(0)
Set RdoDatabase = RdoEnvironment.OpenConnection("", rdDriverNoPrompt, False, _
  "DSN=ORA32;UID=SCOTT;PWD=TIGER;") Set RdoQrydef = RdoDatabase.CreateQuery("q1", "{call test( ? )}")

RdoQrydef(0).Direction = rdParamInputOutput
RdoQrydef(0).Type = rdTypeNUMERIC
RdoQrydef(0).Value = 10

Set RdoRecordset = RdoQrydef.OpenResultset(rdOpenForwardOnly, rdConcurReadOnly)

MsgBox RdoQrydef(0)

End Sub

-mark

>40041 - object collection - couldn't find item indicated by text
>
>I get this error with trying to use following code to run oracle stored
>function with RDO through VB5. The error occurs when code is run by a
>user for oracle connection other than function owner. If function owner
>runs the code then it runs fine.
>
>Does anyone know if :
>#1. RDO has problems in running Oracle stored procs/funcs which are
>owned by other user (ie running user qualified syntax such as
>user.object)
>
>#2. I tried to create a public synonym for the object and run it and
>same thing happens. Does RDO understand Oracle public synonym objects ?
>
>If user has a local synonym for the object then it works so that's the
>work-around but not good because too many local synonyms will have to be
>managed for database with lots and lots of users.
>
>Here is the code :
>
>sSql = "{? = call carat.get_emp_segment(?)}"
>
>Set qry = cn.CreateQuery("test", sSql)
>
>qry.rdoParameters(0).Direction = rdParamOutput 'ERROR OCCURS HERE !!!
>qry.rdoParameters(0).Type = rdTypeVARCHAR
>
>qry.rdoParameters(1).Direction = rdParamInput
>qry.rdoParameters(1).Type = rdTypeNUMERIC
>qry.rdoParameters(1).Value = 21920
>
>qry.Execute
Received on Wed Jan 14 1998 - 00:00:00 CET