How to Pass a VB Array to an Oracle PL/SQL variable???

From: Roy Acres <roy.acres_at_videotron.ca>
Date: 2000/04/22
Message-ID: <3903f991.1331993@news.videotron.ca>#1/1

Sorry... I should have included the specific error message I'm getting, and some sample code:

The error is # 3421 from the Parameters Collection of the Command object. It states, "Application uses a value of the wrong type for the current operation."

Here's some of the Command/Parameters code:

With cmmInsertNotification

    .ActiveConnection = CNSConnection.adoConnection
    .CommandType = adCmdStoredProc
    .CommandText = "pkg_CNS_Database.Insert_CNF_Data"
    .Parameters.Append .CreateParameter("v_cnf_id", adNumeric,

adParamOutput, 6)

    .Parameters.Append .CreateParameter("v_cosmetic_inact_flag", adChar, adParamInput, 1)

    .Parameters.Append .CreateParameter("v_other_manfctr_id", adNumeric, adParamInput, 6)

    .Parameters.Append .CreateParameter("v_distributor_id", adNumeric, adParamInput, 6)
End With

cmmInsertNotification(0) = intCNF_ID  'Returned from database
cmmInsertNotification(1) = strCosmeticInactFlag
cmmInsertNotification(17) = arrOtherManufId
cmmInsertNotification(18) = arrDistributorId

cmmInsertNotification.Execute

I need to pass the arrOtherManufId and arrDIstributorId to the last two Oracle DB stored procedure parameters listed... any thoughts???

===> Roy
roy.acres_at_videotron.ca

roy.acres_at_videotron.ca (Roy Acres) wrote:

>Hi!
>
>I'm in the finishing stages of an application. It has gone very well,
>and now I am working on the code for passing values to the database.
>I am using VB 6.0 Enterprise Edition and talking to Oracle Server 7.34
>using the MDAC/ADO 2.5 Microsoft Provider for Oracle.
>
>For a record the user might save (ie. Insert) into the database, most
>of the fields, of course, contain only a single value. However, there
>are cases where the user is allowed by reason of necessity to have
>multiple values for a field; in couple of cases, in fact, these are
>listed in a grid even.
>
>I have all the related values (for example: manufacturer id) in a
>single-dimension array of type Double. I had errors when using
>Integer data type. A reference I have suggests that Decimal is most
>compatible with Oracle's Number data type, but I can't seem to set a
>variable or array to type Decimal, and I can't convert the array with
>CDec(). I have found a reference using Double as the data type
>against the Number type field in Oracle, so I think I'm OK on this.
>
>My problem is I continue to get errors when trying to pass the arrays
>as ADO command parameters to Oracle during an insert! Has anybody
>encountered this or have any idea what I should be doing? Am I
>allowed to pass arrays to Oracle via ADO? Are there any special
>conventions I should be using?
>
>Any advice or info is really appreciated! Thanks!
>
>
>===> Roy
>roy.acres_at_videotron.ca
Received on Sat Apr 22 2000 - 00:00:00 CDT