oracle-databaseanonymous-functionoracle21c

Execute anonymous block from procedure


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;


Solution

  • 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