Skip to main content
santhoshkumark
New Participant
Mar 9, 2023 10:25 AM UTC
Solved

Execute Dynamic Select Query in Oracle Store Procedure

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.

abhayd3663
Brainy
Mar 10, 2023 5:37 PM UTC

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 ;

4 replies

abhayd3663
Brainy
Mar 9, 2023 3:04 PM UTC

Please share the code snippet of the stored proc to understand the issue.

santhoshkumark
New Participant
Mar 10, 2023 4:21 AM UTC

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.

abhayd3663
Brainy
Mar 10, 2023 5:37 PM UTC

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 ;