Calling Oracle Stored Procedures that return SYS_REFCURSOR

[Mykitiuk_Darrell]

A number of people seemed to have asked similar questions over the years, but I do not see complete or relevant answers.

In Oracle we have a simple stored procedure that takes an input parameter and returns a cursor as an output parameter. We have experimented with SYS_REFCURSOR and strongly typed cursors, the results are the same.

For example: PROCEDURE GET_INDICATORS_FOR_ASSET(p_assetNumber IN VARCHAR2, indicators OUT SYS_REFCUR);

In UiPath we have configured the ExecuteQuery activity as follows:

• Command type: StoredProcedure
• SQL query: “GET_INDICATORS_FOR_ASSET(p_assetNumber, indicators)”
• Parameters:
  – p_assetNumber - In - String - “123”
  – indicators - Out - DataTable

Is DataTable the correct ‘Type’ for the Out parameter? If not, what type should be used when a SYS_REFCURSOR is returned by the stored proc?

As we’ve experimented trying to get this basic feature working, the errors we are seeing vary from “UnmarshalColumnData unimplemented type” to “ORA-06550 invalid PL/SQL”.

We have experimented with the Oracle.ManagedDataAccess.Client as well as the OLE Db driver for Oracle - the results are the same.

Any help would be appreciated.

[Ashish_Soni]

Hi @Mykitiuk_Darrell ,

The datatype is correct.

The error “UnmarshalColumnData unimplemented type” to “ORA-06550 invalid PL/SQL” usually occurs when UiPath is unable to map a column data type returned by a stored procedure to a corresponding .NET data type. This can happen when the stored procedure returns a data type that is not supported by UiPath or when the data type is not recognized correctly.

To resolve this error, you can try the following solutions:

1. Check the data types of the columns returned by the stored procedure and compare them with the data types supported by UiPath. Some data types, such as BLOB or LONG, may not be supported by UiPath. If the stored procedure returns unsupported data types, you can modify the stored procedure to convert the data to a supported data type or to split the data into smaller chunks.
2. Check if the stored procedure is returning any null values or empty strings. UiPath may not be able to handle null or empty values in some cases. You can modify the stored procedure to return default values for null or empty values or handle them in UiPath using conditional statements.
3. Verify that the stored procedure is written correctly and does not contain any syntax errors. You can try executing the stored procedure in a SQL client to check if it returns data without any errors. If there are any syntax errors, you need to correct them before executing the stored procedure in UiPath.
4. Check the version of the Oracle client installed on your system. UiPath may not support some older versions of the Oracle client. You can try updating the Oracle client to the latest version and see if the error persists.
5. Try using a different approach to retrieve data from the stored procedure. For example, instead of using the Execute Non Query or Execute Query activities, you can try using the Oracle.DataAccess.Client.OracleDataAdapter class in a custom activity to retrieve data from the stored procedure.

If none of the above solutions work, you can try contacting UiPath support for further assistance.

Thanks

[Mykitiuk_Darrell]

Hi Ashish,

I have created an even simpler stored proc to eliminate the first 3 possibilities:

The ORA-06550 error remains.

We are using Oracle.ManagedDataAccess.Core version 3.21.50 so I don’t believe it is an Oracle version issue.

I’ve created a ticket with UiPath in case they’ve got ideas.

Thanks for your help.

[Anil_G (Anil Gorthi)]

@Mykitiuk_Darrell

First use only the stored procedure name in the query field…not the parameters names as well

Also can you check the exception details from locals panel…that would provide more info on the exception

And also you can try using the output variable to check if we are getting output there

Cheers

[Mykitiuk_Darrell]

When we supply only the name of the stored procedure in the query field (no parameter names) we get a “wrong number or types of arguments” error.

[Anil_G (Anil Gorthi)]

@Mykitiuk_Darrell

How many inputs kor outputs are needed for your proc?

Did you give correct number and direction in parameters

Cheers

[Mykitiuk_Darrell]

In my latest test the “GET_SUPER_SIMPLE_RESULT” stored procedure takes 0 inputs and returns 1 output cursor. It doesn’t get any simpler than this.

[mario.l (Mario)]

what happens if you use one of the following for Edit SQL":

“GET_SUPER_SIMPLE_RESULT(:indicators)”

or

“BEGIN GET_SUPER_SIMPLE_RESULT(:indicators); END;”

Also, you may need to use “Text” Command Type

[anandhanr]

Hi @Mykitiuk_Darrell,

Did you get any solution for this? I am also facing the same issue. Did anyone get any solution for this? I just want to know how we can call the Oracle stored Procedure with Out parameters from UiPath.

[jnfantasy4]

Did you get any solution for this? I’ve the same problem

[jnfantasy4]

Now can you fix it? I’ve the same problem

[Marcelo_Guimaraes (Marcelo Guimarães)]

You need to assign the proper type in the arguments: