Skip to content

Bind variables instead of concatenating values

Sometimes, the exact SQL statement of a CURSOR is only known at runtime based on the parameters passed by the client. In such cases, the Dynamic SQL comes in handy, to reduce the unwanted complexities of writing static cursors. There are two methods of building the SQL statement of a dynamic cursor. Concatenation and the use of bind variables. If possible, use of bind variables is always recommended.

Consider a table containing Employee information. In this table, the primary key is the Employee_Number and there is another column called mobile_number which is a NOT NULL column which serves the purpose of an alternate key as well. It is necessary to write a function which takes IN parameters, employee_number and mobile_number and return employee information as a record. When employee number is NOT NULL and mobile_number is NULL, then employee_number is used in WHERE clause and WHEN employee_number is NULL and mobile_number is NOT NULL, then mobile_number is used in WHERE clause.

This requirement is going to be met, using dynamic SQL, using both methods. It should be noted that this is a simplified example to highlight the advantages, disadvantages of both methods of writing dynamic SQL statements.

Concatenation

Example

FUNCTION Get_Employee_Info( 
       emp_no_    IN Employee_Tab.Emp_No%TYPE,
       mobile_no_ IN Employee_Tab.Mobile_No%TYPE ) RETURN Employee_Tab%ROWTYPE
IS
       stmt_  VARCHAR2(2000);
BEGIN
       IF (emp_no_ IS NOT NULL) THEN
          stmt_  := 'SELECT * from Employee_Tab e WHERE e.emp_no = '||''''||emp_no_||'''';

          OPEN Emp_Cursor_ FOR stmt_;
          FETCH Emp_Cursor_ INTO employee_rec_;
          CLOSE Emp_Cursor_;

          RETURN employee_rec_;
       ELSIF (mobile_no_ IS NOT NULL) THEN
          stmt_  := 'SELECT * from Employee_Tab e WHERE e.mobile_no = '||''''||mobile_no_||'''';

          OPEN Emp_Cursor_ FOR stmt_;
          FETCH Emp_Cursor_ INTO employee_rec_;
          CLOSE Emp_Cursor_;

          RETURN employee_rec_;
       END IF;
END Get_Employee_Info;

Here, the dynamic SQL statements are built using concatenation using the value of either employee_number or mobile_number. Taking the use of employee_number into consideration, how would the SQL statement look like for two different employee numbers 'E001', 'E002' ?

SELECT * from Employee_Tab e WHERE emp_no = 'E001';

SELECT * from Employee_Tab e WHERE emp_no = 'E002';

For different values of employee_number, the SQL statement becomes different. Oracle SQL engine need to parse each of these SQL statements for different values since each SQL statement is different than the other. This will consume time. When a SQL statement is encountered, SQL engine will parse it once and the parsed statement will be cached. If SQL statement is in the cache, It will be retrieved from cache and used without a re-parse if next encountered SQL statement is identical.

It is apparent that concatenation does not promote the cache and re-use of SQL statements by the SQL Engine.

Another drawback of concatenation is that the concatenated statements may be prone to SQL Injection.

Use of Bind Variables

Example

Same requirement can be met by using Bind Variables. Here the unknown values are indicated by a bind variable and the values for each, will be passed at execution time.

FUNCTION Get_Employee_Info( 
       emp_no_    IN Employee_Tab.Emp_No%TYPE,
       mobile_no_ IN Employee_Tab.Mobile_No%TYPE ) RETURN Employee_Tab%ROWTYPE
IS
       stmt_  VARCHAR2(2000);
BEGIN
       IF (emp_no_ IS NOT NULL) THEN
          stmt_  := 'SELECT * from Employee_Tab e WHERE e.emp_no = :emp_no_'; -- Bind Variable is used

          OPEN Emp_Cursor_ FOR stmt_ USING 'E001'; -- Values are passed to bind variables when the cursor is opened
          FETCH Emp_Cursor_ INTO employee_rec_;
          CLOSE Emp_Cursor_;

          RETURN employee_rec_;
       ELSIF (mobile_no_ IS NOT NULL) THEN
          stmt_  := 'SELECT * from Employee_Tab e WHERE e.mobile_no = :mobile_no_'; -- Bind Variable is used

          OPEN Emp_Cursor_ FOR stmt_ USING '0773542578';
          FETCH Emp_Cursor_ INTO employee_rec_;
          CLOSE Emp_Cursor_;

          RETURN employee_rec_;
       END IF;
END Get_Employee_Info;

SQL Engine will Parse the SQL statement with bind variables once and cache it. For different bind variable values (bound at execution), the already parsed and cached SQL statement will be used. This will be efficient and is the recommended solution.

It has the added advantage of prevention of SQL Injection as well since Oracle will examine the values passed to bind variables.