What is dynamic sql?

Basically Dynamic SQL means creating the SQL statements dynamically at runtime by using variables

Dynamic SQL can be used to create a procedure that operates on a table whose name is not known until runtime or to execute DDL/DCL/SCS statements.

In Pl/SQL such statements cannot be executed statically.
EXECUTE IMMEDIATE  Statement can perform dynamic single row queries.

It enables you to build SQL statements dynamically at runtime.

You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation.

Dynamic SQL describes any SQL DML statement assembled dynamically at runtime as a string and then submitted.

Dynamic SQL is a SQL statement that contains variables that can change during runtime.

It is a SQL statement with place holders and is stored as a character string.

Dynamic SQL enables DDL, DCL or session control statements to be written and executed (by) from PL/SQL.

Examples:

DECLARE
str varchar2(2000);
val number;
BEGIN
str:='Insert into t2 values(:val)';
val:=3;
execute immediate str using val;

END;

DECLARE

TYPE EmpCurTyp IS REF CURSOR;

emp_cv EmpCurTyp;

my_ename VARCHAR2(15);

my_sal NUMBER := 1000;

BEGIN

OPEN emp_cv FOR ’SELECT ename, sal FROM emp WHERE sal > :s’ USING my_sal;

...

END;

Drawbacks of Dynamic SQL

Dynamic SQL that includes user entries in WHERE clauses can be open to SQL injection atacks.

Poorly written dynamic SQL queries often include extra table references and perform poorly.

T-SQL code that generates T-SQL code can be tricky to debug.

Dynamic SQL can be written in two ways

DBMS_SQL

Native Dynamic SQL.