VB- RDO-Oracle

Hi,
I am using VB5.0 with RDO's. I want to know if one can get a set of records( in a resultset ) from a stored procedure from oracle. ( This can be done in SQL server). It is imp to be done through stored procedure? If it can be done t

LVL 1
dishaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
susdashConnect With a Mentor Commented:
I am doing in a different way. I am passing a variant array to the storage procedure as a Byref Parameter. This array contains all the input and output values.Execute the storage procedure with the individual component of the array. Then I transfer the values into a resultset from that array.

 Otherwise make a prepare query for your storage procedure. Pass the the values as set parameters. Then u can get the result as a resultset.
0
 
chris_aCommented:
Forget the RDO, use ADO 2.0 it lets you get recordsets from stored procdures, does the async stuff from RDO and has a native OLEDB driver now
0
 
tomookCommented:
Ditto chris_a, but you can do it in RDO if you have to. If you are doing new development, ADO is not a big step and MS has said RDO is no longer the current method and will become obsolete.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
vinoopaulsCommented:
tomook can you please tell how it is possible in RDO.
ive tried but failed
0
 
dishaAuthor Commented:
I have to do it with RDO....and oracle...and stored proc...how do u do it..could u pls be more explicit...Thanx
0
 
dishaAuthor Commented:
RDO will handle the resultset...but what do u return from u'r PL/SQL stored procedure??

0
 
dishaAuthor Commented:
Hi Susdash.
how do u accept the array in stored procedure??..also
how do u pass array to that proc using RDO..
the second suggestion...does it work with oracle..i think it
does only with SQL.
0
 
vinoopaulsCommented:
I still dont get how one can return a set of records from Oracle
0
 
susdashCommented:
Step1 : Declare an avnArray(0 to No of Cols)  for thet given Storage procedure. Then store all the input parametersin the array. Set all the output parameters to NULL.Remember, the 1st parameter should be from avnArray(1)
       
        Declare mardoqryQueries as rdoQuery

Step 2 : Initialize the storage procedure with the Handle(eg. lngQryH)

Step3: Prepare the parameters as follows

 
    lngLastParameter = UBound(avnArray)
   
    'Following loop sets all the parameters
    For lngIndex = 1 To lngLastParameter
     
      strParamNo = vbCrLf & "Parameter No = " & lngIndex _
      & " ; Value = " & avnArray(lngIndex)
     
      'Set the parameter for the prepared statement
      mardoqryQueries(lngQryH).rdoParameters(lngIndex).Value = orvnFields(lngIndex)
     
    Next lngIndex   'End of for loop
   
  End If            'End of checking whether any parameters exist


Step 4: Execute the SP and get back the array
 

  With mardoqryQueries(lngQryH)
 
    .Execute                                         'Execute the stored procedure
   
   
      If .rdoParameters.Count <> 0 Then
     
        'If stored procedure has returned some value
        For lngIndex = 0 To lngLastParameter
   
          'Get the parameter values
          avnArray(lngIndex) = .rdoParameters(lngIndex).Value
       
        Next lngIndex
       
      End If
     
    End If
   
  End With
 

Step 5: Now you can store the values from the array to your resultset.

0
 
vinoopaulsCommented:
But where are we getting the set of records one set out to get??This will only return the
output parameters. In SQL Server it is possible for the sp to return  a set of records in the
form of a select statement being the last executable statement in it.
Can the same function be done here?
0
 
vinoopaulsCommented:
But where are we getting the set of records one set out to get??This will only return the
output parameters. In SQL Server it is possible for the sp to return  a set of records in the
form of a select statement being the last executable statement in it.
Can the same function be done here?
0
 
dishaAuthor Commented:
I dont think so ..it can be done...because how does PL SQL block
(SP) return the array ...i know RDO can accept it...but how will oracle send it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.