bind variable;
When user executes an SQL query or a PL/SQL code, Oracle uses CPU resource for parsing operations on the SGA. However, if user execute the query with bind variable, "soft parsing" exists instead of "hard parsing". Therefore, "total DB time" and "parsing time" decreases, library cache size is not occupied unnecessarily as well.
e.g.
SET TIMING ON;
VARIABLE v_bind_value NUMBER;
DECLARE
v_value VARCHAR2 (30);
BEGIN
:v_bind_value := 100;
SELECT last_name
INTO v_value
FROM employees
WHERE employee_id = :v_bind_value;
DBMS_OUTPUT.put_line (v_value);
END;
/
When user executes an SQL query or a PL/SQL code, Oracle uses CPU resource for parsing operations on the SGA. However, if user execute the query with bind variable, "soft parsing" exists instead of "hard parsing". Therefore, "total DB time" and "parsing time" decreases, library cache size is not occupied unnecessarily as well.
e.g.
SET TIMING ON;
VARIABLE v_bind_value NUMBER;
DECLARE
v_value VARCHAR2 (30);
BEGIN
:v_bind_value := 100;
SELECT last_name
INTO v_value
FROM employees
WHERE employee_id = :v_bind_value;
DBMS_OUTPUT.put_line (v_value);
END;
/