I want to run a Dynamic "Select" Statement in Oracle DB.
So used a SYS_REFCURSOR output variable and used "OPEN FOR" Statement to execute it. While executing this stored procedure I got this following error
Any suggestions or ideas would be greatly appreciated.Thank you ahead of time.
Discussion posts and replies are publicly visible
Please share the code snippet of the stored proc to understand the issue.
Hi Abhay,This is the code we tried,
CREATE OR REPLACE procedure testprocedure(cursur out SYS_REFCURSOR)
is
sql_stmt VARCHAR2(150);
begin
sql_stmt := 'SELECT * FROM testTable';
open cursur FOR sql_stmt;
end ;
Found out a similar thread in community, https://community.appian.com/discussions/f/general/25773/executestoredprocedureforquery-error
We are checking whether we can fix it by updating the JDBC driver.
This is what I used for one of my project. It's MYSQL.
DELIMITER $$ CREATE DEFINER=`appian`@`%` PROCEDURE `TestProc`(IN `sp_tableName` VARCHAR(30)) NO SQL COMMENT 'Dynamic Select Query.' BEGIN SET @sSQL = CONCAT('SELECT * FROM ', sp_tableName); PREPARE stmt FROM @sSQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END$$ DELIMITER ;
The issue resolved by updating the JDBC driver.
Thanks for the Mysql Code, we too have implemented same for our MySQL database,