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:
- CommandType: StoredProcedure. Result: Execute Non Query: ERROR  [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.
- 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”.
Its good to see you back here
I guess you need to change the argument direction in UiPath as out.
Hope this helps
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: 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
Exactly, can you please try setting size for your varchar in stored procedure
I can’t define a VARCHAR size in a Stored Procedure parameter, only when declaring variables. Seems like a PL/SQL limitation.
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.
In your case, you should use
CALL SP_GEREN_CRGA_TRAB_I(?,?) instead of
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.