Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, sai.

Asked: July 18, 2018 - 11:26 am UTC

Last updated: July 19, 2018 - 12:49 pm UTC

Version: 10g

Viewed 1000+ times

You Asked

How to pass below variable in dynamic sql?

v_emp_rec org.emp%rowtype;

I am getting ORA-01008: not all variables bound error for below code.

declare
v_sql varchar2(1000);
begin
v_sql:='demo_pkg.demo_rates(p_rec=>v_emp_rec);';

execute immediate v_sql;
end;


and Chris said...

That's because you need to:

- Mark the variable as a bind by placing a colon before it
- Pass the record variable in the using clause of exec immediate
- Upgrade to 12c!

Do all this and it'll work:

create or replace procedure p ( p hr.employees%rowtype ) as
begin
  dbms_output.put_line ( 'Emp id = ' || p.employee_id );
end p;
/

declare
  rec hr.employees%rowtype;
begin

  rec.employee_id := 10;

  execute immediate 'begin p ( :rec ); end;' using rec;
    
end;
/
Emp id = 10


But the real question is:

Why are you using dynamic SQL?!

You should call it normally:

begin

  demo_pkg.demo_rates(p_rec=>v_emp_rec);

end;
/


If for some reason you really, really need dynamic SQL, you can use:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2664470900346124026

Is this answer out of date? If it is, please let us know via a Comment

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