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

I have a problem running the below code. When I execute "Part 1" as soon as it hits the first field in the cpw rdoquery I get the **error-"object collection:couldn't find item indicated by text."** The stored procedure "rdoproc" was created under a role "pfp_role" and is valid. I am sure the problem is with how I set the oracle proc up on my end(permissions???). I have a production server with stored procs on oracle 7.3 which run fine under vb 5, but on my development server with the same stored procs the code returns the above error. Could you possibly give an overview as to what steps(1-2-3) need to be taken from the oracle end of things with regards to generating a stored proc that can be called from vb 5. Grants, Privliges etc. I appreciate any help.

 article <37079909.235902479_at_newshost.us.oracle.com>,   msftmail_at_us.oracle.com (support email) wrote:
> 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
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Apr 02 1999 - 00:03:08 CST