How you can use a bind variable in SQLPlus

Card Puncher Data Processing

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

About

This page is about bind variable or sql parameter in SQL plus.

Bind variable'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.

How to

Create Bind Variables

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

VARIABLE myVariable VARCHAR2(30)

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)

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;
/

List the values of bind variables

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

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


MYVARIABLE
----------
Nico

Refer to a bind variable

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

sys@orcl>select :myVariable from dual;
:MYVARIABLE
---------------
Nico

The name of a table cannot be a bind variable

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;
/

Call Sql, Plsql

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.

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.

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;

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:

alter system flush shared_pool;
System altered.

declare
      type rc is ref cursor;
      l_rc rc;
      l_dummy all_objects.object_name%type;
      l_start number default dbms_utility.get_time;
begin
  for i in 1 .. 1000
  loop
	  open l_rc for
	  'select object_name
		 from all_objects
		where object_id = ' || i;
	  fetch l_rc into l_dummy;
	  close l_rc;
  end loop;
  dbms_output.put_line
  ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
	' seconds...' );
end;
  /
14.86 seconds...
PL/SQL procedure successfully completed.

declare
  type rc is ref cursor;
  l_rc rc;
  l_dummy all_objects.object_name%type;
  l_start number default dbms_utility.get_time;
begin
  for i in 1 .. 1000
  loop
	  open l_rc for
	  'select object_name
		 from all_objects
		where object_id = :x'
	  using i;
	  fetch l_rc into l_dummy;
	  close l_rc;
  end loop;
  dbms_output.put_line
  ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
	' seconds...' );
end;
/
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.

Bind variable and the IN operator

PL/SQL - Bind Variable and In Comparison Operators

Configuration

AUTOPRINT

See the autoprint system variable

SET AUTOP[RINT] {ON | OFF} 

Support

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.

Reference





Discover More
Jdbc Class Architecture
JDBC - Oracle

Definition of the JDBC Oracle API or how to connect to a JDBC oracle and made database operations. A simple application includes the following classes: DataHandler.java. This class contains all...
Jpa Mapping Method
JPA - Entity Annotations

A key feature of EJB 3.0 and JPA is the ability to create entities that contain object-relational mappings by using (metadata) annotations rather than deployment descriptors (orm.xml) as in earlier versions....
Bi Server Architecture With Client
OBIEE 10G/11G - Parameters (parameterized query)

A parameter is a variable whose value can be set at runtime. Parameters are especially useful for modifying SELECT statements and setting PL/SQL variables at runtime. In Oracle Database term, you talk...
Card Puncher Data Processing
Oracle Database

Documentation about the Oracle database
Card Puncher Data Processing
Oracle Database - Runstats

Runstats is a tool that have developed to compare two different methods of doing the same thing and show which one is superior. You supply the two different methods and runstats does the rest. Runstats...
Oracle Database Star Transformation
Oracle Database - Star Transformation

This article talk the application of the star query in Oracle which involve a star schema. Star transformation executes the query in two phases: retrieves the necessary rows from the fact table (row...
Card Puncher Data Processing
PL/SQL - (Procedure Language|PL) SQL

PlSql is the development language of the oracle database. SQL was designed from the start to operate on SETS (ie parallel task) whereas PL/SQL brought a lot in terms of exception handling. PL/SQL...
Card Puncher Data Processing
PL/SQL - Bind Variable and In Comparison Operators

With a bind variable, the value is passed atomically. If you pass the value 1, 2 in a IN comparison operator, PL/SQL will try to find the value 1, 2 and not the value 1 and the value 2. With one...
Card Puncher Data Processing
PL/SQL - Cursor

A cursor in the context of Oracle. A cursor is a SQL datatype. A cursor is a pointer to a private SQL area that stores information processing a specific: SELECT or DML statement. The cursor data...
Card Puncher Data Processing
PL/SQL - Dynamic SQL

SQL statements that are only know until run time are called dynamic SQL statements. To process a dynamic sql statement, you can use the following Native dynamic SQL statement: the statement with the...



Share this page:
Follow us:
Task Runner