Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
calling stored procedure from Oracle
#1
Not Solved
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
Reply
#2
Not Solved
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.
Reply
#3
Not Solved
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.
Reply
#4
Not Solved
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
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  calling QTP script from one to another krishnas.tester 3 6,695 11-12-2014, 10:32 AM
Last Post: vinod123
  Request Help | Usage of Oracle Add-In for Oracle SQL Developer Nasir Ahmed 3 3,506 06-18-2014, 04:28 PM
Last Post: vinod123
  Need to put a value in editbox and the value is stored in a variable anushreebehura 2 2,615 05-10-2013, 03:03 PM
Last Post: SweetyChowdhury
  Function is not calling from subdriver qtpexpert 0 2,055 01-18-2013, 12:25 PM
Last Post: qtpexpert
  While calling sub procedure its giving error Akhila 1 2,485 10-22-2012, 01:55 PM
Last Post: harishshenoy

Forum Jump:


Users browsing this thread: 1 Guest(s)