I’d like to request support for executing Redshift stored procedures.
We’ve attempted to use the Redshift Execute snap to CALL a stored procedure that has one IN argument and does not have any OUT or INOUT arguments and received the following error:
Failure: SQL operation failed, Reason: A result was returned when none was expected., error code: 0, SQL state: 0100E, Resolution: Please check for valid Snap properties and input data.
Per the Redshift documentation (CREATE PROCEDURE - Amazon Redshift), “…the procedure returns one result row containing n columns where n is the total number of OUT or INOUT arguments.”
Since the stored procedure argument data type can be any standard Amazon Redshift data and that there can be more than one argument I suspect that it would be a challenge to be able to handle some of those types (i.e. refcursor) when there is more than one argument.
So, if it would be possible to provide limited support for executing Redshift procedures (i.e not support refcursor data type) there would significant value gained. Without it we have no way to include execution of Redshift stored procedures as a part of our pipeline. We either need to orchestrate the execution of stored procedures outside of SnapLogic, or we need to attempt to implement the logic totally using the SnapLogic Redshift snap pack, and that may not be possible, or would not be as efficient as using a stored procedure.