Using bind variables and parsing

We have already discussed bind variables and parsing in the Using bind variables recipe in Chapter 4, Optimizing SQL Code. In this recipe, we will see another example, using the same principles applied to a PL/SQL procedure.

How to do it...

The following steps will demonstrate the bind variables using PL/SQL:

1. Connect to the database as user SH:

   CONNECT sh@TESTDB/sh
   

2. Create a function to calculate the maximum length of data stored in an arbitrary field with a variable condition on another field:

   CREATE FUNCTION CONDITIONAL_COLUMN_LEN(TABLE_NAME IN VARCHAR2,
    COLUMN_NAME IN VARCHAR2, COND_FIELD IN VARCHAR2,
    COND_VALUE IN VARCHAR2) RETURN NUMBER
   IS
     L_RESULT NUMBER := 0;
     L_STMT VARCHAR2(2000);
   BEGIN
     L_STMT := 'SELECT MAX(LENGTH(' || COLUMN_NAME ||
      ')) FROM ' || TABLE_NAME ||
      ' WHERE ' || COND_FIELD || ' = ' || COND_VALUE;
     EXECUTE IMMEDIATE L_STMT INTO L_RESULT;
     RETURN L_RESULT;
   END;
   /
   

3. Calculate using the function created in the previous step for...