Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
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;
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;
/
INSERT INTO pwd (userid, password) VALUES (1, 'Ellison');
INSERT INTO pwd (userid, password) VALUES (2, 'Loaiza');
INSERT INTO pwd (userid, password) VALUES (3, 'Catz');
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
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;
/