How to call oracle PL\SQL procedure in QTP

[NEERJA nawani]

Hi,

  I am trying to call PLSQL procedure from QTP with following code.

but not able to execute it. Pls tell me right method  to call PLSQL procedure with input and output parameters.

 rc = com_load_environment()

rc = com_OpenDBConnection()

Set objRsData = CreateObject("ADODB.recordset")

Set objRSCMD = CreateObject("Adodb.command")

objRSCMD.ActiveConnection =objConnDB

objRSCMD.CommandType = 4

objRSCMD.CommandText =  "{CALL Test.PKGTEST.createBusinessEntity(?,?,?,?,?)}"

objRSCMD.Parameters.Append .CreateParameter("ErrorText", adNumber,adParamOutput,null)

objRSCMD.Parameters.Append .CreateParameter("ErrorCode", adNumber,adParamOutput,null)

objRSCMD.Parameters.Append .CreateParameter("Businessentityid", adNumber,adParamOutput,null)

objRSCMD.Parameters.Append .CreateParameter("Businessentityname", adVarchar,adParamInput,"BEname")

objRSCMD.Parameters.Append .CreateParameter("Businessentitytypeid", adNumber,adParamInput,504)

objRSCMD.Parameters.Refresh

 objRSCMD.Execute 

--

---

Thanks and Regards

Neerja Nawani

[Chethana KP]

Try as below it should work :)

             

 References to read about the “ADODB.Command”  and “ADODB.Parameter”  and “CreateParameter” methods

 http://www.w3schools.com/ado/ado_ref_command.asp

 http://www.w3schools.com/ado/met_comm_createparameter.asp

 

Store Proc which I tired is to acknowledge the Payments Status which is as follows:

 

exec dbo.tp_pmt_inbound_process_ack

            @arg_ackmsgid='10189',

@arg_ackmsgtime='2009-12-10T05:02:09',

@arg_ogltx_execdate         = '2009-12-10T05:02:09',

            @arg_ogltx_creditorname     = 'OMC'

           

 

The following things are necessary to know before trying to run the store Proc

 

1)       Store proc name: “dbo.tp_pmt_inbound_process_ack”

2)       Parameter Name and Parameter Type of the input parameter which has  to be passed

We can see these details in the help section of DB:

Query: sp_helptext tp_pmt_inbound_process_ack

The above query returns the Parameter Type, Max Size of the value

For EX: arg_ackmsgid=‘10189’ in the above Parameter

                Name: arg_ackmsgid

                Type : adVarChar

                Max Size = 35

                            Parameter direction = adParamInput

                            Value = '10189'

       But the DB does not support “Constant” of the Data Type while executing from QTP the “Data Type Enum Values” should be given as follows:

            Reference : http://www.w3schools.com/ado/met_comm_createparameter.asp

           

                        Set objparameter=objcommand.CreateParameter (name,type,direction,size,value)

                                   

                                    Name : “arg_ackmsgid”

                                    Type   :  200

                                    Direction: 1

                                    Size       : 35

                                    Value      : “10189”                                

 

3)       The Mandatory Fields in the Store Proc has to be specified.

If NOT QTP throws an error as follows: “No value given for one or more required parameters.”

 

4)       When size of the input Parameter value is NOT specified correctly the following error message will be thrown by QTP

“Application uses a value of the wrong type for the current operation.”

 

 

Code:

 

Dim connection, theCommand, SQL, param

 

Set connection = CreateObject("ADODB.Connection")

Set theCommand = CreateObject("ADODB.Command")

connection.Open (Environment.Value("ConnectionStr"))

 

SQL = "{call dbo.tp_pmt_inbound_process_ack(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}"

‘The question marks should be equal to the number of Input parameters in the Store Proc

 

thecommand.CommandText = SQL

thecommand.CommandType = 1

thecommand.ActiveConnection = connection

 

 

Set param = thecommand.CreateParameter("arg_ackmsgid",200,1,35, "9679")

thecommand.Parameters.Append param

 

Set param = thecommand.CreateParameter("arg_oglnumoftxs",3,1,5,1)

thecommand.Parameters.Append param

 

Set param = thecommand.CreateParameter("arg_gp_status",200,1,4, "ACTC")

thecommand.Parameters.Append param

 

 

Set param = thecommand.CreateParameter("arg_ogltx_endtoend_id",200,1,30, "RA0000000002")

thecommand.Parameters.Append param

 

Set connection= nothing

Set theCommand= nothing

 

 

Regards,

Chethana

--
You received this message because you are subscribed to the Google
"QTP - HP Quick Test Professional - Automated Software Testing"
group.
To post to this group, send email to Mercu...@googlegroups.com
To unsubscribe from this group, send email to
MercuryQTP+...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/MercuryQTP?hl=en