How to get the output from stored procedure in UiPath

Help Activities
, ,
1

I have the following stored procedure.

DECLARE
  ACCOUNT_NUMBER VARCHAR2(32767);
  ORDER_ID VARCHAR2(32767);

BEGIN
  ACCOUNT_NUMBER:= '12345';
  ORDER_ID := NULL;
  storedProcedureName( ACCOUNT_NUMBER, ORDER_ID);
  DBMS_OUTPUT.PUT_LINE ( 'ORDER_ID = ' || ORDER_ID );
  COMMIT;
END;

I’m able to run the above stored procedure using ExecuteQuery. But, it is not returning the data. How can I get the ORDER_ID from the above stored procedure.

2

Hi,

Is there any error you are receiving while extracting data form the output data table. if you face any errors please share us with the screenshot.

And also please make sure that you have enabled the Command type as stored procedure in the dropdown to enable ability for the execute query activity to execute stored procedures.

Usually execute Query enabled with stored procedure will provide the output as data table.

once you get the datatable you could get the ORDERID by using following expression YourOutputDT.Rows(0).Item(“ORDERID”).ToString

I could see that you assigned NULL value for the ORDERID for testing are you going to enter any order number. thanks.

Please refer the below threads for more guidance. thanks.

3

Hi @kirankumar.mahanthi1 ,
It is not throwing any error and it is executing fine. But, the output datatable is empty.
And, in that SP, ‘ACCOUNT_NUMBER’ is the input and ‘ORDER_ID’ is the output arguments respectively. When, we execute the SP, it should return a value to ‘ORDER_ID’.
Thanks,
Krishnakanth K

4

ok got your point. Did you already tested in the Sql server management studio and is it giving us the correct result. thanks.

5

I’m using SQL developer. It’s ORACLE DB. It’s giving expected result.
Thanks,
Krishnakanth K

6

could you pls share the screenshot of your code or workflow to troubleshoot. thanks.

7

Are you returning just “Order ID” in the SP? or the entire matching row?

8

it is just returning ‘Order_ID’

Thanks,
Krishnakanth K

9

The output of ExecuteQuery is ‘DataTable’ of Type. This might be the reason.

Thank you.

10

sure.

image
image881×339 17.3 KB

11

Yeah. But, is there any other possible way to execute SP and get ORDER_ID.

Thanks,
Krishnakanth K

12

In a typical way it should return a DataTable with just 1 row and 1 column. Since you already mentioned the DT is empty. I have to replicate the same to check more on this.

Thank you.

13

Hi,

By seeing the screenshot I could find that your storing your query in a text file and reading and input that text directly in execute query will not work I guessm

CREATE PROCEDURE sp
AS

  • Your sql_statement*
    GO;

Create procedure with your statement in your db and call that name in the execute query SQL option as “sp”. I think we cannot execute the query directly after selecting drop down as stored procedure. Please try and let us know. Thanks.

14

Can we return the ‘ORDER_ID’, after DBMS_OUTPUT.PUT_LINE ( 'ORDER_ID = ' || ORDER_ID ); this statement, like we do in the methods.

Thanks,
Krishnakanth K

15

Please make the changes and let us know the result. Thank you.

16

it is throwing following exception.
image

Thanks,
Krishnakanth K

17

Hi,

Are you able to create procedure in your db (execute your statement by adding create procedure statement) with the name sp with your statement mentioned above.

Once we successfully create the stored procedure in your db.

Please use that stored procedure name in the query option in our case “sp” and try execute. Please share the output of your db if possible. Thanks.

Please don’t run the entire query in the execute query as statement. Since we selected dropdown as stored procedure we have to give the stored procedure name only in the query section not entire query.

18

Hi,
Stored procedure is already exist in the DB. It is parameterized. When I try to call it by name, by passing the parameters, it is throwing error.
We pass parameters using ‘@’. But it is throwing error, something like, unexpected symbol.

Thanks,
Krishnakanth K

19

Not all SQL sentences are queries to a table that return a set in a datatable.

If you don’t know this and can’t infere it from the question, then why not better refraining from giving unuseful answers? It gives false hope and makes users lose further time.

By seeing the screenshot I could find that your storing your query in a text file and reading and input that text directly in execute query will not work I guessm

CREATE PROCEDURE sp
AS

  • Your sql_statement*
    GO;

Create procedure with your statement in your db and call that name in the execute query SQL option as “sp”. I think we cannot execute the query directly after selecting drop down as stored procedure. Please try and let us know. Thanks.

That’s completely your invention. Nothing prevents you from writing the text there or from storing in a variable. It’s the same, UiPath offers the choice, and it’s straightforward to test it before posting a false statement.