Differences

This shows you the differences between two versions of the page.

--- plsql_bind_varialbes [2020/06/04 14:48] – [Hard Parse] andonovj
+++ plsql_bind_varialbes [2020/06/04 15:43] (current) – [Hard Parse] andonovj
@@ Line 13: / Line 13: @@
 It is obvious to notice, that we want always to have no-parse. Unfortunetely that is rarely the case
-=====Parsing=====
+=====Parsing with Bind Variables=====
 Let's get through all of them:
@@ Line 22: / Line 22: @@
 {{ :sql_parse_overview.jpg?500 |}}
+That shared area check, can be seen below. If it succeed, we will have a soft parse, if not, we will have a hard parse and we will continue with the most expensive parts:
+  * Optimization - Generate Multiply Execution Plans
+  * Row Source Generation - Generate a query plan.
+Let's example of a query which will always do a hard parse the first time, given that we change the values each time:
+<Code:bash|Hard Parse Example without Bind variable>
+CREATE OR REPLACE FUNCTION get_count(p_act_id accounts.act_id%TYPE)
+return NUMBER is
+  l_count NUMBER;
+BEGIN
+  SELECT COUNT(*)
+    INTO l_count
+  FROM orders
+ WHERE order_act_id = p_act_id;
+ RETURN l_count;
+END get_count;
+exec get_count(100); <- Hard Parse
+exec get_count(200); <- Hard Parse
+exec get_count(300); <- Hard Parse
+exec get_count(400); <- Hard Parse
+</Code>
+Of course, there are database settings, which we can use, to FORCE the optimized to use its magic, but in general using bind varialbes is always better.
+Here is how this could've have been done using bind varialbes with Native Dynamic SQL:
+<Code:bash|Hard Parse Example with Bind Variables>
+CREATE OR REPLACE FUNCTION get_count(p_column VARCHAR2,
+                                     p_value NUMBER)
+return NUMBER is
+  l_count NUMBER;
+  l_query VARCHAR2(200);
+BEGIN
+ l_query := 'SELECT COUNT(*) FROM orders where '||p_column||' = :col_value';
+ EXECUTE IMMEDIATE l_query into l_count USING p_value;
+                                                 ^
+                                                 |
+                                           Bind Variable
+ RETURN l_count;
+END get_count;
+exec get_count(order_act_id ,100); <- Hard Parse
+exec get_count(order_act_id ,200); <- Soft Parse
+exec get_count(order_act_id ,300); <- Soft Parse
+exec get_count(order_act_id ,400); <- Soft Parse
+</Code>
+Of course there is a different ways, using DBMS_SQL or Native Dynamic SQL, whatever you choose, some gives you more control, some not. However USE BIND VARIABLES IN EVERY CASE.
@@ Line 78: / Line 130: @@
 dbms_sql.close_cursor(m_cursor);
 </Code>