Native Dynamic SQL

1.Dynamic SQL with DML Statements

●Deleting rows from any table:

create function del_rows(table_name varchar2)
return number is
begin
  execute immediate 'delete from '||table_name;
  return sql%rowcount;
end;

begin
  dbms_output.put_line(
         del_rows('employee_names')||' rows deleted.');
end;

●Inserting a row into a table with two columns:

create procedure add_row(
       table_name varchar2,
       id number,
       name varchar2) is
begin
  execute immediate 'insert into '||table_name|| 
                    'values (:1,:2)' using id,name;
end;

2.Dynamic SQL with a Single-Row Query

create or replace function get_emp(emp_id number)
return employees%rowtype is
stmt varchar2(200);
emprec employees%rowtype;
begin
  stmt:='select * from employees '||'where employee_id=:id';
  execute immediate stmt into emprec using emp_id;
  return emprec;
end;
------------------------------------------------------------
declare
  emprec employees%rowtype := get_emp(100);
begin
  dbms_output.put_line('emp:'||emprec.last_name);
end;

3.Dynamic SQL with a Multirow Query

create or replace procedure list_employees(deptid number) is
  type emp_refcsr is ref cursor;
  emp_cv emp_refcsr;
  emprec employees%rowtype;
  stmt varchar2(200) := 'select * from employees';
begin
  if deptid is null then 
     open emp_cv for stmt;
  else
     stmt:=stmt ||' where department_id = :id';
  open emp_cv for stmt using deptid;
  end if;
  loop
    fetch emp_cv into emprec;
    exit when emp_cv%notfound;
    dbms_output.put_line(emprec.department_id ||''||emprec.last_name);
  end loop;
  close emp_cv;
end;

4.Dynamically Executing a PL/SQL Block

create function annual_sal(emp_id number)
return number is
       plsql varchar2(200) :=
             'DECLARE '||
             ' emprec employees%ROWTYPE; '||
             'BEGIN '||
             ' emprec := get_emp(:empid); '||
             ' :res := emprec.salary * 12; '||
             'END;';
       result NUMBER;
BEGIN
  EXECUTE IMMEDIATE plsql USING in emp_id,out result;
  RETURN result;
END;

5.Using Native Dynamic SQL to Compile PL/SQL Code

create or procedure complie_plsql(
       name varchar2,
       plsql_type varchar2,
       options varchar2:=null) is
   stmt varchar2(200):='alter '||plsql_type||''||name||' compile ';
begin
  if options is not null then
    stmt := stmt||''||options;
  end if;
  execute immediate stmt;
end;