Question:

I am making a test. I have all tests in rows, so my rows looks like this;

ID | TEST ---------------------------------- 1 | 'select sysdate from dual' 2 | 'select sysdatesss from dual'

Now I read it row by row and I need to test it with EXPLAIN PLAN FOR

so the for the first row it would be

EXPLAIN PLAN FOR select sysdate from dual

but I have problem converting the TEST field. Right now I use;

EXPLAIN PLAN FOR testing.TEST

but it does not work.

Any ideas?

A SQL statement is a string, but you have to use dynamic SQL to convert a SQL statement that is stored as a string.

FOR i IN (SELECT t.test FROM TESTING t) LOOP EXECUTE IMMEDIATE 'EXPLAIN PLAN FOR '|| i.test ||''; END LOOP;

Reference:

<ul><li><a href="http://www.databasejournal.com/features/oracle/article.php/2109681/EXECUTE-IMMEDIATE-option-for-Dynamic-SQL-and-PLSQL.htm" rel="nofollow">EXECUTE IMMEDIATE</a></li> </ul>

EXEC IMMEDIATE 'EXPLAIN PLAN FOR ' || TESTING.TEST