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;

MT0 · Accepted Answer · 2024-09-11 08:18:59Z

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

Collectives™ on Stack Overflow

Execute anonymous block from procedure

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related