In pl/sql how can I execute a select statement stored in CLOB type of field?

Question

In Oracle DB, I have a large select staetment with lots of joins and cases that is stored in a CLOB field in one of my tables. How can i execute this statement from the CLOB?

Answer 1

Look into the EXECUTE IMMEDIATE syntax.
Example table:

CREATE TABLE test(id number, largedata clob);
INSERT INTO test VALUES (1, 'select name from v$database'); 
commit;
select * from test;

DECLARE
l_sql clob;
l_result VARCHAR2(50);
BEGIN
  SELECT LARGEDATA INTO l_sql FROM TEST;
  EXECUTE IMMEDIATE l_sql INTO l_result;
  dbms_output.put_line(l_result);
END;
/

Output is the DB name.