calling stored procedure from Oracle

[borisk]

I am not really a QTP or even QA Specialist, but it so happened I am
helping a lady in my office to write a code to call a stored procedure
in Oracle. It is supposed to take two parameters, do something with
them and return the result set.
We are having trouble passing the parameters, so I decided to write a
version of this stored procedure which has no input parameters but
still returns a results set.

Basically the code is like this:

Code:

create or replace procedure XYZ(p_results OUT SYS_REFCURSOR) AS
  BEGIN
..
  open p_results for SELECT 'ABC' from dual;
  END;

When I test it through Oracle SQL Developer it works fine.

However, when we try to call it through QTP we get en error:

Code:

Set conn={create connection}

set cmd=Server.createObject("ADODB.Command")
Set smc.ActiveConnection=conn
cmd.CommandText="XYZ"
cmd.CommandType = 4 ' adCmdStoredProc

cmd.Parameters.Refresh
set rs = cmd.execute
Do Until rs.BOF Or rs.EOF
  print ...
Loop

We get an error:
[Microsoft] ODBC Driver for Oracle][Oracle]ORA-06550: line 1, column 7:
PLS - 00306: wrong number or typrs of arguments in call to 'XYZ'
ORA-06550: Line 1, column 7:
PL/SQL: Statement ignored

Line(57): "Set rs=cmd.Execute".

=====

It seems that Oracle is expecting some kind of a definition of this
ouitput parameter anyway.

What are we doing wrong?



Thanks