(Oracle) Stored Proc returning rst back to VB6

[Andrzejek]

I have this simple Stored Procedure in Oracle:

CREATE OR REPLACE PROCEDURE ANDY_LETTINGS
(lettings_recordset OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN lettings_recordset FOR
    SELECT DISTINCT SOME_FIELD
    FROM TABLEA
  END ANDY_LETTINGS;
/

And I even granted myself an EXECUTE on this procedure

Now I have this code in my VB6 application:

Dim cmdMine As New ADODB.Command
Dim rsMine As New ADODB.Recordset

cmdMine.ActiveConnection = Cn [green]'This is my connection[/green]
cmdMine.CommandTimeout = 300
cmdMine.CommandType = adCmdStoredProc
cmdMine.CommandText = "ANDY_LETTINGS"
[red]
Set rsMine = cmdMine.Execute[/red]  [green]'Here is the problem[/green]

This last RED line gives me an error:
PLS-00306: wrong numbers or types of arguments in call to 'ANDY_LETTINGS'
ORA-06550:line 1, column 7

This Stored Procedure works just fine from VB.NET.
What am I doing wrong in VB6? Any ideas...?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[fredericofonseca]

try CommandText = "{ call ANDY_LETTINGS}"

VB6 has lots of quirks with Oracle and when you start dealing with parameters more will pop up - same with datatypes being returned

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Andrzejek]

I tried your suggestion, and got an error on the same red line:
PLS-00103: Encountered the symbol "ANDY_LETTINGS" when expected one of the following:
.(*@%=-+</> at in is mod ...... :-(

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Andrzejek]

It looks like if I log into Oracle as a schema owner, I can execute a simple Stored Procedure (as long as it does not return any recordset). Granting myself an Execute does not allow me to run SP - strange...

Have fun.

---- Andy

There is a great need for a sarcasm font.