BI Publisher: What the Lexical! Understanding why, when and how to use lexicals and bind variables correctly

Lexicals can be confusing for new developers. Lexicals & Bind variables are used in the following technology sets:

1. BI Publisher
2. Reports6i
3. SQL Scripts
4. PL/SQL

A bind variable is used for the assignment of value, a lexical is the literal value. The syntax is as follows for a bind variable :P_SEGMENT and the sytax for lexical is &L_WHERE_CLAUSE. Here’s how you would use the two of these in a query:

1. Select * from mtl_system_items where segment1 = :p_segment
2. Select * from mtl_system_items where &l_where_clause

Here are some neat tricks with lexicals and how they can be combined with bind variables. Below we can see that a lexical can contain a bind variable. This may seem confusing but it has major implications for the performance of your query.

Good:
l_where_clause := 'segment1 = :p_segment';

Bad:
l_where_clause := 'segment1 = '''p_segment'';

Appending segment1i s a bad idea because it causes a performance issue. Every time this query is parsed it needs to generate a new explain plan. So nothing gets cached. Where as the lexical with a bind variable is much better, because the statement gets cached.

The performance issue doesn’t really rear its ugly head until it gets in a production environment or this query is being executed in some sort of batch process.

That’s it, that’s all there is to lexical and bind variables.

Good Luck!