Database Reference
In-Depth Information
BINDS Entry Format
The minimum SQL trace level for enabling
BINDS
entries is 4. The structure of a
BINDS
entry
consists of the word
BINDS
followed by the cursor number and a separate subsection (
bind
n
or
BIND #
n
) for each bind variable.
BINDS #m:
<subsection 0>
<details of bind variable 0>
…
<subsection n>
<details of bind variable n>
Bind variables are numbered from left to right within the statement text starting at zero.
When associating bind variables with subsections, do not pay attention to numerals, which
may be included in the name of a bind variable (e.g.,
:B1
). As you will see in the examples that
follow, the bind variable name
:B<
n
+1>
may appear before
:B
n
, where
n
is an integer. This is
indeed confusing. The correct association is formed by reading the statement text from left to
right and top to bottom. Subsection 0 provides details on the first bind variable thus encountered,
subsection 1 on the second, and so on.
Bind in Oracle9
i
The following anonymous PL/SQL block will serve as an example for examining
BINDS
entries:
DECLARE
sal number(8,2):=4999.99;
dname varchar2(64):='Shipping';
hired date:=to_date('31.12.1995','dd.mm.yyyy');
BEGIN
FOR emp_rec IN ( SELECT e.last_name, e.first_name, e.salary, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id=d.department_id
AND e.salary > sal
AND e.hire_date > hired
AND d.department_name=dname) LOOP
null;
END LOOP;
END;
/
Tracing this block at SQL trace level 4 or 12 yields the trace file entries including a
BINDS
section as shown here:
PARSING IN CURSOR #2 len=209 dep=1 uid=0 oct=3 lid=0 tim=118435024029 hv=3341549851
ad='19cf2638'
SELECT e.last_name, e.first_name, e.salary, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id=d.department_id
AND e.salary > :b3
AND e.hire_date > :b2
