Re: Problem running Oracle package from VB

From: <barx_at_my-deja.com>
Date: Thu, 28 Sep 2000 00:48:57 GMT
Message-ID: <8qu4ho$ulm$1@nnrp1.deja.com>

Hi,

I have achieved this in VB through using REF CURSOR instead of TABLE in my OUT variable. Also I found I had problems using the Append/CreateParameter syntax. I am using the following to call the procedure

    cmd.CommandText = "pkgName.procName"     cmd.CommandType = adCmdStoredProc
    Set rs = cmd.Execute

CREATE OR REPLACE PACKAGE pkgCursor
IS

    TYPE ref_cur IS REF CURSOR;
END; CREATE OR REPLACE PACKAGE STPSELECTTBLCOUNTRY IS
PROCEDURE stpSelecttblCountry(

CountryID       NUMBER ,
RC1     OUT pkgCursor.ref_cur);

END; CREATE OR REPLACE PACKAGE BODY STPSELECTTBLCOUNTRY IS
PROCEDURE stpSelecttblCountry(

CountryID       NUMBER ,
RC1     OUT pkgCursor.ref_cur)

IS
BEGIN

                OPEN RC1 FOR
                SELECT CountryID,  CountryCode,  CountryName,  RegionID
FROM tblCountry
                WHERE CountryID = stpSelecttblCountry.CountryID;

END stpSelecttblCountry;
END stpSelecttblCountry;

Also check that you have the latest ODBC drivers.

Hope this helps you, I was recently looking for this type of info myself and I realise how difficult it is to find...

barx

In article <sqotadr4c5d145_at_corp.supernews.com>,   "Chris Hallgren" <chris_at_hallgren.org> wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Sep 27 2000 - 19:48:57 CDT