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

[krr]

From the code you have provided above I came to conclusion that you are missing with connection string.
1)provider, database, id, password, dataset.
2)object for Command, Its like set cmd=createObject("ADODB.Command")
3)I saw "smc". What is this object all about?

Try correct above suggested things and let me know the result if it worked or not.

[borisk]

Sorry, I couldn't cut and paste the actual code so I retyped it and made a few typos. 'Smc' is actually 'cmd'. Connection string is correct in the actual code and trust me this is not a connection issue. It seems this logic doesn't work in Oracle. I read somewhere that in order to use Stored procedure returning results in Oracle I actually need a different logic: Instead of creating a Command object I need to create an OracleCommand object using Oracle specific driver, and all the other commands have to be changed. I couldn't find any example of this in QTP. A sa result I changed the approach and now create the result table inside stored procedure and after calling it from QTP, read the results from this table in QTP script. This is not elegant, but I had to move forward.

[gaveyom]

Hi borisk,

try the below code, hope it will resolve your issue

Code:

Set objDB = CreateObject("ADODB.Connection")
strDSN = "DRIVER=SQL Server; DATABASE=[databasename];APP=QuickTest Professional;SERVER=[servername];Description=Testconnection"
objDB.Open(strDSN)
Set objRec = objDB.Execute("exec [spname] '[param1]','[param2'")
Do Until objRec.EOF
        Msgbox objRec.Fields("[column]")        
        objRec.MoveNext
Loop
Set objRec = Nothing
Set objDB = Nothing