Oracle Database - SQL/PLSQL/SQL Plus - Bind Variable

> Procedural Languages > (SQL Plus|SqlCl) (Oracle Database Console)

1 - About

Bind variables are variables you create in SQL*Plus (or any other API such as jbdc) and then reference in:

  • PL/SQL
  • or SQL.

With bind variable, you can't substitute database object names (tables, views, columns etc), you can only subsitute literals. If the object name is generated at runtime, you'll need to generate the string of the SQL or PLSQL command.

Two binding methods:

  • named binding
  • positional binding

At run time, bind arguments replace corresponding placeholders in the dynamic string.

Advertising

3 - How to

3.1 - Create Bind Variables

You create bind variables in SQL*Plus with the VARIABLE command.

VARIABLE myVariable VARCHAR2(30)

3.2 - List all bind variables

You list all bind variables in SQL*Plus by typing the VARIABLE command without arguments.

VARIABLE
variable   ret_val
datatype   NUMBER

variable   myvariable
datatype   VARCHAR2(30)

3.3 - Set the value of a bind variable

You reference bind variables in PL/SQL by typing a colon (:) followed immediately by the name of the variable.

To change a bind variable in SQL*Plus, you must enter a PL/SQL block. For example:

BEGIN
 :myVariable:='Nico';
END;
/

3.4 - List the values of bind variables

You get the values of bind variables with the PRINT command.

PRINT
   RET_VAL
----------
         4


MYVARIABLE
----------
Nico
Advertising

3.5 - Refer to a bind variable

You reference bind variables by typing a colon (:) followed immediately by the name of the variable.

[email protected]>SELECT :myVariable FROM dual;
:MYVARIABLE
---------------
Nico

The name of a table cannot be a bind variable

4 - Improving Performance of Dynamic SQL with Bind Variables

When you code INSERT, UPDATE, DELETE, and SELECT statements directly in PL/SQL, PL/SQL turns the variables into bind variables automatically, to make the statements work efficiently with SQL. When you build up such statements in dynamic SQL, you need to specify the bind variables yourself to get the same performance.

Why ?
Because if you don't and you add more user to your application, you add more contention; more contention means more waiting; more waiting means slower response times. When you add users, the systme spent most of its time keeping htem in line, waiting to parse SQL in the shared pool. Add amount of additionnal CPU would help them.

In the example below, Oracle opens a different cursor for each distinct value of emp_id. This can lead to resource contention and poor performance as each statement is parsed and cached.

CREATE PROCEDURE fire_employee (emp_id NUMBER) AS
BEGIN
   EXECUTE IMMEDIATE
      'DELETE FROM emp WHERE empno = ' || TO_CHAR(emp_id);
END;
/

You can improve performance by using a bind variable, which allows Oracle to reuse the same cursor for different values of emp_id:

CREATE PROCEDURE fire_employee (emp_id NUMBER) AS
BEGIN
   EXECUTE IMMEDIATE
      'DELETE FROM emp WHERE empno = :num' USING emp_id;
END;
/

5 - Call Sql, Plsql

5.1 - Sql

In the below statement, you are NOT USING BIND VARIABLES, for example I clearly see:

INSERT INTO gfx_suggestion (suggestion, suggestion_id, TIMESTAMP, 
suggestion_type_fl,
name, email, business_unit_key) SELECT 'test suggestion. RJ 04/19/01', 
MAX(suggestion_id)+1, sysdate, 'T', 'Ron Jennings' , '[email protected]', '5' FROM 
gf_suggestion

that MUST BE rewritten as:

INSERT INTO gfx_suggestion (suggestion, suggestion_id, TIMESTAMP, 
suggestion_type_fl,
name, email, business_unit_key) SELECT :1, MAX(suggestion_id)+1, sysdate, :2, :3, :4, :5 
FROM gf_suggestion

Or you will not go ANYWHERE with this application. Bind variables are SO MASSIVELY important – I cannot in any way shape or form OVERSTATE their importance.

Advertising

5.2 - Plsql

Same with the PLSQL call I see there:

BEGIN :1 := gfx.insrt_coach('Ron
Jennings',5,'04172001','','','','');  END;

That MUST be coded as:

BEGIN :1 := gfx.insrt_coach(:2,:3,:4,:5,:6,:7,:8); 

If you do not fix this, your application is doomed to utter and total failure from day one.

Java supports bind variables, your developers must start using prepared statements and bind inputs into it. If you want your system to ultimately scale beyond say about 3 or 4 users – you will do this right now (fix the code). It is not something to think about, it is something you MUST do. A side effect of this - your shared pool problems will pretty much disappear. That is the root cause.

If I was to write a book on how to build 'non scalable applications in Oracle', this would be the first and last chapter. This is a major cause of performance issues and a major inhibitor of scalability in Oracle. The way the Oracle shared pool (a very important shared memory data structure) operates is predicated on developers using bind variables. If you want to make Oracle run slowly, even grind to a total halt just refuse to use them.

5.2.1 - Null Bind Variable in Dynamic Select Statement

The documentation said that you cannot set a bind variable with a Boolean literals (TRUE, FALSE, and NULL) but thanks to query transformation (Oracle-base), you can do it with the 1=1 trick.

l_sql := 'SELECT COUNT(*) INTO :l_number FROM emp WHERE 1=1 ';
 
IF p_job IS NOT NULL THEN
l_sql := l_sql || 'AND job = :job ';
ELSE
-- At parse time, the query optimizer sees that "1=1" is always true, therefore this whole OR expression must always equate to true, so it throws the predicate away. 
l_sql := l_sql || 'AND ((1=1) OR :job IS NULL) ';
END IF;
 
DBMS_OUTPUT.PUT_LINE(l_sql);
 
EXECUTE IMMEDIATE l_sql INTO l_number USING p_job;

6 - Example

For those that do not know, a bind variable is a placeholder in a query. For example, to retrieve the record for employee 1234, I can either query:

SELECT * FROM EMP WHERE EMPNO = 1234;

Or I can query:

SELECT * FROM EMP WHERE EMPNO = :empno;

And supply the value for :empno at query execution time.

The difference between the two is huge, dramatic even. In a typical system, you would query up employee 1234 maybe once and then never again. Later, you would query up employee 456, then 789 and so on. If you use literal (constants) in the query each and every query is a brand new query, never before seen by the database. It will have to be parsed, qualified (names resolved), security checked, optimized and so on. In short, it will be compiled. Every unique statement you execute will have to be compiled every time. This would be like shipping your customers Java source code and before calling a method in a class you would invoke the Java compiler, compile the class, run the method and then throw away the byte code. The next time you wanted to execute the same exact method, you would do the same thing compile it, run it and throw it away. Executing SQL statements without bind variables is very much the same thing as compiling a subroutine before each and every call. You would never consider doing that in your application you should never consider doing that to your database either.

Not only will parsing a statement like that (also called a HARD parse) consume many more resources and time then reusing an already parsed query plan found in the shared pool (called a SOFT parse), it will limit your scalability. We can see it will obviously take longer, what is not obvious is that it will reduce the amount of users your system can support. This is due in part to the increased resource consumption but mainly to the latching mechanisms for the library cache where these plans are stored after they are compiled. When you hard parse a query, we will spend more time holding certain low level serialization devices called latches. These latches protect the data structures in the shared memory of Oracle from concurrent modifications by two sessions (else Oracle would end up with corrupt data structures) and from someone reading a data structure while it is being modified. The longer and more often we have to latch these data structures, the longer the queue to get these latches will become. Similar to the MTS architecture issue described above, we will start to monopolize scarce resources. Your machine may appear to be underutilized at times  but yet everyone in the database is running very slowly. This will be because someone is holding one of these serialization mechanisms and a line is forming. You are not able to run at top speed.

The second query above on the other hand, the one with :empno, is compiled once and stored in the shared pool (the library cache). Everyone who submits the same exact query that references the same object will use that compiled plan (the SOFT parse). You will compile your subroutine once and use it over and over again. This is very efficient and the way the database is intending you will do your work. Not only will you use less resources (a SOFT parse is much less resource intensive), but you will hold latches for less time and need them less frequently. This increases your performance and greatly increases your scalability.

Just to give you a tiny idea of how huge of a difference this can make performance wise, you only need to run a very small test:

tkyte@TKYTE816> ALTER system flush shared_pool;
System altered.
 
tkyte@TKYTE816> DECLARE
  2      TYPE rc IS REF CURSOR;
  3      l_rc rc;
  4      l_dummy all_objects.object_name%TYPE;
  5      l_start NUMBER DEFAULT DBMS_UTILITY.get_time;
  6  BEGIN
  7      FOR i IN 1 .. 1000
  8      LOOP
  9          OPEN l_rc FOR
 10          'select object_name
 11             from all_objects
 12            where object_id = ' || i;
 13          FETCH l_rc INTO l_dummy;
 14          CLOSE l_rc;
 15      END LOOP;
 16      DBMS_OUTPUT.put_line
 17      ( ROUND( (DBMS_UTILITY.get_time-l_start)/100, 2 ) ||
 18        ' seconds...' );
 19  END;
 20  /
14.86 seconds...
 
PL/SQL PROCEDURE successfully completed.
 
tkyte@TKYTE816> DECLARE
  2      TYPE rc IS REF CURSOR;
  3      l_rc rc;
  4      l_dummy all_objects.object_name%TYPE;
  5      l_start NUMBER DEFAULT DBMS_UTILITY.get_time;
  6  BEGIN
  7      FOR i IN 1 .. 1000
  8      LOOP
  9          OPEN l_rc FOR
 10          'select object_name
 11             from all_objects
 12            where object_id = :x'
 13          using i;
 14          FETCH l_rc INTO l_dummy;
 15          CLOSE l_rc;
 16      END LOOP;
 17      DBMS_OUTPUT.put_line
 18      ( ROUND( (DBMS_UTILITY.get_time-l_start)/100, 2 ) ||
 19        ' seconds...' );
 20  END;
 21  /
1.27 seconds...
 
PL/SQL PROCEDURE successfully completed.

That is pretty dramatic. The fact is that not only does this execute much faster (we spent more time PARSING our queries then actually EXECUTING them!) it will let more users use your system simultaneously.

7 - Configuration

7.1 - AUTOPRINT

See the autoprint system variable

SET AUTOP[RINT] {ON | OFF} 

8 - Support

8.1 - ORA-1747 - invalid user.table.column, table.column, or column specification

With bind variable, you can't substitute database object names (tables, views, columns etc), you can only subsitute literals. If the object name is generated at runtime, you'll need to generate the string of the SQL or PLSQL command.

9 - Reference

lang/sqlplus/bind_variable.txt · Last modified: 2017/09/28 15:01 by gerardnico