executing select statement stored in table column by replacing variable value dynamically

Question

I have simple PL/SQL block with below code

declare 
rule1 varchar2(100 char);
begin
for i in (select table_name from all_tables where table_owner='EqEDI') loop
  execute immediate 'select rule_stmt from rulebook ' into rule1 ;
  execute immediate rule1 into result;
  dbms_output.put_line('Result is '||result);
end loop;
end;

the rule statement stored in table rulebook is :

"'select count(1) from '|| <tablename>"

I want this above statement to be executed for all tables present for given owner but while executing, it does not replace in query with actual tables. How can I achieve it with simple PL/SQL block.

Answer 1

rulebook table's contents is kind of wrong. Not that you can NOT do it the way you stored select statement into it - it is just impractical as you have to remove single quotes, remove tablename (as you can't even bind it, but concatenate what cursor returned) ... too much unnecessary jobs to be done.

Also, check all_tables and names of its columns - there's no table_owner, just owner.

Therefore, I'd suggest you to store such a statement:

SQL> SELECT * FROM rulebook;

RULE_STMT
--------------------------------------------------------------------------------
select count(*) from

Fix your PL/SQL script:

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     rule1   VARCHAR2 (100 CHAR);
  3     l_str   VARCHAR2 (100);
  4     result  NUMBER;
  5  BEGIN
  6     FOR i IN (SELECT table_name
  7                 FROM all_tables
  8                WHERE     owner = 'SCOTT'
  9                      AND table_name = 'EMP')
 10     LOOP
 11        EXECUTE IMMEDIATE 'select rule_stmt from rulebook '
 12           INTO rule1;
 13
 14        l_str := rule1 || i.table_name;
 15
 16        EXECUTE IMMEDIATE l_str
 17           INTO result;
 18
 19        DBMS_OUTPUT.put_line ('Result is ' || result);
 20     END LOOP;
 21  END;
 22  /
Result is 14

PL/SQL procedure successfully completed.

SQL>