A script-enabled browser is required for this page to function properly.

About bind references

Bind references (or bind variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries. Bind references may not be referenced in FROM clauses or in place of reserved words or clauses.

You create a bind reference by typing a colon (:) followed immediately by the column or parameter name. If you do not create a column or parameter before making a bind reference to it in a SELECT statement, Reports Builder will create a parameter for you by default.

Restrictions

Examples

Example 1:   SELECT clause

In the following example, the value of DFLTCOMM replaces null values of COMMPLAN in the rows selected.

SELECT CUSTID, NVL(COMMPLAN, :DFLTCOMM) COMMPLAN
  FROM ORD;

Example 2:   WHERE clause

The value of CUST is used to select a single customer.

SELECT ORDID, TOTAL
  FROM ORD
  WHERE CUSTID = :CUST;

Example 3:   GROUP BY clause

All non-aggregate expressions such as NVL(COMMPLAN, :DFLTCOMM) in the SELECT clause must be replicated in the GROUP BY clause.

SELECT NVL(COMMPLAN, :DFLTCOMM) COMMPLAN, SUM(TOTAL) TOTAL
  FROM ORD
  GROUP BY NVL(COMMPLAN, :DFLTCOMM);

Example 4:   HAVING clause 

The value of MINTOTAL is used to select customers with a minimum total of orders.

SELECT CUSTID, SUM(TOTAL) TOTAL
  FROM ORD
  GROUP BY CUSTID HAVING SUM(TOTAL) > :MINTOTAL;

Example 5:   ORDER BY clause

The value of SORT is used to select either SHIPDATE or ORDERDATE as the sort criterion. Note that this is not the same as ORDER BY 1 because :SORT is used as a value rather than to identify the position of an expression in the SELECT list. Note that DECODE is required in this example. You cannot use a bind variable in an ORDER BY clause unless it is with DECODE.

SELECT ORDID, SHIPDATE, ORDERDATE, TOTAL
  FROM ORD
  ORDER BY DECODE(:SORT, 1, SHIPDATE, 2, ORDERDATE);

Example 6:   CONNECT BY and START WITH clauses

References in CONNECT BY and START WITH clauses are used in the same way as they are in the WHERE and HAVING clauses.

Example 7: PL/SQL

procedure double is 
begin; 
  :my_param := :my_param*2; 
end;

The value of myparam is multiplied by two and assigned to myparam.

See also

About parameters

About lexical references

Differences between bind and lexical references