Unable to receive response after calling PL/SQL stored procedure or function from JDBC request.

[skareemulla]

I want to call the store procedure and PL/SQL function using Ready API 1.8/1.6 version.
I am using Oracle database.
I tried to call the procedure through Groovy script and JDBC also but I am not able to call store procedure using both .
My store procedure name TEST1 .It consists two parameters . One input parameter and output parameter.

The procedure and function returning response in REFCURSOR

procedure:

{call packarename.TEST1(1222122,?)}

Fucntion: 

{call packagename.Funtion1('112331',null,'2',null,null,null,'2')}

Please suggest.

Thanks

Kareem

[TanyaYatskovska]

Hi Kareem,

Please refer to this topic:

https://community.smartbear.com/t5/SoapUI-NG/Caling-a-Stored-Procedure-in-JDBC-Test-Step/m-p/122858

It contains sample scripts of how to call stored procedures.

Does it help?

[skareemulla]

Hi Tanya Gorbunova,

I am not able to access the given link receiving an error "The page you are trying to access was not found. Please check your URL for typos and try again."

please help

Thanks

kareem

[TanyaYatskovska]

Hi Kareem,

It's strange. It works for me. Try this one.

[nmrao]

Yes, thread is not accessible.

[nmrao]

this one too not accessible.

[nmrao]

Check the below link and see if that helps.
http://www.javaworld.com/article/2074010/core-java/better-jdbc-with-groovy-sql.html

[skareemulla]

Hi,

I got the solution for it, now I can able read the response which is returned by calling  PL/SQL stored procedure or function using below code

Prerequisite:

Add JDBC driver in Lib folder of  Ready API workspace.

Calling Procedure which is having out parameter as REF CURSOR

Import oracle.jdbc.*;
...
CallableStatement cstmt;
ResultSet cursor;

// Use a PL/SQL block to open the cursor
cstmt = conn.prepareCall
         ("{call PackageName.Procedurename(InputPar1,?)"); // '?' is for out parameter

cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.execute();
cursor = ((OracleCallableStatement)cstmt).getCursor(1);

// Use the cursor like a normal ResultSet
while (cursor.next ())
    {System.out.println (cursor.getString(1));} 

Calling PL/SQL function which return REF CURSOR in response:

Import oracle.jdbc.*;
...
CallableStatement cstmt;
ResultSet cursor;

// Use a PL/SQL block to open the cursor
cstmt = conn.prepareCall
         ("{ ? = call Packagename.Funcitonname(Par1,Par2)}");

cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.execute();
cursor = ((OracleCallableStatement)cstmt).getCursor(1);

// Use the cursor like a normal ResultSet
while (cursor.next ())
    {System.out.println (cursor.getString(1));} 

Regards

Kareem