Calling Stored Procedures of Oracle PL/SQL


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: Calling Stored Procedures of Oracle PL/SQL

Hybrid View

  1. #1
    Narayan Guest

    Calling Stored Procedures of Oracle PL/SQL


    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



  2. #2
    marc Guest

    Re: Calling Stored Procedures of Oracle PL/SQL


    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
    >
    >



  3. #3
    Narayan Guest

    Re: Calling Stored Procedures of Oracle PL/SQL


    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
    >>
    >>

    >



  4. #4
    marc Guest

    Re: Calling Stored Procedures of Oracle PL/SQL


    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
    >>>
    >>>

    >>

    >



  5. #5
    marc Guest

    Re: Calling Stored Procedures of Oracle PL/SQL


    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
    >>>>
    >>>>
    >>>

    >>

    >



  6. #6
    Narayan Guest

    Re: Calling Stored Procedures of Oracle PL/SQL


    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
    >>>>
    >>>>
    >>>

    >>

    >



  7. #7
    marc Guest

    Re: Calling Stored Procedures of Oracle PL/SQL


    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
    >>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center