Retrieve Out parameter from Oracle Stored Procedure

[wesalencar (Weslley Alencar)]

Hi all,

I’m using an Oracle Database and I’ve been trying to call a Stored Procedure but I can’t seem to find a way to retrieve the value of an OUT parameter in a Studio variable. Both CommandType options (‘Text’ and ‘StoredProcedure’) don’t seem to work properly when using an OUT parameter, regardless of the database activity used (Execute Query/Non Query). Here is what I have tried so far and the respective outputs:

1. CommandType: StoredProcedure. Result: Execute Non Query: ERROR [42000] [Microsoft][ODBC driver for Oracle][Oracle]ORA-00900: invalid SQL statement.

In this case, I can’t even make a valid call to the database. Something about the statement seems wrong but according to other forum posts this should work perfectly fine for other databases.

2. CommandType: Text. Result: the procedure is correctly called, but no Out parameter is returned to Studio.

In this case, In parameters work but after the procedure is executed it seems like no binding is done between the output variable in Studio and the Out parameter in the procedure. The row is added to the database table but the variable that is supposed to return the recently added table ID remains equal to 0.

For all the calls I’m using this Provider Name: “System.Data.Odbc”.

Any suggestions?

[Nithinkrishna]

Hey @wesalencar,

Its good to see you back here

I guess you need to change the argument direction in UiPath as out.

Hope this helps

Thanks

[wesalencar (Weslley Alencar)]

Hi @Nithinkrishna,

I appreciate your quick response. Changed the parameter direction and interestingly now both scenarios previously explained present the exact same error: Execute Non Query: String[1]: the Size property has an invalid size of 0.

Searching a little bit about this error, I found out that this could be due to not specifying the size of a varchar2 parameter in the procedure or in the ExecuteNonQuery method. It doesn’t make much sense to me since I was able to pass the In parameter a few times succesfully. I wonder if this error message is misleading in some way once the output is a Number/Int64 parameter… And I just saw a very similar case without a solution. Activity Execute non query - Erros to get OUTPUT parameters when calling an StoredProcedure

Any thoughts?

[Nithinkrishna]

Exactly, can you please try setting size for your varchar in stored procedure

[wesalencar (Weslley Alencar)]

I can’t define a VARCHAR size in a Stored Procedure parameter, only when declaring variables. Seems like a PL/SQL limitation.

[Tudor_Sandu]

Three things here:

1. The correct way to call a stored procedure in Oracle ODBC is CALL Procedure_Name(?,?...,?) and it’s coded in the DB driver, not the activity, so there’s no going around it.
2. Regarding the Size property error, it’s been addressed for SQL Server and Oracle ODBC and will be fixed in a release we are planning to do in January.
3. We are aware of the out parameters issue and we will be trying to come up with a fix in the same January release of the Database activities.

[wesalencar (Weslley Alencar)]

Hi @Tudor_Sandu, thank you for the information. Looking forward the January release to have this potentially solved then.

Before marking this as the solution, can you just provide an example of point 1 you mentioned? Not sure I get it entirely since I was able to call the procedure in distinct ways.

[Tudor_Sandu]

@wesalencar

Before marking this as the solution, can you just provide an example of point 1 you mentioned? Not sure I get it entirely since I was able to call the procedure in distinct ways.

In your case, you should use CALL SP_GEREN_CRGA_TRAB_I(?,?) instead of SP_GEREN_CRGA_TRAB_I(?,?)