Re: Problem running Oracle package from VB

A colleague of mine recently tried the same thing. He was able to call stored procedures as long as PL/SQL tables are not being passed as parameters. I am unaware of any non-Oracle tool that works in this situation. Our solution was to write a ProC program that called the SP and mapped the PL/SQL table onto an array of VARCHARs (or whatever
you need). The VB calls the ProC and gets the array that way.

Hope that helps - be interested if you find a better way.

Mike
Chris Hallgren <chris_at_hallgren.org> wrote in message news:sqotadr4c5d145_at_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 Sat Sep 02 2000 - 05:24:44 CDT