troubleshooting Question

Returning recordset to VB6 using cmd object and oracle sys_refcursor

Avatar of colindow
colindow asked on
Oracle DatabaseVisual Basic Classic
5 Comments1 Solution4413 ViewsLast Modified:
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()
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros