Note: Doing SQL from PL/SQL: Best and Worst Practices

A must read document for all Oracle PL/SQL developers.
1.
PL/SQL supports three methods for issuing SQL: Embedded SQL, native dynamic sql and dbms_sql api.
alter session is not supported by embedded sql. So use execute immediated 'alter session ...'

2. Use the constant keyword in the declaration of any variable that is not changed after its initialization.
(reduces the surface area of attack for SQL injection)

3. Always specify the authid property explicitly in every PL/SQL unit. definer's or invoker's
procedure p(Input in varchar2) authid Current_User is...

4. To avoid sql injection
Stmt constant varchar2(200) := Replace(
 Template, '&&t',
 Sys.DBMS_Assert.Simple_Sql_Name(The_Table));

5. When the sql statement text cannot be fixed at compile time, strive to sue a fixed syntax template and limit the run-

time variation to the provision of names.

6. Native dynamic sql was introduced as an improvement on the dbms_sql API (it is easier to write and executes faster):
open_cursor(), parse(), define_column(), bind_variable(), execute_and_fetch() and close_cursor()

7. when a PL/SQL subprogram executes a
SQL statement there is at least one, and possibly many, so-called context
switches from the PL/SQL virtual machine, to the SQL virtual machine, and
back to the PL/SQL virtual machine

8.
When many rows are to be selected, and the result set may be arbitrarily big,
process them in batches by using fetch... bulk collect into with the limit clause
inside an infinite loop.

9.
When you have an “upsert” requirement, use merge rather than implementing
update... set row... and providing an exception handler for Dup_Val_On_Index
that implements the corresponding insert.