We help IT Professionals succeed at work.

Returning recordset to VB6 using cmd object and oracle sys_refcursor

4,402 Views
Last Modified: 2013-12-20
I am trying to return a recordset to VB6 from an oracle procedure (in a package using a sys_refcursor -oracle 10g). I am using a connection string based on Provider=MSDAORA.

The procedure has two parameters an IN id and an OUT sys_refcursor. This is the header of the proc
procedure get_usertest1 (in_acct_id in useraccounts.id%type, mycursor out sys_refcursor)
this returns a single record sys_refcursor based on the id in.

e.g. Select * FROM table where ID=in_acct_id;

I would have thought the code to do this should look like this
    szCmd = "DATA_TOOLS.GET_USERTest1"
   
    Set rs = New ADODB.Recordset
    Set cmd = New ADODB.Command
   
    With cmd
       
        .ActiveConnection = mconnoracle
        .CommandText = szCmd
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter(, adNumeric, adParamInput, , plngId)
           
    End With
   
    Set rs = cmd.Execute()

but this gives me an error about parameters (wrong number or types of arguments in call to 'GET_USERTEST1') - my understanding from reading various items on the web is that its not necessary to add a parameter in the VB cmd object for the returning recordset.

However If I change the command text to embed the ID in the commands text and make the commandtype adCmdText then the code  executes successfully. I'm a bit nervous about using this as I'm not sure why this way works. Should the first method work and if so what have I done wrong?

This works:-
    szCmd = "begin DATA_TOOLS.GET_USERTest1(7370); end;"
   
    Set rs = New ADODB.Recordset
    Set cmd = New ADODB.Command
   
    With cmd
       
        .ActiveConnection = mconnoracle
        .CommandText = szCmd
        .CommandType = adCmdText
                   
    End With
   
    Set rs = cmd.Execute()
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
many thanks for your prompt response

COuldnt get the MSDAORA to work but the OraOLEDB.Oracle with the PLSQLRSet=1 extended properties dit the trick . Originally my connection string was "Driver={Microsoft ODBC for Oracle}" but none of my attempts to retunr a cursor worked. Do you know of any good references sies where I can read a bit about the differences in the providers behaviour. I should really find out more before I push out the existing code with a new provider string!

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>Do you know of any good references sies where I can read a bit about the differences in the providers behaviour
no, as each one cooks it own meal... MSDAORA is what microsoft made as provider to make the linked servers in sql server to work
the ms odbc for oracle is to use the odbc driver instead of oledb.

only the oraoledb is from oracle, so only those can really know how to internally do the internal stuff efficiently...
the only way is to test, sad to say.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
note that 10 years ago, I had a terrible choice to do:
* use the oracle odbc driver, which was slow
* use the ms oracle odbc driver, which had a bug
* change my nice code to terrible code

Author

Commented:
Thats good to know as we currently have an old SQL server 6.5 database and our app interacts with our oracle 10g rack. Hope to upgrade SQL server pretty soon so the linked server stuff may soon be of interest - but thats another story....

Thanks again.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.