Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

(Oracle) Stored Proc returning rst back to VB6

Status
Not open for further replies.

Andrzejek

Programmer
Jan 10, 2006
8,509
US
I have this simple Stored Procedure in Oracle:

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

Code:
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.
 
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

FAQ219-2884
FAQ181-2886
 
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top