Calling Stored Procedures of Oracle PL/SQL

[Narayan]

Hi all,

I have a problem when calling the Stored Procedure of Oracle. I have a Oracle
Stored Procedure which accepts an Integer Array as an argument. Can anyone
let me know if there is a way to call this Stored Procedure from Visual Basic
by passing an integer array. If there is a way pl. let me know the method.
I tried passing a Variant Array by Creating a Parameter and then Appending
it to the Parameters Collection in ADO.

The problem I face is the Paramater object of ADO expects the Parameter Type
which will be (adArray OR adInteger) (it is an ORed value) that represents
an Integer Array. This is what MSDN says. This gives me invalid Type error.
I also tried passing a variant array. Same error. But in either of the case
I could not pass the value for the Value data member of the ADO's Parameter
Object bcos it accepts the value but not the array address whcih is what
is required to be passed for the stored procedure.

i.e
Dim x(2) as Variant
Dim y(2) as Integer
y(0) = 1
y(1) = 2
x(0)=1
x(1) = 2

' Case 1

CreateParameter("ARG1", adInteger OR adArray, adParameterInput, x)

' Case 2

CreateParameter("ARG1", adVariant OR adArray, adParameterInput, y)

both of the above cases results in an error when I try to set a value for
the Value data member of the Parameter Object which here is the 4 argument
of the CreateParameter method.

Any ideas or suggesstions are welcome. Its very urgent.


Regards

Narayan

[marc]

Have you tried

CreateParameter("ARG1", adInteger OR adArray, adParameterInput, VarPtr(x))

Marc

"Narayan" <narayankv@yahoo.com> wrote:
>
>Hi all,
>
>I have a problem when calling the Stored Procedure of Oracle. I have a Oracle
>Stored Procedure which accepts an Integer Array as an argument. Can anyone
>let me know if there is a way to call this Stored Procedure from Visual
Basic
>by passing an integer array. If there is a way pl. let me know the method.
>I tried passing a Variant Array by Creating a Parameter and then Appending
>it to the Parameters Collection in ADO.
>
>The problem I face is the Paramater object of ADO expects the Parameter
Type
>which will be (adArray OR adInteger) (it is an ORed value) that represents
>an Integer Array. This is what MSDN says. This gives me invalid Type error.
>I also tried passing a variant array. Same error. But in either of the case
>I could not pass the value for the Value data member of the ADO's Parameter
>Object bcos it accepts the value but not the array address whcih is what
>is required to be passed for the stored procedure.
>
>i.e
>Dim x(2) as Variant
>Dim y(2) as Integer
>y(0) = 1
>y(1) = 2
>x(0)=1
>x(1) = 2
>
>' Case 1
>
>CreateParameter("ARG1", adInteger OR adArray, adParameterInput, x)
>
>' Case 2
>
>CreateParameter("ARG1", adVariant OR adArray, adParameterInput, y)
>
>both of the above cases results in an error when I try to set a value for
>the Value data member of the Parameter Object which here is the 4 argument
>of the CreateParameter method.
>
>Any ideas or suggesstions are welcome. Its very urgent.
>
>
>Regards
>
>Narayan
>
>

[Narayan]

Hi marc,

Thanx for the immediate response. Did you by any chance meant that I have
to pass the first element of the arrayor is there any function called VarPtr
in VB that takes the array name as the argument ? Pl. let me know.
Expecting your response.

Thanx a ton

Narayan


"marc" <whale@ultranet.com> wrote:
>
>Have you tried
>
>CreateParameter("ARG1", adInteger OR adArray, adParameterInput, VarPtr(x))
>
>Marc
>
>"Narayan" <narayankv@yahoo.com> wrote:
>>
>>Hi all,
>>
>>I have a problem when calling the Stored Procedure of Oracle. I have a
Oracle
>>Stored Procedure which accepts an Integer Array as an argument. Can anyone
>>let me know if there is a way to call this Stored Procedure from Visual
>Basic
>>by passing an integer array. If there is a way pl. let me know the method.
>>I tried passing a Variant Array by Creating a Parameter and then Appending
>>it to the Parameters Collection in ADO.
>>
>>The problem I face is the Paramater object of ADO expects the Parameter
>Type
>>which will be (adArray OR adInteger) (it is an ORed value) that represents
>>an Integer Array. This is what MSDN says. This gives me invalid Type error.
>>I also tried passing a variant array. Same error. But in either of the
case
>>I could not pass the value for the Value data member of the ADO's Parameter
>>Object bcos it accepts the value but not the array address whcih is what
>>is required to be passed for the stored procedure.
>>
>>i.e
>>Dim x(2) as Variant
>>Dim y(2) as Integer
>>y(0) = 1
>>y(1) = 2
>>x(0)=1
>>x(1) = 2
>>
>>' Case 1
>>
>>CreateParameter("ARG1", adInteger OR adArray, adParameterInput, x)
>>
>>' Case 2
>>
>>CreateParameter("ARG1", adVariant OR adArray, adParameterInput, y)
>>
>>both of the above cases results in an error when I try to set a value for
>>the Value data member of the Parameter Object which here is the 4 argument
>>of the CreateParameter method.
>>
>>Any ideas or suggesstions are welcome. Its very urgent.
>>
>>
>>Regards
>>
>>Narayan
>>
>>
>

[marc]

VarPtr is a function in VB. The function returns the variable pointer (address)
or in this case a pointer to the first element of the array. This should
be the address the PL/SQL script is looking for. Pass the varible as an
adinteger. If you MSDN library the function is describe there (the book
that it references is very good, I have a copy).

Marc

"Narayan" <narayankv@yahoo.com> wrote:
>
>Hi marc,
>
>Thanx for the immediate response. Did you by any chance meant that I have
>to pass the first element of the arrayor is there any function called VarPtr
>in VB that takes the array name as the argument ? Pl. let me know.
>Expecting your response.
>
>Thanx a ton
>
>Narayan
>
>
>"marc" <whale@ultranet.com> wrote:
>>
>>Have you tried
>>
>>CreateParameter("ARG1", adInteger OR adArray, adParameterInput, VarPtr(x))
>>
>>Marc
>>
>>"Narayan" <narayankv@yahoo.com> wrote:
>>>
>>>Hi all,
>>>
>>>I have a problem when calling the Stored Procedure of Oracle. I have a
>Oracle
>>>Stored Procedure which accepts an Integer Array as an argument. Can anyone
>>>let me know if there is a way to call this Stored Procedure from Visual
>>Basic
>>>by passing an integer array. If there is a way pl. let me know the method.
>>>I tried passing a Variant Array by Creating a Parameter and then Appending
>>>it to the Parameters Collection in ADO.
>>>
>>>The problem I face is the Paramater object of ADO expects the Parameter
>>Type
>>>which will be (adArray OR adInteger) (it is an ORed value) that represents
>>>an Integer Array. This is what MSDN says. This gives me invalid Type error.
>>>I also tried passing a variant array. Same error. But in either of the
>case
>>>I could not pass the value for the Value data member of the ADO's Parameter
>>>Object bcos it accepts the value but not the array address whcih is what
>>>is required to be passed for the stored procedure.
>>>
>>>i.e
>>>Dim x(2) as Variant
>>>Dim y(2) as Integer
>>>y(0) = 1
>>>y(1) = 2
>>>x(0)=1
>>>x(1) = 2
>>>
>>>' Case 1
>>>
>>>CreateParameter("ARG1", adInteger OR adArray, adParameterInput, x)
>>>
>>>' Case 2
>>>
>>>CreateParameter("ARG1", adVariant OR adArray, adParameterInput, y)
>>>
>>>both of the above cases results in an error when I try to set a value
for
>>>the Value data member of the Parameter Object which here is the 4 argument
>>>of the CreateParameter method.
>>>
>>>Any ideas or suggesstions are welcome. Its very urgent.
>>>
>>>
>>>Regards
>>>
>>>Narayan
>>>
>>>
>>
>

[marc]

Sorry, I left out VarPtr(x(0)) not VarPtr(x). Also i would declare the variables
as long in the VB program. That way the variables will be 4 bytes long in
VB as is oracle's int type. VB integer type is 2 bytes long as is smallint
in oracle.

Marc

"marc" <whale@ultranet.com> wrote:
>
>VarPtr is a function in VB. The function returns the variable pointer (address)
>or in this case a pointer to the first element of the array. This should
>be the address the PL/SQL script is looking for. Pass the varible as an
>adinteger. If you MSDN library the function is describe there (the book
>that it references is very good, I have a copy).
>
>Marc
>
>"Narayan" <narayankv@yahoo.com> wrote:
>>
>>Hi marc,
>>
>>Thanx for the immediate response. Did you by any chance meant that I have
>>to pass the first element of the arrayor is there any function called VarPtr
>>in VB that takes the array name as the argument ? Pl. let me know.
>>Expecting your response.
>>
>>Thanx a ton
>>
>>Narayan
>>
>>
>>"marc" <whale@ultranet.com> wrote:
>>>
>>>Have you tried
>>>
>>>CreateParameter("ARG1", adInteger OR adArray, adParameterInput, VarPtr(x))
>>>
>>>Marc
>>>
>>>"Narayan" <narayankv@yahoo.com> wrote:
>>>>
>>>>Hi all,
>>>>
>>>>I have a problem when calling the Stored Procedure of Oracle. I have
a
>>Oracle
>>>>Stored Procedure which accepts an Integer Array as an argument. Can anyone
>>>>let me know if there is a way to call this Stored Procedure from Visual
>>>Basic
>>>>by passing an integer array. If there is a way pl. let me know the method.
>>>>I tried passing a Variant Array by Creating a Parameter and then Appending
>>>>it to the Parameters Collection in ADO.
>>>>
>>>>The problem I face is the Paramater object of ADO expects the Parameter
>>>Type
>>>>which will be (adArray OR adInteger) (it is an ORed value) that represents
>>>>an Integer Array. This is what MSDN says. This gives me invalid Type
error.
>>>>I also tried passing a variant array. Same error. But in either of the
>>case
>>>>I could not pass the value for the Value data member of the ADO's Parameter
>>>>Object bcos it accepts the value but not the array address whcih is what
>>>>is required to be passed for the stored procedure.
>>>>
>>>>i.e
>>>>Dim x(2) as Variant
>>>>Dim y(2) as Integer
>>>>y(0) = 1
>>>>y(1) = 2
>>>>x(0)=1
>>>>x(1) = 2
>>>>
>>>>' Case 1
>>>>
>>>>CreateParameter("ARG1", adInteger OR adArray, adParameterInput, x)
>>>>
>>>>' Case 2
>>>>
>>>>CreateParameter("ARG1", adVariant OR adArray, adParameterInput, y)
>>>>
>>>>both of the above cases results in an error when I try to set a value
>for
>>>>the Value data member of the Parameter Object which here is the 4 argument
>>>>of the CreateParameter method.
>>>>
>>>>Any ideas or suggesstions are welcome. Its very urgent.
>>>>
>>>>
>>>>Regards
>>>>
>>>>Narayan
>>>>
>>>>
>>>
>>
>

[Narayan]

Hey Marc,

Actually VB does not suppost passing integer arrays or generally arrays that
matters to Oracle Stored Procedures it seems. If you have situation that
requires and array to be sent to oracle Pass an Visual Basic Comma de-limitted
String as a Variant to the Oracle Procedure. The Stored Proc. has to be written
in such a way thatit acceps this string and parse it to get all the reuired
paramaters that you wanted to pass iif it would have been an array and then
do any required operation with those values that were parsed from the string.
This is the work around that was given for the problem that I was facing.
Anyway I learnt a new function in VB and also other related Functions to
that through MSDN online. Pl. note that youalso have functions like VarPtrArray(),
VarPtr(), StrPtr() (For a location of a String Array), VarPtrStringArray()
for String Array and objPtr for get the address of an object. The VarPtr()
function returns the address of only a location of an array. If you need
the address of an array you need to call the VarPtrArray() function with
the name of the array as an argument like the following :-

Dim x(10) as Integer
Dim xAddr as Long
x = VarPtrArray(x())

xAddr = VarPtr(x(0))

The VarPtrArray function has to be declared in a module bcos its a DLL function
(external procedure). The decl. goes like this :-

Declare Function VarPtrArray Lib "msvbvm50.dll" Alias "VarPtr" _
(Var() as Any) As Long

I assumed that you might not be knowing these functions and hence thought
it woulf be useful. If you already knew these then I am wrong. Pl. excuse
me for this long mail.


Regards

Narayan



"marc" <whale@ultranet.com> wrote:
>
>VarPtr is a function in VB. The function returns the variable pointer (address)
>or in this case a pointer to the first element of the array. This should
>be the address the PL/SQL script is looking for. Pass the varible as an
>adinteger. If you MSDN library the function is describe there (the book
>that it references is very good, I have a copy).
>
>Marc
>
>"Narayan" <narayankv@yahoo.com> wrote:
>>
>>Hi marc,
>>
>>Thanx for the immediate response. Did you by any chance meant that I have
>>to pass the first element of the arrayor is there any function called VarPtr
>>in VB that takes the array name as the argument ? Pl. let me know.
>>Expecting your response.
>>
>>Thanx a ton
>>
>>Narayan
>>
>>
>>"marc" <whale@ultranet.com> wrote:
>>>
>>>Have you tried
>>>
>>>CreateParameter("ARG1", adInteger OR adArray, adParameterInput, VarPtr(x))
>>>
>>>Marc
>>>
>>>"Narayan" <narayankv@yahoo.com> wrote:
>>>>
>>>>Hi all,
>>>>
>>>>I have a problem when calling the Stored Procedure of Oracle. I have
a
>>Oracle
>>>>Stored Procedure which accepts an Integer Array as an argument. Can anyone
>>>>let me know if there is a way to call this Stored Procedure from Visual
>>>Basic
>>>>by passing an integer array. If there is a way pl. let me know the method.
>>>>I tried passing a Variant Array by Creating a Parameter and then Appending
>>>>it to the Parameters Collection in ADO.
>>>>
>>>>The problem I face is the Paramater object of ADO expects the Parameter
>>>Type
>>>>which will be (adArray OR adInteger) (it is an ORed value) that represents
>>>>an Integer Array. This is what MSDN says. This gives me invalid Type
error.
>>>>I also tried passing a variant array. Same error. But in either of the
>>case
>>>>I could not pass the value for the Value data member of the ADO's Parameter
>>>>Object bcos it accepts the value but not the array address whcih is what
>>>>is required to be passed for the stored procedure.
>>>>
>>>>i.e
>>>>Dim x(2) as Variant
>>>>Dim y(2) as Integer
>>>>y(0) = 1
>>>>y(1) = 2
>>>>x(0)=1
>>>>x(1) = 2
>>>>
>>>>' Case 1
>>>>
>>>>CreateParameter("ARG1", adInteger OR adArray, adParameterInput, x)
>>>>
>>>>' Case 2
>>>>
>>>>CreateParameter("ARG1", adVariant OR adArray, adParameterInput, y)
>>>>
>>>>both of the above cases results in an error when I try to set a value
>for
>>>>the Value data member of the Parameter Object which here is the 4 argument
>>>>of the CreateParameter method.
>>>>
>>>>Any ideas or suggesstions are welcome. Its very urgent.
>>>>
>>>>
>>>>Regards
>>>>
>>>>Narayan
>>>>
>>>>
>>>
>>
>

[marc]

I was not aware of that as I have never tried it. Thought it might pass the
address and have oracle get the array. I did think that an integer array
would not work well as the number data type (not smallint or integer to many
data types out there) is not the same length in bytes. Oracle I would think
would then be looking for numbers in different byte widths. Did you try
to get an array of the same byte width as the oracle datatype?

Marc

"Narayan" <narayankv@yahoo.com> wrote:
>
>Hey Marc,
>
>Actually VB does not suppost passing integer arrays or generally arrays
that
>matters to Oracle Stored Procedures it seems. If you have situation that
>requires and array to be sent to oracle Pass an Visual Basic Comma de-limitted
>String as a Variant to the Oracle Procedure. The Stored Proc. has to be
written
>in such a way thatit acceps this string and parse it to get all the reuired
>paramaters that you wanted to pass iif it would have been an array and then
>do any required operation with those values that were parsed from the string.
>This is the work around that was given for the problem that I was facing.
>Anyway I learnt a new function in VB and also other related Functions to
>that through MSDN online. Pl. note that youalso have functions like VarPtrArray(),
>VarPtr(), StrPtr() (For a location of a String Array), VarPtrStringArray()
>for String Array and objPtr for get the address of an object. The VarPtr()
>function returns the address of only a location of an array. If you need
>the address of an array you need to call the VarPtrArray() function with
>the name of the array as an argument like the following :-
>
>Dim x(10) as Integer
>Dim xAddr as Long
>x = VarPtrArray(x())
>
>xAddr = VarPtr(x(0))
>
>The VarPtrArray function has to be declared in a module bcos its a DLL function
>(external procedure). The decl. goes like this :-
>
>Declare Function VarPtrArray Lib "msvbvm50.dll" Alias "VarPtr" _
>(Var() as Any) As Long
>
>I assumed that you might not be knowing these functions and hence thought
>it woulf be useful. If you already knew these then I am wrong. Pl. excuse
>me for this long mail.
>
>
>Regards
>
>Narayan
>
>
>
>"marc" <whale@ultranet.com> wrote:
>>
>>VarPtr is a function in VB. The function returns the variable pointer
(address)
>>or in this case a pointer to the first element of the array. This should
>>be the address the PL/SQL script is looking for. Pass the varible as an
>>adinteger. If you MSDN library the function is describe there (the book
>>that it references is very good, I have a copy).
>>
>>Marc
>>
>>"Narayan" <narayankv@yahoo.com> wrote:
>>>
>>>Hi marc,
>>>
>>>Thanx for the immediate response. Did you by any chance meant that I have
>>>to pass the first element of the arrayor is there any function called
VarPtr
>>>in VB that takes the array name as the argument ? Pl. let me know.
>>>Expecting your response.
>>>
>>>Thanx a ton
>>>
>>>Narayan
>>>
>>>
>>>"marc" <whale@ultranet.com> wrote:
>>>>
>>>>Have you tried
>>>>
>>>>CreateParameter("ARG1", adInteger OR adArray, adParameterInput, VarPtr(x))
>>>>
>>>>Marc
>>>>
>>>>"Narayan" <narayankv@yahoo.com> wrote:
>>>>>
>>>>>Hi all,
>>>>>
>>>>>I have a problem when calling the Stored Procedure of Oracle. I have
>a
>>>Oracle
>>>>>Stored Procedure which accepts an Integer Array as an argument. Can
anyone
>>>>>let me know if there is a way to call this Stored Procedure from Visual
>>>>Basic
>>>>>by passing an integer array. If there is a way pl. let me know the method.
>>>>>I tried passing a Variant Array by Creating a Parameter and then Appending
>>>>>it to the Parameters Collection in ADO.
>>>>>
>>>>>The problem I face is the Paramater object of ADO expects the Parameter
>>>>Type
>>>>>which will be (adArray OR adInteger) (it is an ORed value) that represents
>>>>>an Integer Array. This is what MSDN says. This gives me invalid Type
>error.
>>>>>I also tried passing a variant array. Same error. But in either of the
>>>case
>>>>>I could not pass the value for the Value data member of the ADO's Parameter
>>>>>Object bcos it accepts the value but not the array address whcih is
what
>>>>>is required to be passed for the stored procedure.
>>>>>
>>>>>i.e
>>>>>Dim x(2) as Variant
>>>>>Dim y(2) as Integer
>>>>>y(0) = 1
>>>>>y(1) = 2
>>>>>x(0)=1
>>>>>x(1) = 2
>>>>>
>>>>>' Case 1
>>>>>
>>>>>CreateParameter("ARG1", adInteger OR adArray, adParameterInput, x)
>>>>>
>>>>>' Case 2
>>>>>
>>>>>CreateParameter("ARG1", adVariant OR adArray, adParameterInput, y)
>>>>>
>>>>>both of the above cases results in an error when I try to set a value
>>for
>>>>>the Value data member of the Parameter Object which here is the 4 argument
>>>>>of the CreateParameter method.
>>>>>
>>>>>Any ideas or suggesstions are welcome. Its very urgent.
>>>>>
>>>>>
>>>>>Regards
>>>>>
>>>>>Narayan
>>>>>
>>>>>
>>>>
>>>
>>
>