Useful shortcuts for vi editor

Thursday, 8 June 2017

e.g. bind variable usage

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;
/