Badhu

• Jun 27th, 2006

 

Bind Parameters are denoted by (:) Used to get the value from the User.

Lexcial Parmeters are denoted by (&) Used to get the Clause from the User.

Forms, Pl/Sql and SQL - we can use both. Reports - we can use Bind Paramters.

sanjoy.dubey

 Profile Answers by sanjoy.dubey Questions by sanjoy.dubey

• Mar 3rd, 2008

 

In Reports you can use both bind and lexical parameters

trraka

 Profile Answers by trraka 

• Mar 19th, 2008

 

Both could be used in reports as well as forms.

In reports Lexical parametrs are used to create the where clause of the Report query dynamically.

eg

select * from em where &p_emp;

and in the after parameter form trigger you can set the values of the where clause as:

p_emp:='emp_name like %:p_emp_name% '

Raghu2008

 Profile Answers by Raghu2008 

• Sep 18th, 2008

 

Hi,

 The bind parameter is prefixed with ':' and it is used to accept and refer the value while executing the queries where as lexical parametes (prefixed with '&') are used to build report queries dynamically. Bind parametes can be used in Report and Forms but lexical parameters can be used on reqports only.

Thanks,
Raghu. 

samareshp

 Profile Answers by samareshp 

• Apr 1st, 2009

 

Both are used in reports.
 
Differences between bind and lexical references


Bind references are used to replace a single value in SQL or PL/SQL. 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 used in a FROM clause.
 An example is:
SELECT ORDID,TOTAL
  FROM ORD
  WHERE CUSTID = :CUST

Lexical references are placeholders for text that you embed in a SELECT statement, when you want the parameter to substitute multiple values at runtime. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH. You cannot make lexical references in PL/SQL. Before you reference a lexical parameter in a query you must have predefined the parameter and given it an initial value.
An example is: SELECT ORDID, TOTAL
  FROM &ATABLE

A reference to a parameter used to represent a string of text in a SQL SELECT statement. For a lexical reference, you must precede the parameter name with an ampersand (&).


You cannot make lexical references in a PL/SQL statement. You can, however, use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL.

If a column or parameter is used as a lexical reference in a query, its data type must be character.

Dayakark

• Feb 16th, 2017

 

Bind references are used to replace a single value in SQL or PL/SQL. 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.

Syntax:
SELECT Col1,Col2 FROM XX_Table WHERE Col1 = :P_col1;

Bind parameters are used to substitute single value at runtime for evaluation and are identified by a preceding ‘:’

Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH. You cannot make lexical references in PL/SQL. Before you reference a lexical parameter in a query you must have predefined the parameter.

Syntax:
SELECT Col1,Col2 FROM &ATABLE

Lexical parameters are used to substitute multiple values at run time and identified by a preceding ‘&’. Lexical can consist of as little a one line where clause to an entire select statement

kamaraj

• Nov 2nd, 2017

 

Bind replaces single value in SQL as input
Lexical replaces a statement in SQL as input
Both are used in Oracle Reports

gamal

• Nov 8th, 2017

 

What is key.next.item trigger?

kamaraj

• Dec 16th, 2018

 

key next item trigger fires when we move next item(key board events such like tab or enter), it ill not fire when we click mouse to another item.

KAMARAJ

• Dec 16th, 2018

 

bind refrences uses :varname
lexical refrences uses &varname in sql statement