BIND Vs LEXICAL

BIND VARIABLE :
-- are used to replace a single value in sql, pl/sql
-- bind variable may be used to replace expressions in select, where, group, order
by, having, connect by, start with cause of queries.
-- bind reference may not be referenced in FROM clause (or) in place of
reserved words or clauses.
LEXICAL REFERENCE:
-- you can use lexical reference to replace the clauses appearing AFTER select,
from, group by, having, connect by, start with.
-- you can’t make lexical reference in a pl/sql statmetns.

Flex mode and Confine mode

Confine mode
On: child objects cannot be moved outside their enclosing parent objects.
Off: child objects can be moved outside their enclosing parent objects.

Flex mode:
On: parent borders "stretch" when child objects are moved against them.
Off: parent borders remain fixed when child objects are moved against
them.


Diff Between Implicit and Explicit Cursors
1) Implicit: declared for all DML and pl/sql statements.
By default it selects one row only.

2) Explicit: Declared and named by the programmer.Use explicit cursor to individually process each row returned by a Multiple statements, is called ACTIVE SET. Allows the programmer to manually control explicit cursor in the
Pl/sql block

Execution Sequences of sql clauses.
a)Select…..
b)Group by…
c)Having…
d)Orderby..