cancel
Showing results for 
Search instead for 
Did you mean: 

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

Highlighted
New Contributor

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

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

 

 

7 REPLIES 7
Highlighted
Community Manager

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

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?

---------
Tanya Gorbunova
SmartBear Community Manager

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
Highlighted
New Contributor

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

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

Highlighted
Community Manager

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

Hi Kareem,

 

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

 

---------
Tanya Gorbunova
SmartBear Community Manager

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
Highlighted
Community Hero

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

Yes, thread is not accessible.


Regards,
Rao.
Highlighted
Community Hero

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

this one too not accessible.


Regards,
Rao.
Highlighted
Community Hero

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

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


Regards,
Rao.
Highlighted
New Contributor

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

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

 

New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors