Dynamic SQL with Execute Immediate

Most of the SQL statements you will write in PL/SQL are static, which means that they are parsed when you compile the block in which you wrote the SQL statements. But sometimes you don’t have all the information needed at compile time to parse your SQL statements. Perhaps the name of a column or a WHERE clause is constructed only when the program is executed. In this case, you must write dynamic SQL.

It’s very easy to write static SQL in PL/SQL program units, and that’s one of the best things about PL/SQL. It’s also quite easy to implement dynamic SQL requirements in PL/SQL

example to add a record 

 create or  replace procedure add_row
 (p_table_name varchar2, 
  p_id number,
  p_name varchar2,
  p_loc varchar2) is
begin
execute immediate 'insert into '|| p_table_name || ' values
 (:1, :2, :3)' using p_id, p_name, p_loc;
end;

example to drop a table 

DECLARE

    ddl_qry     VARCHAR2 (100);

BEGIN

    ddl_qry := 'DROP TABLE tut_83';

    EXECUTE IMMEDIATE ddl_qry;

END;