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
;
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