Databases Reference
In-Depth Information
Implementing the previously mentioned functions using bind variables would be done
as follows:
PROCEDURE TEST_INJECTION(
NAME IN sh.customers.cust_last_name%TYPE) IS
BEGIN
DECLARE
l_stmt VARCHAR2(2000);
res NUMBER;
BEGIN
l_stmt := 'SELECT COUNT(*) FROM sh.customers s WHERE
s.cust_last_name = :p_name';
EXECUTE IMMEDIATE l_stmt INTO res USING NAME;
DBMS_OUTPUT.PUT_LINE('Count: ' || TO_CHAR(res));
END;
END TEST_INJECTION;
PROCEDURE TEST_INJECTION2(
NAME IN sh.customers.cust_last_name%TYPE) IS
BEGIN
DECLARE
l_stmt VARCHAR2(2000);
BEGIN
l_stmt := 'BEGIN DBMS_OUTPUT.PUT_LINE (''You passed '' ||
:p_name); END;';
EXECUTE IMMEDIATE l_stmt USING NAME;
END;
END TEST_INJECTION2;
See also
F
We will see more about latches in the recipe
Minimizing latches using bind
variables
in
Chapter 11
,
Tuning Contention
F
To know more about dynamic SQL refer to
Avoiding dynamic SQL
in
Chapter 2
,
Optimizing Application Design
and
Introduce Adaptive Cursor Sharing for bind
variable peeking
in
Chapter 7
,
Improving the Oracle Optimizer
