JDBC Stored Procedure Call

Hi Shruti,

Create one java class file which would establish connection between JDBC and your DB.

Make jar file of this class and put it in SOAPUI bin/ext folder.

Use this class in groovy step to establish connection with DB  and then call store procedure with syntax as

{call SCHEMA_NAME.PACKAGE_NAME.STORE_PROCEDURE_NAME(?,?,?,?,?,?,?,?,?,?,?,?)}";

WHERE ?= IN and OUT parameters

---

shruti wrote:
I want to call the store procedure using SOAP UI.
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.

CREATE procedure test1(abc in varchar,result out varchar)
is
begin
insert into tst1 values(abc);
dbms_output.put_line(result);
end;


I tried following syntax using JDBC:

1st Try :
@declare @param1 varchar(50)
@set @param1 := 'XXX'
@exec client..TEST1 @param1

2nd Try :
execute TEST1('SAM',result)

3rd Try:
DECLARE
ABC VARCHAR2(32767);
BEGIN
ABC := 'Testing';
EMAPP.TEST1 ( ABC );
COMMIT;
END;


I used following combination through Groovy script

1st :
sql.call("{?=call TEST1(?)}",[sql.VARCHAR,'SHRUTI']) {result -> assert result=='OUTPUT'}

2nd :
def first = 'Sam'
sql.call("{$Sql.VARCHAR = call TEST1($first)}") { name ->
assert name == 'Sam Pullara'

Please suggest me that How I can call the store procedure using groovy or JDBC ?

Thanks
Shruti

---