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.
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.
