Bind variables

In PL/SQL, any variable you use in a statically parsed SQL statement automatically becomes a bind variable. If you follow the good programming practice of puttingliteral values in parameters and constants, and if you refer to these variables rather than the literal values in your SQL statements, you will generally be in good shape. Here's a simple example:

FUNCTION maxcats (threshhold_in IN NUMBER DEFAULT 100) RETURN NUMBERIS CURSOR qcur IS SELECT category, COUNT(*) FROM booklist GROUP BY category HAVING COUNT(*) > threshhold_in;etc...

PL/SQL translates the threshhold_in parameter into a bind variable in the query.

A lesser-known fact is that you can also use bind variables inanonymous blocks in SQL*Plus. For example:

SQL> VARIABLE howmany NUMBERSQL> EXEC :howmany := maxcats

Note that you cannot, however, assign a value to a SQL*Plus bind variable unless you do so in an anonymous block.

One problem with bind variables comes up when you usedynamic SQL to construct a SQL statement at runtime. Sloppy programming results in statements getting built with literal values. For example:

CREATE OR REPLACE FUNCTION count_recent_records (tablename_in IN VARCHAR2, since_in IN DATE) RETURN PLS_INTEGER AS count_l PLS_INTEGER; BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tablename_in || ' WHERE lastupdate > TO_DATE(''' || TO_CHAR(since_in, 'YYYYMMDD') || ''', ''YYYYMMDD'')' INTO count_l; RETURN count_l; END;

This causes the dynamic construction of statements such as:

SELECT COUNT(*) FROM tabname WHERE lastupdate > TO_DATE('20020315', 'YYYYMMDD')

Repeated invocation with different since_in arguments can result in a lot of unsharable statements. For example:

SELECT COUNT(*) FROM tabname WHERE lastupdate > TO_DATE('20020105', 'YYYYMMDD')SELECT COUNT(*) FROM tabname WHERE lastupdate > TO_DATE('20010704', 'YYYYMMDD')SELECT COUNT(*) FROM tabname WHERE lastupdate > TO_DATE('20030101', 'YYYYMMDD')

A "bind variable" version of this would be:

CREATE OR REPLACE FUNCTION count_recent_records (tablename_in IN VARCHAR2, since_in IN DATE)RETURN PLS_INTEGERAS count_l PLS_INTEGER;BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tablename_in || ' WHERE lastupdate > :thedate' INTO count_l USING since_in; RETURN count_l;END;

which results in statements that look like this to the SQL compiler:

SELECT COUNT(*) FROM tabname WHERE lastupdate > :thedate

Not only is the second version prettier and easier to follow, but it should also perform better over repeated invocations with the same tablename_in but with different since_in arguments.

However, there is a way to relax the optimizer's rules on literal strings as of Oracle 8.1.6, which introduced a parameter calledCURSOR_SHARING. You can turn on this feature using the command:

ALTER SESSION SET CURSOR_SHARING = FORCE; /* available in 8.1.6 or later */

or:

ALTER SESSION SET CURSOR_SHARING = SIMILAR; /* in 9.0 or later */

For the remainder of your session, Oracle will invisibly rewrite the literal queries from the first function into a single statement like this:

SELECT COUNT(*) FROM tabname WHERE lastupdate > TO_DATE(:"SYS_B_0", :"SYS_B_1")

This provides the same results to the application, but in a way that eases the burden on the library cache.

To turn off cursor sharing, specify:

ALTER SESSION SET CURSOR_SHARING = EXACT;

If you are using Oracle8i, you cannot set cursor sharing to SIMILAR; you must use either EXACT or FORCE. With FORCE, Oracle always rewrites the SQL with literals to use a bind variable, but this may decrease performance in some cases. With SIMILAR, Oracle will rewrite the literal to use bind variables only if the optimizer would not consider the literal during optimization.

Use bind variables and an appropriate setting of CURSOR_SHARING to help Oracle share SQL statements. Remember that the datatype and maximum length of the bind variables must match.