Oracle Bind Variables
Version 12.1.0.1

General Information
Library Note This page has been updated for Oracle release 12cR1 and shown to be compatible in the Container DB.
Without further specification bind variables are assumed to be of character type. Replace :b1 with TO_DATE(:b1) if working with date values: TO_NUMBER(:b1) if numeric values.
Data Dictionary Objects
DBA_HIST_SQLBIND V$SQL_BIND_CAPTURE V$SQL_BIND_METADATA
DBMS_ASSERT V$SQL_BIND_DATA WRH$_SQL_BIND_METADATA
Bind Variable Usage set linesize 121
col sql_text format a100

SELECT sql_text
FROM gv$sql
WHERE sql_text LIKE '%:B%'
AND rownum < 21;
Bind Variable Values col value_string format a60

SELECT DISTINCT hash_value, value_string
FROM gv$sql_bind_capture
WHERE rownum < 501
ORDER BY 1;
 
Bind Variable Demo
This demonstration is a merging of demos developed independently by Dan Morgan and Tom Kyte conn / as sysdba

GRANT select ON gv_$statname TO uwclass;
GRANT select ON gv_$latch TO uwclass;
GRANT select ON gv_$sql TO uwclass;
GRANT select ON gv_$sqltext_with_newlines TO uwclass;
GRANT select ON v_$mystat TO uwclass;
GRANT alter system TO uwclass;

conn uwclass/uwclass

CREATE TABLE run_stats (
runid VARCHAR2(15),
name  VARCHAR2(80),
value INT);

CREATE OR REPLACE VIEW stats AS
SELECT 'STAT...' || a.name NAME, b.value
FROM gv$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
UNION ALL
SELECT 'LATCH.' || NAME, gets
FROM gv$latch;

CREATE TABLE t (x INT);

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH SHARED_POOL;

INSERT INTO run_stats
SELECT 'before', stats.*
FROM stats;

set timing on

-- not using bind variables
DECLARE
 x NUMBER(10);
BEGIN
  FOR i IN 1 .. 50000
  LOOP
    EXECUTE IMMEDIATE 'SELECT ' || i || ' INTO :b1 FROM dual'
    INTO x;
  END LOOP;
END;
/

INSERT INTO run_stats
SELECT 'after 1', stats.*
FROM stats;

-- using bind variables
DECLARE
 x NUMBER(10);
BEGIN
  FOR i IN 1 .. 50000
  LOOP
    EXECUTE IMMEDIATE 'SELECT :b1 FROM dual'
    INTO x
    USING i;
  END LOOP;
END;
/

set timing off

INSERT INTO run_stats
SELECT 'after 2', stats.*
FROM stats;

col name format a35

SELECT a.name, b.value-a.value RUN1, c.value-b.value RUN2, ((c.value-b.value)-(b.value-a.value)) DIFF
FROM run_stats a, run_stats b, run_stats c
WHERE a.name = b.name
AND b.name = c.name
AND a.runid = 'before'
AND b.runid = 'after 1'
AND c.runid = 'after 2'
AND (c.value-a.value) > 0
AND (c.value-b.value) <> (b.value-a.value)
ORDER BY ABS((c.value-b.value)-(b.value-a.value));

SELECT sql_fulltext
FROM gv$sql s, gv$sqltext_with_newlines n
WHERE s.hash_value = n.hash_value
AND n.sql_text LIKE '%dual%'
ORDER BY last_active_time;
 
SQL Injection Variable Demo
This example is based on a demo created by Tom Kyte and published in the Jan/Feb 2005 issue of Oracle Magazine CREATE TABLE user_table (
username VARCHAR2(30),
password VARCHAR2(30));

INSERT INTO user_table
(username, password)
VALUES
('Tom Kyte', 'top_secret_password');

COMMIT;

SELECT * FROM user_table;

-- not using bind variables: valid attempt
SQL> Accept Uname prompt "Enter username: "
Enter username: Tom Kyte

SQL> Accept Pword prompt "Enter pass: "
Enter pass: top_secret_password

SELECT COUNT(*)
FROM user_table
WHERE username = '&Uname'
AND password = '&Pword';

-- not using bind variables: SQL injection
SQL> Accept Uname prompt "Enter username: "
Enter username: Tom Kyte

SQL> Accept Pword prompt "Enter pass: "
Enter pass: i_dont_know ' or 'x ' ='x

SELECT COUNT(*)
FROM user_table
WHERE username = '&Uname'
AND password = '&Pword';

-- using bind variables
CREATE OR REPLACE PROCEDURE validate_user(uname VARCHAR2, upasswd VARCHAR2) AUTHID DEFINER IS
 i NATURAL;
BEGIN
  SELECT COUNT(*)
  INTO i
  FROM user_table
  WHERE username = uname
  AND password = upasswd;

  IF i > 0 THEN
    dbms_output.put_line('Access Granted');
  ELSE
    dbms_output.put_line('Access Denied');
  END IF;
END validate_user;
/

set serveroutput on

exec validate_user('Tom Kyte', 'top_secret_password');

exec validate_user('Mark Rittman', 'i dont know');

exec validate_user('Dan Morgan', '''i_dont_know'' or ''x = x''');
DBMS_ASSERT Demo CREATE TABLE pwd (
userid   NUMBER(5),
password VARCHAR2(20));

INSERT INTO pwd (userid, password) VALUES (1, 'Ellison');
INSERT INTO pwd (userid, password) VALUES (2, 'Hurd');
INSERT INTO pwd (userid, password) VALUES (3, 'Katz');

CREATE OR REPLACE PROCEDURE get_pwd(uid IN pwd.userid%TYPE, tab IN user_tables.table_name%TYPE, retval OUT pwd.password%TYPE) AUTHID DEFINER IS
 ret  pwd.password%TYPE;
 str  CONSTANT VARCHAR2(60) := 'SELECT a.password FROM sqlobj a WHERE a.userid = :b1';

 stmt CONSTANT VARCHAR2(90) := REPLACE(str, 'sqlobj', SYS.DBMS_ASSERT.SQL_OBJECT_NAME(tab));
BEGIN
  NULL;
  dbms_output.put_line(stmt);
  EXECUTE IMMEDIATE stmt INTO retval USING uid;
END get_pwd;
/

DECLARE
 x VARCHAR2(200);
BEGIN
  get_pwd(2, 'PWD', x);
  dbms_output.put_line(x);
END;
/

DECLARE
 x VARCHAR2(200);
BEGIN
  get_pwd(2, 'BAD', x);
  dbms_output.put_line(x);
END;
/

Related Topics
DBMS_ASSERT
Delete Statements
Insert Statements
Merge Statements
Native Dynamic SQL
Update Statements