Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ricardo.

Asked: June 10, 2021 - 10:30 am UTC

Last updated: June 11, 2021 - 5:06 pm UTC

Version: 11g

Viewed 100+ times

You Asked

Hi guys.

I have a piece of code like this:

declare 
  myvar number := 4;     
begin
 select rule into v_stmt from rules where rule_id=1;
 --v_stmt -> 'begin :a := my_function(my_var, 1), 1); end;'
 execute immediate v_stmt;
end;
/


Is there a way to inject my_var value to execute v_stmt?

and we said...

You want to bind it!

Do this by:

- Adding colon before the variable in the statement to make it a bind variable
- Supply it's value with the using clause

For example:
set serveroutput on
declare 
  myvar number := 4;     
begin
 execute immediate 
   'begin dbms_output.put_line ( :myvar ); end;'
   using myvar;
end;
/
4

Rating

  (1 rating)

Comments

So simple. Thank you

Ricardo Oberdan Pereira, June 11, 2021 - 12:00 pm UTC

So simple.
Thanks Chris.
You're amazing guys!
Chris Saxon
June 11, 2021 - 5:06 pm UTC

You're welcome

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library