Why use dynamic SQL in Oracle? I don't understand the difference between the two ways of writing

Question

sql_1 := 'select count(1)
                 from table_1 a
                 where a.col_id = '''|| v_1 ||''' 
                 and a.col2 like ''%'|| v_2 ||'';
execute immediate sql_1
        into v_new;
-----------------------------------------------      
select count(1)
        into v_new
from table_1 a
where a.col_id = '''|| v_1 ||''' 
and a.col2 like ''%'|| v_2 ||'';

I think the effect of the two methods is the same in a procedure. Why is the first one better?

Answer 1

why is the first one better please

Dynamic SQL is not better as, among other reasons, the syntax errors, etc. will be raised at run-time and not compile time so you cannot debug issues as easily.

If you do not need dynamic SQL then do not use it:

select count(1)
into   v_new
from   table_1 a
where  a.col_id = v_1 
and    a.col2  like '%'|| v_2 

If you must use dynamic SQL (don't) then use bind variables (to prevent the SQL engine from having to re-parse the statement when you change a variable and to avoid SQL injection issues):

sql_1 := 'select count(1)
          from   table_1 a
          where  a.col_id = :1
          and    a.col2 like ''%'' || :2';

EXECUTE IMMEDIATE sql_1 INTO v_new USING v_1, v_2;

However, you should reserve dynamic SQL for when you must dynamically specify identifiers (rather than values) and, even then, it should be a prompt to step back and review what you are doing and whether you can change your approach to avoid using dynamic SQL.