Problem running Oracle package from VB

From: Chris Hallgren <chris_at_hallgren.org>
Date: Tue, 29 Aug 2000 22:47:18 -0400
Message-ID: <sqotadr4c5d145@corp.supernews.com>

Hi all,

I have this book that was supposed to show me how to run an Oracle SP from VB. I've created the example Oracle package and VB code from the book, but it's not working (I've learned to really hate this book). When I run the VB code, on the 'Set rs = qy.Execute' statement I get:

Run-time error '-2147217887 (80040e21)':

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work has been done.

Here's the Oracle package:

---

PACKAGE client_package IS
TYPE char_table_type IS TABLE OF VARCHAR(50)

    INDEX BY BINARY_INTEGER; PROCEDURE get_clients (

    p_table_size NUMBER,
    p_clients OUT char_table_type);

END client_package;

---

PACKAGE BODY client_package IS
PROCEDURE get_clients (

    p_table_size NUMBER,
    p_clients OUT char_table_type)
IS

    CURSOR client_cursor IS

        SELECT client_name
        FROM tblclient;

    v_client_name   VARCHAR(50) := '';
    v_table_row     NUMBER(3) :=0;

BEGIN
    OPEN client_cursor;
    LOOP

        FETCH client_cursor INTO v_client_name;
        EXIT WHEN client_cursor%NOTFOUND OR
            v_table_row >= p_table_size;

        v_table_row := v_table_row + 1;
        p_clients (v_table_row) := v_client_name;

        DBMS_OUTPUT.PUT_LINE (v_client_name);

    END LOOP;
    CLOSE client_cursor;
END get_clients;
END;

---

Here's the VB code:

Private Sub Form_Load()

    Dim objConn As New ADODB.Connection
    Dim qy As New ADODB.Command
    Dim rs As ADODB.Recordset
    Dim sConn As String
    Dim sSQL As String

    sSQL = "{call client_package.get_clients (?, {RESULTSET 20, p_clients})}"

    Set objConn = New ADODB.Connection

    sConn = "Data Provider=MSDAORA" & _
            "; Data Source=MRA" & _
            "; User Id=mradvantage" & _
            "; Password=hello"

    objConn.Open sConn

    With qy

        .CommandText = sSQL
        .CommandType = adCmdText
        .ActiveConnection = objConn
        .Parameters.Append .CreateParameter(, adNumeric, adParamInput)

    End With

    qy(0) = 5

    Set rs = New ADODB.Recordset
    Set rs = qy.Execute

    While Not rs.EOF

        MsgBox rs(0)
        rs.MoveNext

    Wend

    rs.Close
    Set rs = Nothing

End Sub

---

I know that many questions have been posted regarding this error message, however not many *answers* are posted. Any help would be greatly appreciated.

Thanks,
chris

---

Chris Hallgren, LLC
chris_hallgren_at_fuse.net
(513) 708-2938

Received on Tue Aug 29 2000 - 21:47:18 CDT