CODEWITHSUNDEEP
oracle-databaseplsql
Damien
Damien

Reputation: 14057

Returning data from a Dynamic PL/SQL Query

I am fetching a query stored in the database, attempting to execute it with parameters, and then handle the result. 

DECLARE 
  SQLSTR VARCHAR(5000); 
BEGIN 
SELECT SelectString INTO SQLSTR FROM MySelectTable WHERE Name = 'QueryOfDoom';
EXECUTE IMMEDIATE SQLSTR USING 1;
END;

The query executes but nothing is returned. I've searched around on here and found you can't return data from a bloc. That's fine. However how can I get data out? Since it's dynamic it strikes me as cumbersome if I had to define a table structure to bulk collect the data and subsequently select from. Is there a easier way?

Upvotes: 0

Views: 99

Answers (1)

XING
XING

Reputation: 9886

You have to use this way. The way you are doing PLSQL is not able to use using clause as it finds nothing to bind. 

DECLARE 
  SQL1 VARCHAR(5000); 
  SQLSTR VARCHAR(5000);   
BEGIN 
sql1:= 'SELECT SelectString FROM MySelectTable WHERE Name = :QueryOfDoom';

EXECUTE IMMEDIATE SQL1 into SQLSTR USING 1;
END;

Upvotes: 1

Related Questions