executeStoredProcedureForQuery error

Did you try to run the procedure from DB side? I think the issue is from DB side only not from Appian Side.

java.sql.SQLException: Invalid column type: 2012 when using Hibernate and Oracle stored procedure with REF_CURSOR - Hibernate ORM - Hibernate

Thank you Deepak for responding.

Procedure is working fine and been used for a while.

I am migrating from executestoredprocedure plugin to Appian executeStoredProcedureForQuery and getting this error.

What data type is ri!gcids?  I've noticed the OOB stored procedure rule is a bit more finnicky about little data differences than the older plug-in version, so I'd start there (particularly upon seeing the error message text).

It's a Text type. The error is gone when I remove the Out parameter (REFCURSOR). The IN parameters seem don't trigger the error but the Out parameter does.

I don't believe you will need to define your OUT parameter with the IN parameters - with only the input defined as above, can you access the return from local!spResult.parameters.DATA_CURSOR?

Chris, when I said remove Out parameter, I did it in Procedure to make sure no issue with IN parameter.  IN parameter looks fine. The error occurs when I have OUT parameter in Procedure. 

local!spResult.parameters.DATA_CURSOR is not accessible since the execution is error.

Gotcha - unfortunately I'm only familiar with the older fn!executeStoredProcedure().  This may be a case for Appian support.  The "Invalid column type: 2012" error you are receiving appears to be an Oracle error message however, related to the REF_CURSOR.

Just curious, as the procedure is doing a simple SELECT, is there some reason you are not able to complete this with a!queryEntity()?

There are some notes related to Oracle and cursors in the documentation for this function as well: 

Oracle

• The stored procedure name and input parameters are case sensitive.
• For a stored procedure that is defined within a package in an Oracle database, enter the procedureName as package_name.stored_procedure_name
• Access the stored procedure query results in the parameters key using the name that corresponds to the cursor parameter, instead of in results.

Cursors have some notes in the PostgreSQL section, but mostly about where to obtain the results:

Because PostgreSQL databases use cursor objects for the stored procedure output, the query results from this stored procedure are returned in the parameter key.

However, if you execute a similar stored procedure in a database that doesn't use cursor objects for the stored procedure output, the query results will be in the results key instead. Below, you can see how the results will differ between stored procedures executed on PostgreSQL and MariaDB.

Gotcha - unfortunately I'm only familiar with the older fn!executeStoredProcedure().  This may be a case for Appian support.  The "Invalid column type: 2012" error you are receiving appears to be an Oracle error message however, related to the REF_CURSOR.

Just curious, as the procedure is doing a simple SELECT, is there some reason you are not able to complete this with a!queryEntity()?

There are some notes related to Oracle and cursors in the documentation for this function as well: 

Oracle

• The stored procedure name and input parameters are case sensitive.
• For a stored procedure that is defined within a package in an Oracle database, enter the procedureName as package_name.stored_procedure_name
• Access the stored procedure query results in the parameters key using the name that corresponds to the cursor parameter, instead of in results.

Cursors have some notes in the PostgreSQL section, but mostly about where to obtain the results:

Because PostgreSQL databases use cursor objects for the stored procedure output, the query results from this stored procedure are returned in the parameter key.

However, if you execute a similar stored procedure in a database that doesn't use cursor objects for the stored procedure output, the query results will be in the results key instead. Below, you can see how the results will differ between stored procedures executed on PostgreSQL and MariaDB.

Yes looks like it REFCURSOR issue (code 2012).

and yes I couldn't use query entity in this case for some reason.