Note: 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.
Bind Variable Usage
set linesize 121
col sql_text format a100
SELECT sql_text
FROM gv
WHERE sql_text LIKE '%:B%'
AND rownum < 21;
Bind Variable Values
col value_string format a60
SELECT DISTINCT hash_value, value_string
FROM gv
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_ TO uwclass;
GRANT select ON gv_ TO uwclass;
GRANT select ON gv_ TO uwclass;
GRANT select ON gv_ TO uwclass;
GRANT select ON gv_ 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 a, gv b
WHERE a.statistic# = b.statistic#
UNION ALL
SELECT 'LATCH.' || NAME, gets
FROM gv;
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 .. 5000
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 .. 5000
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 s, gv n
WHERE s.hash_value = n.hash_value
AND n.sql_text LIKE '%dual%'
ORDER BY last_active_time;
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) 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;
/
Free