calling stored procedure from Oracle

calling stored procedure from Oracle - Printable Version

+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: VB Scripting/Descriptive Programming (https://www.learnqtp.com/forums/Forum-VB-Scripting-Descriptive-Programming)
+--- Thread: calling stored procedure from Oracle (/Thread-calling-stored-procedure-from-Oracle)

calling stored procedure from Oracle - borisk - 12-18-2012

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

RE: calling stored procedure from Oracle - krr - 12-31-2012

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.

RE: calling stored procedure from Oracle - borisk - 12-31-2012

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.

RE: calling stored procedure from Oracle - gaveyom - 03-19-2013

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