Execute the SQL statements from table within an Oracle SQL procedure

Question

I'm a newbie in PL/SQL and I'm struggling with following issue. I'm looking for an answer for 4 hours and it's still not working...

I've got above 300 records in a table with SQL statements and my goal is to write a procedure that is able to loop over them under some conditions. I'd like also to check which queries passed and which ones failed.

So it goes like this (list of steps/pseudocode):

• procedure or function that takes a condition as a parameter
• iterates over records that match a condition
• executes SQL statements which fit match
• gives an information of success or failure of execution

I was trying to do something like this (below) but I think it's not correct - because I'm not looping over records.

DECLARE
   sql_stmt    VARCHAR2(3000);

BEGIN
   sql_stmt := 'SELECT testcase_sql
                FROM data_audit_testcase_v2
                WHERE testcase_desc LIKE ''Test 6 - EM%''';
   dbms_output.put_line('Sth: ' || sql_stmt);
   EXECUTE IMMEDIATE sql_stmt ;
END;

I'm thinking about adjusting this code to my issue:

FOR q IN (SELECT sql_text FROM query_table)
LOOP
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM (' || q.sql_text || ')'
     INTO some_local_variable;
  <<do something with the local variable>>
END LOOP;

source here

What do you guys think?

Thank you for your help in advance,

Arthur

Answer 1

You have to do it like this:

DECLARE
   sql_stmt    VARCHAR2(3000);
    cur SYS_REFCURSOR;
    some_local_variable data_audit_testcase_v2.testcase_sql%TYPE;
BEGIN
   sql_stmt := 'SELECT testcase_sql
                FROM data_audit_testcase_v2
                WHERE testcase_desc LIKE :val';
   DBMS_OUTPUT.PUT_LINE('Sth: ' || sql_stmt);
    OPEN cur FOR sql_stmt USING 'Test 6 - EM%';
    LOOP
        FETCH cur INTO some_local_variable;     
        EXIT WHEN cur%NOTFOUND;
        <<do something with the local variable>>
    END LOOP;
    CLOSE cur;
END;