Execute anonymous block from procedure

Question

I have a table which consist of anonymous block in the column rule, which needs to be executed from procedure.

Here is the sample data which looks like the real scenario.

Table emp:

create table emp (id int,name varchar2(20));

Data:

insert into emp values(1,'Jack');
insert into emp values(2,'John');
insert into emp values(3,'Jams');

Table ano:

create table ano(id int,rule varchar2(4000));

Data:

insert into ano values(1,'declare v_cnt number; 
begin 
select count(1) into v_cnt from emp where id = :1; 
if(v_cnt>1) then :2 := ''true''; 
else :2 := ''false''; 
end if; 
end;');

Procedure:

create procedure pr_ano(p_emp emp) 
IS
DECLARE v_out boolean;
BEGIN
    execute immediate p_emp.rule using 1,v_out;
    dbms_output.put_line(v_out);
END;

Procedure call:

pr_ano(emp);

Getting an error Unkown Command;

Answer 1

This is a bad idea as you open your database to SQL injection attacks.

If you are going to use this (don't) then do not let users supply the rules in the ano table and ensure all the rules are carefully sanitised so they are not malicious.

---

You want something like:

create table emp (id int,name varchar2(20));

insert into emp values(1,'Jack');
insert into emp values(2,'John');
insert into emp values(3,'Jams');

create table ano(id int,rule varchar2(4000));

insert into ano values(
1,
'declare
  v_cnt number; 
begin 
  select count(1) into v_cnt from emp where id = :1; 
  if(v_cnt>1)
  then :2 := ''true''; 
  else :2 := ''false''; 
  end if; 
end;'
);

Then the procedure:

create procedure pr_ano(
  i_id ANO.ID%TYPE
) 
IS
  v_rule ANO.RULE%TYPE;
  v_out  VARCHAR2(5);
BEGIN
  SELECT rule INTO v_rule FROM ano WHERE id = i_id;
  EXECUTE IMMEDIATE v_rule USING 1, OUT v_out;
  DBMS_OUTPUT.PUT_LINE(v_out);
END;
/

Then you can call it using:

BEGIN
  DBMS_OUTPUT.ENABLE();
  pr_ano(1);
END;
/

Which outputs:

false

fiddle