How to Use Bind Variable in Oracle

  • by

Shareable Cursor

Using bind variables can make statements with minor differences become literally the same so as to share the same cursor when executions. That means, SQL engine doesn’t have to parse those similar statements every time they are executed, it reuses the shared cursor to service millions of executions.

Unshareable Cursors

In contrast, dynamic and unshareable SQL statements with tiny differences are regarded as different ones. That makes SQL engine keep parsing statements and finding new private SQL areas to be located. Eventually, countless cursors with similar statements exhausts your shared pool. Moreover, you might see errors like ORA-04031: unable to allocate 4216 bytes of shared memory.

Although we can save this kind of SQL statements by setting CURSOR_SHARING to FORCE, using static and bind variables is always a good thing from performance and security perspectives. Beside, Oracle has warned that Do Not Use CURSOR_SHARING = FORCE as a Permanent Fix.

Use Bind Variable in SQL*Plus

We can use it directly in SQL*Plus. For the following example, we don’t have to recompose the statement, instead we change the bind variable.

SQL> variable LNAME varchar2(25);
SQL> exec :LNAME := 'Chen'

PL/SQL procedure successfully completed.

SQL> select first_name from employees where last_name = :LNAME;

FIRST_NAME
--------------------
Ed

SQL> exec :LNAME := 'Sullivan';

PL/SQL procedure successfully completed.

SQL> select first_name from employees where last_name = :LNAME;

FIRST_NAME
--------------------
Martha

As you can see, we repeatedly use literally the same statement to service all executions.

SQL> select sql_id from v$sql where sql_text like 'select first_name from employees where last_name = :LNAME';

SQL_ID
-------------
1rwyzpf20abf0
1rwyzpf20abf0

Use Bind Variable in PL/SQL

The following is an anonymous PL/SQL block. In which, a query statement binds a variable :1 that can easily be changed in value for further executions. Furthermore, the code looks neat and understandable, not to mention the performance gain if we use it in a loop.

SQL> set serveroutput on;
SQL> declare
  2  v_firstname varchar2(25);
  3  v_lastname varchar2(25);
  4  v_sql varchar2(250) := 'select first_name from employees where last_name = :1';
  5  begin
  6  v_lastname := 'Chen';
  7  execute immediate v_sql into v_firstname using v_lastname;
  8  dbms_output.put_line('The person whose last name is ' || v_lastname || ' is called ' || v_firstname || '.');
  9  v_lastname := 'Sullivan';
 10  execute immediate v_sql into v_firstname using v_lastname;
 11  dbms_output.put_line('The person whose last name is ' || v_lastname || ' is called ' || v_firstname || '.');
 12  end;
 13  /
The person whose last name is Chen is called Ed.
The person whose last name is Sullivan is called Martha.

PL/SQL procedure successfully completed.

Nevertheless, human readiness is also an important factor that can evaluate the quality of PL/SQL code, not just only performance is concerned.

Further reading: How to Find out Badly Written Statements for Tuning.

Leave a Reply

Your email address will not be published. Required fields are marked *