Oracle stored procedures/functions using ADO 
Author Message
 Oracle stored procedures/functions using ADO

I know there have been many postings about this, but I tried the solutions
suggested and they didn't work. Lastly I found the following article in
MSDN: Q255043 - HOWTO: Retrieve ADO Recordset from Oracle (REF CURSOR)
Through ASP. Tried that and I keep getting: "Run-time error '-2147217900
(80040e14)': Syntax error in {call...} ODBC Escape."
I am running my test application on Windows 2000. I appreciate any help.


Here is the code I am using:

Oracle code:

create or replace package experimentincursors
    cursor c1 is select constituent.firstname || ' ' || constituent.lastname
as ConstituentName from constituent;
    type cursorType is ref cursor return c1%ROWTYPE;
    Procedure spListEmp(theCursor in out cursorType);

create or replace package body experimentincursors as
procedure spListEmp(theCursor in out cursorType) is
    v_cursor cursorType;
    OPEN v_cursor FOR
        SELECT constituent.firstname || ' ' || constituent.lastname as
ConstituentName from constituent;
    theCursor := v_cursor;
END spListEmp;
END experimentincursors;

VB Code:

Private Sub Command1_Click()
    Dim SOME_DA As SOME_DataAccess
    Dim RS As ADODB.Recordset

    Set SOME_DA = New SOME_DataAccess
    Set RS = SOME_DA.RunSPReturnRS("{call
experimentincursors.spListEmp({resultset 0, theCursor})}")
End Sub

Function RunSPReturnRS(ByVal strSP As String) As ADODB.Recordset
    On Error GoTo ErrorHandler

    ' Create the ADO objects
    Dim cmd As ADODB.Command

    ' GetContext.CreateObject does not work with Oracle driver

    'Set cmd = CtxCreateObject("ADODB.Command")

    Set cmd = New ADODB.Command

    ' Init the ADO objects & the stored proc parameters
    cmd.ActiveConnection = Conn
    cmd.CommandText = strSP
    cmd.CommandType = adCmdStoredProc
    Set RunSPReturnRS = cmd.Execute

    ' Cleanup and return
    Set cmd = Nothing
    Exit Function

    Set cmd = Nothing
    RaiseError g_modName, "RunSPReturnRS(" & strSP & ", ...)"
End Function

Public Function GetConnectionString() As String
    GetConnectionString = "Provider=MSDAORA.1;Data Source=PC67ORACLE;User
End Function

Mon, 04 Aug 2003 22:50:51 GMT  
 [ 1 post ] 

 Relevant Pages 

1. ADO executing oracle stored procedure/function

2. Using oracle stored procedures/functions to return result sets

3. Oracle stored procedure and input arrays using ADO and VB6

4. Error calling Oracle store procedures using ADO

5. Recordsets Using ADO and Oracle Stored Procedures

6. oracle's stored procedure from vb using ADO

7. executing a stored procedure in oracle using ADO

8. ADO stored procedures from VC++ using ORACLE

9. Unable to get Output data using VB, Ado 2.1 and Oracle Stored procedure

10. HELP ==>Oracle Stored Procedure using ADO(VB)

11. HELP ==>Oracle Stored Procedure(PACKAGE) using ADO(VB)

12. ado recordsets from stored procedures, stored procedures have input parameters


Powered by phpBB® Forum Software