Skip to main content
santhoshkumark
santhoshkumark
March 9, 2023
Solved

Execute Dynamic Select Query in Oracle Store Procedure

  • March 9, 2023
  • 4 replies
  • 0 views

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.

Best answer by abhayd3663

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
abhayd3663
March 9, 2023

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

santhoshkumark
santhoshkumarkAuthor
March 10, 2023

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
abhayd3663Answer
March 10, 2023

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 ;

    Terms & ConditionsAccessibility statement