Execute procedures in oracle data base

Help
,
1

Hello guys!!
I am connected to an oracle database and am trying to run a procedure on uipath. It performs well, but after this query I execute another one that gives a select in this procedure, but an error is generated saying that the string is not in the correct format

This is my error:

image
image639×536 18.9 KB

The String is in the correct format.
I’ve been taking a look at the behavior of the program and realized that the error is generated because somehow after executing the first query (procedure) it is deleted or the database closes or something.

I come here to see if anyone has passed through this experience or know how to solve the problem.

Thank you very much!

2

Hi, Based on your investigation,

Have you tried removing the first query with procedure and test? Do you use parameters to pass value on your query?

By any chance, can you share your query?

3

Hello friend, I took the first query, but getting only the second it generates an empty dataTable, and I’m not passing parameters. Would it be possible for me to pass the second query as a parameter of the first one?

This is the sql that I need to execute (it is not possible to execute it all. It has to be the procedure first and then the select):

begin
sinagro.fluxo_pagar_sinagro (TO_DATE(‘01/08/2012’, ‘DD/MM/YYYY’), TO_DATE(‘26/03/2019’, ‘DD/MM/YYYY’), TO_DATE(‘26/03/2019’, ‘DD/MM/YYYY’));
end;

SELECT * FROM sinagro.FLUXO_PAGAR ORDER BY 1,2,6,12

Thank you very much for replying, I await your return.

4

What is the function of this script? ^

I think the right way is:

  1. Write and apply the stored procedure on the target database.
  2. Using Execute Non Query, you will call the stored procedure name and set the commandtype property as Stored Procedure.
  3. You can also pass values on the stored procedure parameters.
5

Hi @Emman_Pelayo,
I am also working on a similar project where I need to call a ORACLE stored procedure from UiPath which is already deployed in the target database and get output as a data table for further processing.

The procedure will take manager id as a input parameter and outputs all employees names, employee id’s reporting him/her. Although it is a simple select query we are planing it to implement through stored procedures.

I can implement this in My SQL database but in Oracle I am unable to do it.

Can you help me by showing how the procedure should look like in Oracle so that it should give an output as datatable. Any sample file would be much helpful

6

Hi @vinaynasani,

I haven’t tried with oracle yet, but maybe you can try:

In UiPath - Create Execute non query with:
in param = manager id, string/int32

In Oracle stored procedure, you insert your query result on a temporary table

In UiPath - Create Execute Query which selects the temporary table.

Regards.

7

Sorry Emman, that is not the answer he was expecting and neither am I because it doesn’t answer the problem. Looking on the forum I can see multiple questions on this topic but NO valid answer.
Can someone post a sample xaml that does this?

Here is a sample stored proc that I created please use this:

CREATE OR REPLACE PACKAGE DA_TEST AS

TYPE sqlcur IS REF CURSOR;

/* ********************************************************************* */

PROCEDURE Get_Operator (
in_org_id IN VARCHAR2,
inret_data IN OUT sqlcur);

/* ********************************************************************* */

END DA_TEST;
/

CREATE OR REPLACE PACKAGE BODY DA_TEST AS

PROCEDURE Get_Operator (
in_org_id IN VARCHAR2,
inret_data IN OUT sqlcur)
IS

BEGIN
OPEN
inret_data
FOR
SELECT 123 as ORG_ID
, ‘test1’ as ORG_NAME
FROM DUAL
UNION
SELECT 456 as ORG_ID
, ‘test2’ as ORG_NAME
FROM DUAL
UNION
SELECT TO_NUMBER(in_org_id) as ORG_ID
, ‘test3’ as ORG_NAME
FROM DUAL;

END;

/* ********************************************************************* */

END DA_TEST;
/

1 Like
8

I welcome myself to the club of “looking on the forum I can see multiple questions on this topic but NO valid answer”, @Emman_Pelayo. Have you found, by any chance, any way to overcome this after 4 years of pain?

I can’t believe noone is having the requirement to call stored procedures in their Studio projects using ORACLE and they can live without this.

9

Stored procedure works and u can use in/out parameters. But it works only with string or numeric types, and not SYS_REFCURSOR

10

Thanks for the comment, @AlexandraVeizu, but without a piece of code showing the proper format and maybe a captura of the proper activity setup, there’s not much I can try to see if it works…