Forum Discussion

shyambiyar's avatar
shyambiyar
New Contributor
11 years ago

How to call an oracle stored procedure to return one param

I need to make a call to an oracle stored procedure. It takes 4 arguments - one of which is an output parameter.

declare
my_name varchar2(20);
begin
l_interface.P_GET_NEXT_NAME(my_name, 'a', null, 'd');
dbms_output.put_line('My name: '||my_name);
end;

The above code is how I am able to see the returned value in TOAD.

In soapUI Pro, I used the JDBC step and used the below as the SQL Query. Selected the checkbox that says 'Stored Procedure'.

 L_INTERFACE.P_GET_NEXT_NAME (:myName,'e', NULL, 'd')

The variable myName was declared in the properties section of the JDBC test step - I gave an empty value to this. When I run the test step, the output was the following:

<Results>
<UpdateCount>-1</UpdateCount>
</Results>

My objective is to verify that the myName variable/output-parameter is giving me a value that is consistent with the input-parameters provided. I think there are two problems that I need help with:

  • The stored procedure result is giving -1 instead of 1 as the updated row count.
  • Once the stored procedure gives me correct output (1 row updated), I need to get the value of the myName variable.

    I did google and browse the forum and all. But did not find anything that solves my problem. Please help.

    Thank you.
  • 4 Replies

    • shyambiyar's avatar
      shyambiyar
      New Contributor
      Thanks Reshma. I had already done the Groovy way. However, I wanted to do the JDBC step way since it is supposed to be a more straightforward method. I hope somebody has a solution with respect to the JDBC step itself.
    • Hi, I am also trying to call store procedure with one output parameter using JDBC way or Groovy way. I have no clue of using groovy script. can somebody help ?
    • nmrao's avatar
      nmrao
      Champion Level 3
      Please avoid cross posting. Locking this topic.