Re: HOW TO: ADO 2.5 Oracle Stored Proc into Recordset

In article <SswV5.18786$II2.1855235_at_newsread2.prod.itd.earthlink.net>,   "Hope Reyes" <hopealso_at_my-deja.com> wrote:
> Hi and thanks in advance...
>
> Can somone give me syntax, and/or advice, and or references, that
 will allow
> me to run an Oracle stored procedure from VB ADO 2.5 that will
 provide input
> (criteria parameters) and return a resultant recordset on a DQL select
> statement inside the stored proc? Can one use a simple command
 object and
> parameters collection or not? I have a book (Visual Basic Oracle 8,
 wrox)
> that says in older ADO this would not work and one needed a pass-
 through.
> But we are trying to use OLE DB instead of ODBC.
>
> I am interested in using Oracle stored procedures so that I
 may "reuse"
> query syntax and simply provide parameters to what would ordinarily
 be a
> simple SQL SELECT statement that executes joins and includes WHERE
 criteria.
> I am trying to get away from the method of writing all SQL for Access
 2000
> forms, etc. in long VB strings. These are not process oriented or
 business
> rules stored proc's, but simply a way of mimicking what is possible
 with
> straightforward Access "parameter queries".
>
> If you have tips on using such a stored proc as the record source for
 an
> Access form, report, or combo/list box please include that too! I
 don't
> know if the best method will be trying to set the form's recordset
 property,
> etc... still investigating.

Assuming that your stored procedure(s) return as an OUT parameter a REF CURSOR (the result of your query), you should be able to simply call the stored procedure like you would any other stored procedure, i.e.

{call myStoredProc( arg1, arg2, arg3 )}

except that you omit the REF CURSOR argument. The driver (at least the Oracle OLE DB provider) will automatically return the REF CURSOR as the result set.

Justin Cave

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 30 2000 - 17:14:04 CST