Returning data from a Dynamic PL/SQL Query

Question

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?

It depends on how the data is consumed. If it's by an application, then it's common to return a ref cursor and the application can read from that cursor. If it's by another PL/SQL process, that process should almost certainly know the exact type of the data that's going to be returned. It's rare to have a PL/SQL object accept and process "any" input. It can be done but it's usually ugly. — Jon Heller
– Jon Heller, Commented Feb 2, 2017 at 19:29

XING · Accepted Answer · 2017-02-02 17:53:06Z

1

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;

edited Feb 2, 2017 at 17:53

answered Feb 2, 2017 at 17:42

XING

9,9164 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Returning data from a Dynamic PL/SQL Query

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related