executeStoredProcedureForQuery error

Certified Associate Developer

I am getting error as below on calling Oracle's stored procedure using a!executeStoredProcedureForQuery. 

Stored procedure and expression are attached below. Help is much appreciated.

  Discussion posts and replies are publicly visible

Parents Reply Children
  • 0
    Certified Associate Developer
    in reply to Mike Schmitt

    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?

  • 0
    Certified Associate Developer
    in reply to Chris

    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.

  • 0
    Certified Associate Developer
    in reply to Chris

    Yes looks like it REFCURSOR issue (code 2012).

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