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